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?

Answer 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.

Answer 2

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)