SQL INSERT Using WHILE LOOP and SELECT

Question

Need to add a bunch of repetitive records to the database and don't want to do all of it by hand.

Need to add the intial records by hand but then want to create a query I can run to essentially template rest of the records and then just change the one different field by hand.

Problem is, I don't know exactly what the initial records are until I enter them.

For example

 FieldOne | FieldTwo   | FieldThree
----------+------------+--------------
 Text One | Text Two A | int Three A
 Text One | Text Two B | int Three A
 Text One | Text Two C | int Three A
 ...

This all has to be done by hand. I then need to create:

 FieldOne | FieldTwo   | FieldThree
----------+------------+--------------
 Text One | Text Two A | int Three B
 Text One | Text Two B | int Three B
 Text One | Text Two C | int Three B
 ...

... repeat for all the different values in Field Two.

Hope that makes sense?

Essentially what I want to do is:

WHILE (SELECT ALL DISTINCT VALUES FIELDTWO)
BEGIN
INSERT INTO TABLE VALUES ('Same all the time', Value From the FieldTwo Select, 'Same all the time')
END

Can this be done in SQL Server and can you give me the exact T-SQL statement to do it?

Use the code button(like i've done with the sql) instead of </br> tags since it's impossible now to reformat your code. — Tim Schmelter
– Tim Schmelter, Commented Jul 18, 2012 at 13:21

Gordon Linoff · Accepted Answer · 2012-07-18 13:30:08Z

10

You should be able to do this:

INSERT INTO TABLE1 (fieldtwo, fieldone, fieldthree)
SELECT DISTINCT fieldtwo, 'Same all the time',  'Same all the time' From TABLE2

edited Jul 18, 2012 at 13:30

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

answered Jul 18, 2012 at 13:27

03Usr

3,4456 gold badges43 silver badges64 bronze badges

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

2 Comments

Hunter Over a year ago

Awesome. Simple but effective! Thanks.

NiallMitch14 Over a year ago

This answer has been so handy for a project I have been working on. Thank you so much for a nice specifc use-case insert query!

Collectives™ on Stack Overflow

SQL INSERT Using WHILE LOOP and SELECT

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related