7

I am trying to write an SQL script to create an insert statement for each row of table TEST which has field A (int) and field B varchar(30), in my MS SQL database.

I would like to save those insert statements into a txt file to be able to apply that data back to another database.

Any idea about how to do it?

1

4 Answers 4

8
  1. Right-click on your Database in Object Explorer
  2. Select Tasks -> Generate Scripts...
  3. In that wizard you will be able to select the desired object(s) that you'd like to script out
  4. The next portion has an Advanced button, click that
  5. Under General, you will see the option called Types of data to script. The options are Schema only, Schema and data, or Data only. I believe you want the last option (Data Only)
2
  • I think that this only works on 2008+. Jun 29, 2012 at 16:52
  • +1 Confirmed that this works on 2000 and 2005 instances using 2012 and 2008R2 SSMS.
    – swasheck
    Jun 29, 2012 at 18:23
3

Why not download SSMS Tools Pack from http://www.ssmstoolspack.com/Download . It easily creates insert scripts. I use it all the time and it is a fabulous tool. Did I mention it is Free!

1
  • It's not free anymore Mar 3, 2013 at 7:03
0

You can also use the bcp utility to import or export data.

In your case use something like the following command:

bcp "SELECT A, B FROM TEST" queryout Test.txt -c -T

To insert data from the file into another table:

bcp Database.Schema.Table in Test.txt -T –c

Hope this helps as an alternative

0

I often use a SELECT to generate other SQL:

select
    'insert dbo.TEST(A, B) values (' + convert(varchar(20), A) + ', ''' + B + ''');'
from dbo.TEST;

Save the output and re-use as needed. If column B contains text delimiters you'll have to deal with this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.