SQL Server Performance Forum – Threads Archive
generate insert into table values() from a table
How should I generate insert into table values() from a table.pls suggestThe first entry that you find in BOL for INSERT INTO gives the VALUES (…) option – that’s a bit stupid as in most cases you will need the SELECT syntax, like so: INSERT INTO MyTable1 (column1, column2)
SELECT column1, column2 FROM MyTable2 You can use the complete syntax for SELECT statements, but you must make sure that you’re not violating constraints in the target table (like duplicate records) and that FK fields get the correct corresponding value.
any stored procedure or any method is there so that i can generate the scripts just by suppiing the table name as a variable.
EM provides the way to generate scripts of db objects but no provision for getting the insert scripts filled with data from the tables
Hm, you’ll have to make your way through the tables in such a way that all tables with foreign keys are filled only after all FK tables are already filled – or drop those constraints and recreate them after inserting the data. And you’ll have to look at inserting identity values. So it depends on the number of tables and relationships, but it’s probably best to script out the whole thing. You can also import tables, including data, but AFAIK you still have to re-create all relationships and constraints – not sure about indexes or triggers.
Check this script:
http://vyaskn.tripod.com/code/generate_inserts.txt
]]>