470,815 Members | 1,272 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,815 developers. It's quick & easy.

Extract data in "Insert Into..." statement format

Is there a way in SQL Server 2000 to extract data from a table, such that
the result is a text file in the format of "Insert Into..." statements, i.e.
if the table has 5 rows, the result would be 5 lines of :

insert into Table ([field1], [field2], .... VALUES a,b,c)
insert into Table ([field1], [field2], .... VALUES d, e, f)
insert into Table ([field1], [field2], .... VALUES g, h, i)
insert into Table ([field1], [field2], .... VALUES j, k, l)
insert into Table ([field1], [field2], .... VALUES m, n, o)

Thanks in advance
Jul 23 '05 #1
5 2905
Vyas has just what you need:
http://vyaskn.tripod.com/code.htm#inserts

--
David Portas
SQL Server MVP
--
Jul 23 '05 #2
INSERT INTO TABLE1(FIELD1,FIELD2)
SELECT ABC, XYZ from TABLE2 where bla bla

or

INSERT INTO TABLE1(A,B,C)
SELECT X,Y, 'some static text' FROM TABLE2

the number of columns must tally


Chad Richardson wrote:
Is there a way in SQL Server 2000 to extract data from a table, such that the result is a text file in the format of "Insert Into..." statements, i.e. if the table has 5 rows, the result would be 5 lines of :

insert into Table ([field1], [field2], .... VALUES a,b,c)
insert into Table ([field1], [field2], .... VALUES d, e, f)
insert into Table ([field1], [field2], .... VALUES g, h, i)
insert into Table ([field1], [field2], .... VALUES j, k, l)
insert into Table ([field1], [field2], .... VALUES m, n, o)

Thanks in advance


Jul 23 '05 #3
You can by creating a calculated column that does the insert format:

select 'insert into table (id, name, phone) values (' +
cast(id as varchar(10)) + ',' +
quotename(name,'''') + ',' +
quotename(phone,'''') + ')'
from namelist

results:
insert into table (id, name, phone) values (1, 'James', 'Smith')
insert into table (id, name, phone) values (2, 'John', 'O''Kieth')

--
David Rowland
For a good User and Performance monitor, try DBMonitor
http://dbmonitor.tripod.com

Jul 23 '05 #4
You can by creating a calculated column that does the insert format:

select 'insert into table (id, name, phone) values (' +
cast(id as varchar(10)) + ',' +
quotename(name,'''') + ',' +
quotename(phone,'''') + ')'
from namelist

results:
insert into table (id, name, phone) values (1, 'James', 'Smith')
insert into table (id, name, phone) values (2, 'John', 'O''Kieth')

--
David Rowland
For a good User and Performance monitor, try DBMonitor
http://dbmonitor.tripod.com

Jul 23 '05 #5
Thanks all for the responses!
"Chad Richardson" <chad@NIXSPAM_chadrichardson.com> wrote in message
news:11*************@corp.supernews.com...
Is there a way in SQL Server 2000 to extract data from a table, such that
the result is a text file in the format of "Insert Into..." statements,
i.e. if the table has 5 rows, the result would be 5 lines of :

insert into Table ([field1], [field2], .... VALUES a,b,c)
insert into Table ([field1], [field2], .... VALUES d, e, f)
insert into Table ([field1], [field2], .... VALUES g, h, i)
insert into Table ([field1], [field2], .... VALUES j, k, l)
insert into Table ([field1], [field2], .... VALUES m, n, o)

Thanks in advance

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Betikci Boris | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.