If numeric then Insert numeric else Insert non-numeric

Question

I have table source SRC such as:

*Column1*
First
Second
45
Fouth

Now I want to insert these data into table DEST (ID, NAME) with this logic:

If row is numeric, insert into (ID, NAME) VAUES (45, 'TBD').
If the row is not numeric, generate ID and insert into (ID, NAME) VALUES (*GENERATED ID*, NAME).

I tried something like:

DECLARE @i INT;
SELECT @i = MAX ( ID ) + 1
FROM DEST;


IF ( SELECT ISNUMERIC ( SELECT Column1 FROM SRC ) AS help ) = 1
    BEGIN
        INSERT INTO DEST (ID, NAME) VALUES (45, 'TBD')
    END;
ELSE
    BEGIN
        INSERT INTO DEST (ID, NAME) SELECT ROW_NUMBER() OVER(ORDER BY NAME) +@i, 'First';
       INSERT INTO DEST (ID, NAME) SELECT ROW_NUMBER() OVER(ORDER BY NAME) +@i, 'Second';
       INSERT INTO DEST (ID, NAME) SELECT ROW_NUMBER() OVER(ORDER BY NAME) +@i, 'Fourth';
    END;

(simplified solution to demonstrate the purpose, it should be dynamic, not hardcoded)

.., but that obviously does not work. How to do that?

Does the generated ID have to be unique? In your example, if you had 46 rows, then the last one would also have a generated ID of 45 (conflicting with your example data item '45'). — Martin
– Martin, Commented Jun 9, 2015 at 12:33
Yes, they need to be unique. Such situation wont happen as I am checking what IDs I already have in DEST table. If I already have 45 in there, I do not need to insert it. If I am missing this ID coming from SRC table, it should be added into DEST table. SRC table is now table containing records I do not have in my DEST table. — DNac
– DNac, Commented Jun 9, 2015 at 12:36
Do you want to insert numeric or integer values? What does your id column in DEST support? — ughai
– ughai, Commented Jun 9, 2015 at 12:38
ID in DEST is interger type. So if the record in SRC table is integer, it should be added into ID. If the record is varchar, it should be added into NAME column with ID auto generated (to be unique value). — DNac
– DNac, Commented Jun 9, 2015 at 12:40

Martin · Accepted Answer · 2015-06-09 12:37:26Z

1

One approach you can take is the following, which uses a CASE statement to allow you to differentiate between numeric and non-numeric values of Column1:

-- Some temporary tables to make the example work
CREATE TABLE #SRC (Column1 VARCHAR(50))
INSERT INTO #SRC (Column1) VALUES ('First'), ('Second'), ('45'), ('Fourth')
CREATE TABLE #DEST (ID INT)

DECLARE @i INT
-- If #DEST is empty we need to have an initial value of 1
SELECT @i = ISNULL(MAX(ID),0) + 1 FROM #DEST
PRINT @i

INSERT INTO #DEST (ID)
    SELECT  CASE ISNUMERIC(Column1)
              WHEN 1 THEN Column1
              ELSE ROW_NUMBER() OVER (ORDER BY Column1) + @i
            END
      FROM  #SRC

SELECT  *
  FROM #DEST

DROP TABLE #SRC
DROP TABLE #DEST

The example will not port directly into your code, but should give you a good basis to begin working from in order to achieve your desired result.

Note my comment to your original post that you may get clashes on the ID column if inserting multiple rows with this. You will need to consider what to do in that situation.

answered Jun 9, 2015 at 12:37

Martin

16.5k1 gold badge33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DNac Over a year ago

Thanks, I played around with this a bit and seems to be working.

ughai · Accepted Answer · 2015-06-09 13:01:32Z

You could try something like this.

First insert numeric values by checking ISNUMERIC.

INSERT INTO DEST (ID,Name)
SELECT TRY_CONVERT(INT,Column1),'TBD'
FROM SRC 
WHERE ISNUMERIC(Column1) = 1

Now Insert other values

DECLARE @maxid INT 
SELECT @maxid = MAX(ID) FROM DEST;

INSERT INTO DEST (ID,Name)
SELECT @maxid + ROW_NUMBER()OVER(ORDER BY Column1 ASC),Column1
FROM SRC 
WHERE ISNUMERIC(Column1) = 0

Note : ISNUMERIC doesn't guarantee that the value will be converted successfully to a numeric value. Also it looks like you want to check if the value is integer and not numeric unless you are ok with truncation of decimal point. You can use Column1 LIKE '%[0-9]%' to check if a value contains only numbers and not decimal value if that is the case

For Example, the below value '.' returns ISNUMERIC as 1 however cannot be converted to a numeric or an int :

DECLARE @value varchar(10) = '.'

SELECT ISNUMERIC(@value),TRY_CONVERT(INT,@value),TRY_CONVERT(NUMERIC(18,2),@value)

Collectives™ on Stack Overflow

If numeric then Insert numeric else Insert non-numeric

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related