SQL Server Insert Row: Inserting a row per “group”

I am wanting to insert multiple rows into a table based on the “Material” column. Attached below is a snippet of the table:

+----------+---------+-----------+
| Material | Station | BuildTime |
+----------+---------+-----------+
| ABC      |    #1   |         5 |
| ABC      |    #2   |        10 |
| ABC      |    #3   |        15 |
| DEF      |    #1   |         7 |
| DEF      |    #2   |        12 |
| DEF      |    #3   |        19 |
| GHI      |    #1   |        11 |
| GHI      |    #2   |        24 |
| GHI      |    #3   |        13 |
+----------+---------+-----------+

As you can see, there are three types of material here at three different stations – making for a total of 9 entries. Let’s say I wanted to add a “Station #4” row for each type of material. How can I insert per material “group”?

In my specific case, the “buildtime” value of Station #4 will all be identical with a value of 50.

This seems simple if there were truly only three material groups, but in the actual case, there are hundreds. Is there a way to parse through them and insert a row per group?

Thanks in advance.

Answer

You can use insert:

insert into t (material, station, buildtime)
   select distinct material, '#4', 50
   from t;

Advertisement

Answer