Thursday, 8 September 2011

Generating SQL insert commands using SELECT

A quick hack for generating a list of SQL insert commands from an existing data set. Useful for copying specific data between databases/tables.

select 'insert into DestTable values(''' + Value1 + ''',''' + Value2 + ''')' from SourceTable

This query will return a result set in the format:
insert into DestTable values('a','b')
insert into DestTable values('x','y')

A few notes:
  • The '+' might need to be replaced with a concat function on certain databases.
  • A convert or cast operation may be required for non-character values
  • The result set can be limited by adding a where class to the select query