SQL Server- can a select query be executed to specify column names for the insert into command?



  • So I'm trying to insert the same table into itself, but I have got a breakage because of repetition of primary keys. Hence, I'm thinking to insert the same rows without the primary key first, and then another query would insert an auto_increment primary key.

    The following query breaks because of the primary key (obviously):

    INSERT INTO table_name 
    SELECT * FROM table_name;
    

    While the following query returns every field barring the primary key:

    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = table_name
    

    EXCEPT

    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS [tc]
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE [ku] ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
    AND ku.table_name = table_name

    Now if I try to insert into a table not every column, the following is a proper syntax:

    INSERT INTO table_name () 
    SELECT   FROM table_name
    

    But if I try to make it work for my case, the following query breaks because of not maintaining the table structure:

    INSERT INTO dbo.drugs 
    SELECT (SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'drugs'
    

    EXCEPT

    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS [tc]
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE [ku] ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
    AND ku.table_name = 'drugs') FROM drugs;

    The above breaks because I have not mentioned the column_names in the first line. Is there a way I can make this work?



  • Something like this:

        declare @sql nvarchar(max) = 
        (
            select concat('
            insert into drugs(',STRING_AGG(cast(name as nvarchar(max)), ', ') within group (order by column_id),')
            select ',STRING_AGG(cast(name as nvarchar(max)), ', ') within group (order by column_id),'
            from drugs')
            from sys.columns c
            where c.object_id = object_id('drugs')
              and c.is_identity = 0
       )
       print(@sql)
       exec (@sql)
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2