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.
No comments:
Post a Comment