Generate insert statements for existing data
Once in a while I get this query: Do you know any tool to generate scripts for data in an existing table, of course for SQL Server?
Well, There are quite a few options.
If your company is rich enough to sponsor a tool then you could go for Redgate SQLToolBelt. This has quite a nice set of tools to generate scripts from existing data or new data and to compare scripts etc.
You could also try out the free tool : http://www.ssmstoolspack.com But this works only with SQL server Management Studio 2005 SP2 or above.
If you are using SQL Server 2008 you could use the built in option SSMS in 2008. This is a quick run through to generate Insert statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:
- DATABASE NAME: Right Click
- TASKS: GENERATE SCRIPTS
- Under Table/View Options: Set SCRIPT DATA = TRUE
You will then get the create table statement and all of the INSERT statements for the data straight out of SSMS.
You can also use this simple stored procedure given generously by Vyas. The procedure that does the script generation is here.
I am using it for quite sometime now. Works perfect for my requirement!
The problem with this approach that it is database specific plus you have to be connected to database.
I had several situations when I wanted to generate inserts using text file but could not.
Likely there is another solution
http://www.dbsoftlab.com/Advanced-ETL-Processor-News/Generate-Insert-Statements.html
Mike