Dynamic INSERT statement

Here's the deal. I'm just tired of writing this script. I have to write it several times a week.

I'll blog it here and be done with writing it. <smiles>

This is a script that will produce an insert statement for any table in SQL 2008 +

Sure, you can just right click on a table and get this... but my solution is ready to use and requires only minimal editing.

And (for all you OCDers like me) the commas line up.

declare @table_name varchar(255)

declare @out varchar(max)

declare @out2 varchar(max)

select @table_name = 'PM30200'

select @out = coalesce(@out + ' ,','') + rtrim(column_name)

from information_schema.columns

where table_name like @table_name

and not column_name in ('DEX_ROW_TS','DEX_ROW_ID')

order by ordinal_position

select @out2 = coalesce(@out2 + ',','') + 't.' + rtrim(column_name)

from information_schema.columns

where table_name like @table_name

and not column_name in ('DEX_ROW_TS','DEX_ROW_ID')

order by ordinal_position

print 'insert into ' + @table_name + ' (' + @out + ')'

print ' select ' + space(len(@table_name)) + @out2

print ' from ' + @table_name + ' t'

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.

Links
Home
Knowledge Areas
Sitemap

Contact

Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences

Related Articles