SQL 2005: How can I create "Insert Into" statements?
-
I needed to create "Insert Into" statements for about 10 records of data (6 fields) to send to someone who has the same database I have. I was hoping SQL 2005 would have a way to do this, but I don't see it.
Google led to me to a couple of stored procs to generate the statements, but neither of them actually worked. Creating the statements manually is... tedious.
Is there a generally accepted, easy way to do this? (These are both development systems, so I don't know if we'll ever set up real replication between them or not.)
Thanks.
-
Why not use the Import/Export functionality? You export the data as excel/csv/etc and the other user imports it (setting identity insert on if applicable). The other option I use is to export the data to excel and then build the Insert statement as a function on one of the columns. I do similar things to manage my custom error codes to keep track of them / generate the creation proc statements.
-
I agree with the former poster that textfiles work quite well...
But if you need inserts you can send me a short email without spam to rdrunner6_spam at gmx dot de
I will send you a copy of the insert script then
-
[quote user="DWalker59"]
I needed to create "Insert Into" statements for about 10 records of data (6 fields) to send to someone who has the same database I have. I was hoping SQL 2005 would have a way to do this, but I don't see it.
Google led to me to a couple of stored procs to generate the statements, but neither of them actually worked. Creating the statements manually is... tedious.
Is there a generally accepted, easy way to do this? (These are both development systems, so I don't know if we'll ever set up real replication between them or not.)
Thanks.
[/quote]
For a really quick and dirty solution, set a query window to "Output to Text" and do a select * from the table. Then add quotes and commas as necessary and paste an "INSERT (blah,blah,blah,...) VALUES(" in front of each line. You can even make the INSERT line from the header row.
However, I'd recommend that you build a little app in your favorite language to make INSERT statements. You'll use it a hundred times throughout your professional career. Eventually you can add stuff like special character handling, less common data types (varbinary is fun), an INSERT or UPDATE variation, and a bunch of other useful stuff.
-
select 'Insert into fred (col1, col2, ...) values (' + convert(varchar, col1) + ', ' + convert(varchar, col2) + ', ' + ... + ')'
from fred
Add (escaped) quotes as required for character based column types.
I suppose if you really want to go over the top, you could go to the system catalogs & generate the above statement by looking at all columns and their types on the table.
-
Adding a bunch of quotes and commas manually would be a pain in the rear end. I'll ask the guy who said he had a way to generate them.
Using Excel didn't seem like an easy way either. We'll see.
Thanks.
-
I'd recommend scripting the tables, exporting the data from them into text files, then importing them into the newly created database.
Or... you could just back up your database and have your buddy restore it to his machine.
-
You can use this utility:
http://www.codeproject.com/dotnet/ScriptDatabase.aspHope this helps!
Bye,
Reto
-
Something like this? http://software.techrepublic.com.com/download.aspx?docid=227448
MySQL comes with mysqldum which does exactly what you're asking for. I'd be surprised if Microsoft doesn't ship a similar tool to create .sql files.
