How do I script my table to generate INSERT INTO commands?

Question

I have a lookup table with about 10 records, I know I can script the structure to a text file, but how can I script the data to insert into commands?

Answer 1

This depends pretty much on the tools you are using...

Answer 2

The quick and dirty way is to run a select into a string and tell sql enterprise manager to give you text (not grid) as the output

SELECT 'INSERT INTO TABLES (fields here) VALUES (' + field1 + ', '....

Answer 3

Ten records, and it's urgent?

Just type it out manually. Should be pretty easy to cut-n-paste.

Answer 4

Do something like this:

select "insert into my_targ_table(my_field_1, my_field_2, ..., my_field_n) values(" || x.my_field_1_col || ", " || x.my_field_2_col || ");"
from my_source_table x

Then just run the script you've generated.

Answer 5

Assuming SQL Server...

SQL Management Studio will generate an insert script. Right-click your database and select Tasks-Export data