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?

some sample data and a detailed description of what should happen to that data will get you a useful answer. — dnagirl
– dnagirl, Commented Oct 26, 2009 at 16:33
possible duplicate of Exporting data In SQL Server as INSERT INTO — Flexo - Save the data dump
– Flexo - Save the data dump ♦, Commented Dec 31, 2011 at 4:03

Aric TenEyck · Accepted Answer · 2009-10-26 16:33:26Z

5

Ten records, and it's urgent?

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

answered Oct 26, 2009 at 16:33

Aric TenEyck

8,0521 gold badge37 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave Swersky · Accepted Answer · 2009-10-26 16:35:11Z

2

Assuming SQL Server...

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

answered Oct 26, 2009 at 16:35

Dave Swersky

34.8k9 gold badges83 silver badges120 bronze badges

1 Comment

Madhivanan Over a year ago

Note that this is supported from version 2008 onwards

Lucero · Accepted Answer · 2009-10-26 16:32:19Z

2

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

answered Oct 26, 2009 at 16:32

Lucero

60.4k9 gold badges127 silver badges154 bronze badges

Comments

Russell Steen · Accepted Answer · 2009-10-26 16:33:23Z

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 + ', '....

answered Oct 26, 2009 at 16:33

Russell Steen

6,6126 gold badges40 silver badges57 bronze badges

Comments

cakeforcerberus · Accepted Answer · 2009-10-26 16:34:33Z

2

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.

answered Oct 26, 2009 at 16:34

cakeforcerberus

4,6576 gold badges34 silver badges42 bronze badges

1 Comment

recursive Over a year ago

I had the same idea, only 28 seconds later.

Andreas · Accepted Answer · 2011-06-20 05:47:22Z

This code works with all tables

DECLARE @TblName varchar(128)
DECLARE @WhereClause varchar(255)
DECLARE @cmd1 varchar(7000) 
DECLARE @cmd2 varchar(7000)

SET @TblName = '<tablename>' --Name of your table
SET @WhereClause = ' ' --where clause ex columnA = 1
SET @cmd1 = 'SELECT '' INSERT INTO ' + @TblName + ' ( '
SET @cmd2 = ' + '' VALUES ( '' + '

create table #tableDef (id int identity (1,1), ColType int, ColName varchar(128))

--Fetch column names and datatypes
insert  #tableDef (ColType, ColName)
select case when DATA_TYPE like '%char%' then 1
            when DATA_TYPE like '%datetime%' then 2 
            else 0 end ,
    COLUMN_NAME
from    information_schema.columns
where   TABLE_NAME = @TblName
order by ORDINAL_POSITION

SELECT @cmd1 = @cmd1 + ColName + ',',
       @cmd2 = @cmd2
                + ' CASE WHEN ' + ColName + ' IS NULL '
                +   ' THEN ''NULL'' '
                +   ' ELSE '
                +     case ColType 
                      when  1 then  ''''''''' + ' + ColName + ' + ''''''''' 
                      when  2 then  ''''''''' + ' + 'CONVERT(VARCHAR(20),' + ColName + ')' + ' + '''''''''                    
                      else 'CONVERT(VARCHAR(20),' + ColName + ')' end
                + ' END + '','' + '
        from    #tableDef
order by id

select @cmd1 = left(@cmd1,len(@cmd1)-1) + ' ) '' '
select @cmd2 = left(@cmd2,len(@cmd2)-8) + '+'')'' FROM ' + @tblName + @WhereClause

select '/*' + @cmd1 + @cmd2 + '*/'
exec (@cmd1 + @cmd2)

drop table #tableDef

Collectives™ on Stack Overflow

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

6 Answers 6

Comments

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related