Insert same row multiple times within SQL table based on unique value in field 1

Question

I currently have a table where column 1 is a unique identifier for the remaining columns and I need to insert a new value into the table for each unique identifier. The example is as follows:

Column 1   Column 2   Column 3 
10         Address    True 
10         City       False
10         State      True
20         Address    True
20         City       True
20         State      True

I need to insert a new row based upon each unique identifier in Column 1, like so:

Column 1   Column 2   Column 3
10         Address    True
10         City       False
10         State      True
*10         NEW        NEW*
20         Address    True
20         City       True
20         State      True
*20         NEW        NEW*

For some reason, the SQL script for the quick lookup and insert is just escaping me on a Monday morning. Any help would be appreciated.

Thanks!

Column 1 is not unique here.

Kermit
– Kermit

2013-03-18 15:58:48 +00:00
Commented Mar 18, 2013 at 15:58 — Kermit
– Kermit, Commented Mar 18, 2013 at 15:58

Marlin Pierce · Accepted Answer · 2013-03-18 17:36:53Z

1

insert into table-name
(Column1, Column2, Column3)
select Column1, 'NEW', 'NEW*' from table-name group by Column1

You can alternatively use a distinct in the sub-select, but I have started switching to group by, which can be more flexible if I want to change a query to count or something.

edited Mar 18, 2013 at 17:36

answered Mar 18, 2013 at 16:04

Marlin Pierce

10.1k5 gold badges33 silver badges56 bronze badges

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

4 Comments

Chris Over a year ago

Thank you @Marlin Pierce, Aarolama Bluenk and AshReva. I'm trying to use a hybrid of the suggestions. My apologies for the use of 'unique' in this case. I would consider myself an average SQL user at best so still learning. At this time, I've tried INSERT INTO table-name VALUES (X, Y, Z) WHERE Column 1 = Random Value. The issue with that code obviously is that I would have to do that for each value of Column 1 (the thing I'm trying to avoid). I'm hoping Marlin's suggestion will do the quick lookup to resolve that.

Chris Over a year ago

Thank you again. So I think the thing I'm missing is the quick look up to insert the row multiple times into the table based on each new value in Column 1. I believe I have over 1,000 "unique" values in Column 1 that need to have this additional row added to it. Any thoughts?

Marlin Pierce Over a year ago

Does the "select Column1, 'NEW', 'NEW*' from table-name group by Column1" not give you exactly what you want to insert? If so, my answer should work.

Chris Over a year ago

Hi @Marlin Pierce. Worked flawlessly. Thank you!

Pekka · Accepted Answer · 2013-05-15 13:36:47Z

1

You can try at variant of Marlin Pierce's suggestion of:

INSERT INTO [table-name]
([Column 1], [Column 2], [Column 3])
SELECT [Column 1], 'NEW', 'NEW*' from [table-name] where [Column 2] = 'Address' group by [Column 1];

This creates a single new row for each of your existing rows.

See the SqlFiddle for executable sample.

edited May 15, 2013 at 13:36

answered Mar 18, 2013 at 20:51

Pekka

3,67431 silver badges47 bronze badges

1 Comment

Chris Over a year ago

Thank you again. So I think the thing I'm missing is the quick look up to insert the row multiple times into the table based on each new value in Column 1. I believe I have over 1,000 "unique" values in Column 1 that need to have this additional row added to it. Any thoughts?

Collectives™ on Stack Overflow

Insert same row multiple times within SQL table based on unique value in field 1

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related