473,224 Members | 1,474 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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 3057
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Tuong Do | last post by:
Hi, I am going to insert a large amount (200,000 records) of data into a table Is there a way that I can temporarily disable the log? so that the insertion run faster Thanks in advance
9
by: Hi5 | last post by:
Hi, Any Idea how, I can make an Insert statement to insert data into 6 different tables, that are all holding all data of my database? Is there any example? I would be grateful if you could...
1
by: Pratik Gupte | last post by:
I have created a database in .mdf format, but I am unable to insert data into its tables. Can anybody help how to insert data using ASP.Net 2.0 using SQL Server 2005 Express edition in windows...
0
by: Mamatha | last post by:
Hi When i clicked a button, i want to insert data from listview in VB.NET to Excel sheet. If you know the solution either above or below is ok for me. I know how to insert from a textfile,but...
2
by: savigliano | last post by:
hello, i am doing a date comparation and i have reallize that the data i have in my database (general date format) it is causing me problems, and because i don´t need the time data i would like to...
3
by: Subrat Das | last post by:
Hi, I have a java application which calls a stored procedure to insert data into a table.Multiple threads of java call the same procedure at the same time. Sometimes it happens that few threads send...
7
by: FNA access | last post by:
I have a form that demands an input mask for a text box. The text box has a mask of three numbers then a space a dash another space and three more numbers. This mask repeats itself upto a max of 4...
0
by: troydixon | last post by:
Hello, I am new at this, and have been trying to insert data into a table by using the footer of a gridview (which I dont like) or by using a detials view on the same page that is doing the...
2
by: Dhananjay | last post by:
Hi all , I have got problem when i am tring to exportGridview Data into Excel format. It is going into text format ,but what i want is if the field is number/currency then it should go into...
8
by: Betikci Boris | last post by:
Can not insert data into SQLite3 database through browser however i can easily insert data into my db from konsole, in both attmpts i used php 5.2.6 on 2.6.25.* linux kernel i think there is a...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.