Inserting records from a table to another using dynamic SQL with conditions to convert data type-CodePudding

I have two tables whose structures as follows:

 table_A

 CREATE TABLE table_A
  ( 
    col_a varchar(100),
    col_b bigint,
    col_c datetime
  )

  table_b
  --Note that columns are same--
  CREATE TABLE table_B
   (
    col_a varchar(10),
    col_b varchar(10),
    col_c varchar(20)
   )

Now I want to INSERT data into table_A from table_B with proper data type conversion.

Below is the SQL string:

 INSERT INTO table_A(col_a,col_b,col_c)
 SELECT CONVERT(varchar,col_a),CONVERT(INT,col_b),CONVERT(datetime,col_c) FROM table_B

So far so good.

Now I want generate the SQL dynamically with the help of INFORMATION_SCHEMA.COLUMNS.

For this I have followed the below steps:

Step 1: Join the Information Schema for the above two tables viz table_A and table_B and store them in a #TempTable. Lets assume that #TempTable has an ID column that is IDENTITY(1,1) but that doesn't follow any sequence like 1,2,3...(Typically this happens in Synapse SQL)

INSERT INTO #TempTable
SELECT S.COLUMN_NAME AS Src_Col, 
         S.DATA_TYPE AS Src_dtype,
         D.COLUMN_NAME AS Dest_Col,
         D.DATA_TYPE AS Dest_dtype,
         CASE WHEN S.DATA_TYPE NOT LIKE D.DATA_TYPE THEN 
                   'CONVERT('  ''''   D.DATA_TYPE   ''''   ','   ''''   S.DATA_TYPE   ''''   ')'
                    ELSE S.DATA_TYPE AS Modified_Col
   FROM INFORMATION_SCHEMA S
   JOIN INFORMATION_SCHEMA.COLUMNS D
   ON S.COLUMN_NAME = D.COLUMN_NAME AND S.TABLE_NAME = REPLACE(D.TABLE_NAME,'_B','_A')

Step 2: Iterate over #TempTable to fetch the Modified_Col values

 SET @Max_ID = (SELECT MAX(ID) FROM #TempTable);
 SET @Min_ID = (SELECT MIN(ID) FROM #TempTable);

 SET @ColToInsert = '';
 SET @Dest_Col = '';
 WHILE @Min_ID <= @Max_ID
 BEGIN
   SET @ColToInsert = (SELECT @ColToInsert   Modified_Col FROM #TempTable T WHERE T.ID = @Min_ID);
   SET @Dest_Col = (SELECT @Dest_Col   Dest_Col FROM #TempTable T WHERE T.ID = @Min_ID);
   SET @Min_ID = @Min_ID   1;
 END

Step 3: Use that @ColToInsert in the below Dynamic SQL

  SET @DySQL = 'INSERT INTO Table_A('   @Dest_Col   ') SELECT '   @ColToInsert   ' FROM table_B';

  exec (@DySQL);

Now at this step 3 I am not getting the expected result. No data is getting inserted into table_A. I can understand that in the CASE statement I have to make some fixes so that convert... portion becomes a string. And I am not able to do so.

Any clue would be appreciated.

CodePudding user response：

I don't understand why you need the temp table at all. You just need to aggregate using STRING_AGG.

You also need to quote the objects and columns using QUOTENAME, and you should use sys.columns etc rather than INFORMATION_SCHEMA, which is for compatibility only.

DECLARE @tableA sysname = 't';
DECLARE @tableB sysname = 's';

DECLARE @sql nvarchar(max) = (
    SELECT CONCAT(
      'INSERT INTO ',
      QUOTENAME(@tableA),
      '(',
      STRING_AGG(CAST(QUOTENAME(cA.name) AS nvarchar(max)), ', '),
      ')
SELECT ',
      STRING_AGG(
        CASE WHEN cA.user_type_id <> cB.user_type_id THEN
          CONCAT(
             'CONVERT(',
             typ.name,
             CASE
               WHEN typ.name IN ('varchar','nvarchar','char','nchar','varbinary','binary')
                 THEN CONCAT('(', CASE WHEN cA.max_length = -1 THEN 'max' END, NULLIF(cA.max_length, -1), ')')
               WHEN typ.name IN ('datetime2','datetimeoffset','time','float','real')
                 THEN CONCAT('(', cA.scale, ')')
               WHEN typ.name IN ('float','real')
                 THEN CONCAT('(', cA.precision, ')')
               WHEN typ.name IN ('decimal','numeric')
                 THEN CONCAT('(', cA.precision, ',', cA.scale, ')')
             END,
             ', ',
             CAST(QUOTENAME(cB.name) AS nvarchar(max)),
             ')'
          )
        ELSE
          CAST(QUOTENAME(cB.name) AS nvarchar(max))
        END
        , ', '),
      '
FROM ',
      QUOTENAME(@tableB)
    )
    FROM sys.columns cA
    JOIN sys.tables tA ON ta.object_id = cA.object_id AND tA.name = @tableA
    JOIN sys.types typ ON typ.user_type_id = cA.user_type_id
    JOIN sys.columns cB ON cB.name = cA.name
    JOIN sys.tables tB ON tB.object_id = cB.object_id AND tB.name = @tableB
);

PRINT @sql;  -- your friend

EXEC sp_executesql @sql;

db<>fiddle