generate insert statement from table

Question

Post reply

generate insert statement from table

bryan van ritter

SSCrazy

Points: 2270
More actions
April 6, 2014 at 1:42 pm

#306063

I have a table with the following columns:

customnr number wk1 wk2 wk3 wk4

1200 20 5 6 7 8

1300 10 4 3 4 6

now i want to generate an insert statement

which loops through the table and does this:

insert into test (customnr,number,wk1,columname) values ('1200','20','5','columname (wk1)')

insert into test (customnr,number,wk2,columname) values ('1200','20','6','columname (wk2)')

insert into test (customnr,number,wk3,columname) values ('1200','20','7','columname (wk3)')

insert into test (customnr,number,wk4,columname) values ('1200','20','8','columname (wk4)')

hope someone can help me, so with columname i mean a select FROM INFORMATION_SCHEMA.COLUMNS

kind regards,

Bryan

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

J Livingston SQL

SSC Guru

Points: 51550

April 6, 2014 at 1:51 pm

#1703597

this may help you

http://www.connectsql.com/2010/11/sql-server-management-studio-generate.html

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

bryan van ritter SSCrazy Points: 2270 More actions · Answer 2

bryan van ritter

SSCrazy

Points: 2270

April 7, 2014 at 6:38 am

#1703743

Hi

Thanks for the answer but i do know about this feauture,

But this is not what i am looking for. The table where i want to

Insert the records in should finally look something like this:

Custnr wk value

12000 wk1 3

14000. Wk2. 4

So something like pivoting

Bryan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

What do you want your output table to look like, using the sample data you've provided?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

How do you differentiate customnr & number from wk1, wk2, wk3, etc.?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

djj SSCoach Points: 18831 More actions · Answer 5

bryan van ritter (4/6/2014)

I have a table with the following columns:

customnr number wk1 wk2 wk3 wk4

1200 20 5 6 7 8

1300 10 4 3 4 6

now i want to generate an insert statement

which loops through the table and does this:

insert into test (customnr,number,wk1,columname) values ('1200','20','5','columname (wk1)')

insert into test (customnr,number,wk2,columname) values ('1200','20','6','columname (wk2)')

insert into test (customnr,number,wk3,columname) values ('1200','20','7','columname (wk3)')

insert into test (customnr,number,wk4,columname) values ('1200','20','8','columname (wk4)')

hope someone can help me, so with columname i mean a select FROM INFORMATION_SCHEMA.COLUMNS

kind regards,

Bryan

This will do the same thing:

INSERT INTO Test (customnr,number,wk1,columname)



select customnr, number, wk1, 'columname (wk1)'

FROM Table1

INSERT INTO Test  (customnr,number,wk1,columname)

select customnr, number, wk2, 'columname (wk2)'

FROM Table1

INSERT INTO Test  (customnr,number,wk1,columname)

select customnr, number, wk3, 'columname (wk3)'

FROM Table1

INSERT INTO Test  (customnr,number,wk1,columname)

select customnr, number, wk4, 'columname (wk4)'

FROM Table1

Luis Cazares SSC Guru Points: 183686 More actions · Answer 6

djj (4/7/2014)

This will do the same thing:

Why would you want to scan the table four times when you can do it just once?



CREATE TABLE #Test( customnr int, number int, wk int, columnname varchar(128));

CREATE TABLE #Source( customnr int, number int, wk1 int, wk2 int, wk3 int, wk4 int);

INSERT #Source VALUES( 1200, 20, 5, 6, 7, 8);

INSERT #Source VALUES( 1300, 10, 4, 3, 4, 6);

INSERT #Test

SELECT customnr,

	number,

	wk,

	columnname

FROM #Source

CROSS APPLY(VALUES(wk1, 'columnname(wk1)'),

		(wk2, 'columnname(wk2)'),

		(wk3, 'columnname(wk3)'),

		(wk4, 'columnname(wk4)'))u(wk, columnname)

SELECT *

FROM #Test

GO

DROP TABLE #Test

DROP TABLE #Source

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

djj SSCoach Points: 18831 More actions · Answer 7

djj

SSCoach

Points: 18831

April 7, 2014 at 8:15 am

#1703800

Why would you want to scan the table four times when you can do it just once?

Because I forget about CROSSAPPLY as I have not used it as often as I should.:-)

Luis Cazares SSC Guru Points: 183686 More actions · Answer 8

djj (4/7/2014)

Why would you want to scan the table four times when you can do it just once?

Because I forget about CROSSAPPLY as I have not used it as often as I should.:-)

Been there 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

djj (4/7/2014)

Why would you want to scan the table four times when you can do it just once?

Because I forget about CROSSAPPLY as I have not used it as often as I should.:-)

Dwain's article[/url] will encourage you to use it more 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bryan van ritter SSCrazy Points: 2270 More actions · Answer 10

Hi guys,

thanx for all your response, but i noticed that i have not made it clear enough. below you see two tables. table dummy is the situation

from where i want to start. this data has been read from an excel sheet into a sql server table. the second table is what i want it

actually to be. this is just a fraction of the data. so what do i want. i want to build a procedure which loops through table one

and print for each record a insert statement so i can insert the records into table two. i hope i made it clear like this. thanx in advance for

all your help.

TABLE DUMMY

number year product1 product2 product3 <== column names

1100 2011 1 5 9

1100 2012 2 6 10

1200 2013 3 7 11

1200 2014 4 8 12

TABLE ORIGINAL

number year description value

1100 2011 product1 1

1100 2011 product2 5

1100 2011 product3 9

1100 2012 product1 2

1100 2012 product2 6

1100 2012 product2 10

1200 2013 product1 3

1200 2013 product2 7

1200 2013 product3 11

1200 2014 product1 4

1200 2014 product2 8

1200 2014 product3 12

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

bryan van ritter (4/12/2014)

i want to build a procedure which loops through table one

and print for each record a insert statement so i can insert the records into table two.

Wouldn't this be easier?

INSERT INTO TABLE2 (number, [year], [description], value)



SELECT number, [year], x.[description], x.value

FROM #DUMMY d

CROSS APPLY (VALUES

	('product1', product1),

	('product2', product2),

	('product3', product3)

) x ([description], value)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bryan van ritter SSCrazy Points: 2270 More actions · Answer 12

bryan van ritter

SSCrazy

Points: 2270

April 26, 2014 at 6:13 am

#1708950

Hi Chris,

thanx for your answer helped me a lot.

regards,

bryan