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

[Chad Richardson]

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

[David Portas]

Vyas has just what you need:
http://vyaskn.tripod.com/code.htm#inserts

--
David Portas
SQL Server MVP
--

[zzzxtreme]

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

[dbmonitor]

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

[dbmonitor]

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

[Chad Richardson]

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