Thursday, March 18, 2010

Generate INSERT statements for a table's data

I have run sometimes into the problem of generating INSERT statements from a table with data to be used later into other databases or simply to recreate automatically the data when the system is installed.

Basically we build a SELECT query that the result is a set of inserts strings, one for each row of data in the table:

select 'insert into TABLE_NAME (field1, field2) values (''' + field1 +''',''' + field2 + ''')' from TABLE_NAME

The tables that make more sense to apply this trick are mainly lookup tables.

Note that we can filter, format, sort and even use the data resulting from a join. We can also apply this solution to SQL Server, Oracle and virtually any sql based relational system.