by Jack

Dynamic SQL Server Insert

In our product we need to often insert from an old table into a new table, however the definition of the old table can vary. In 99% of cases all we are doing in the new table is enlarging column sizes or adding new columns. The following SQL can be used to automatically generate an insert from the columns existing on the old table into the new table, needing only 2 parameters changed -which can be easily changed.

DECLARE @table_columns NVARCHAR(max)
DECLARE @insert_command NVARCHAR(max)
Build column list
The COALESCE prevents NULL values
SELECT @table_columns = COALESCE(@table_columns + ',', ' ') + column_name
FROM information_schema.COLUMNS
WHERE TABLE_NAME = @old_table

-- Build insert statement
SELECT @insert_command = 'insert into ' + @new_table + ' (' + @table_columns + ' )'
+ ' SELECT ' +@table_columns + ' FROM ' + @old_table

-- Run statement in EXEC - this allows for the dynamic statement to be run