Insert multiple rows in 1 table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert multiple rows in 1 table

Author

Topic

Matt-21
Starting Member

11 Posts

Posted - 2011-03-10 : 04:40:02

I have an MS SQL table called routingoperations. It lists all manufacturing operations per part number for example
partid, operationnumber, machine, descrption

with values similar to;

P1234, 10, RMA, Issue Raw Material
P1234, 20, FOR, Forge to Drawing
P1234, 30, FIN, Final Inspection
P1235, 10, RMA, Issue Raw Material
P1235, 20, FOR, Forge to Drawing
P1235, 30, FIN, Final Inspection
P1236, 10, RMA, Issue Raw Material
P1236, 20, FOR, Forge to Drawing
P1236, 30, FIN, Final Inspection

total 40000+ rows

What I would like to do is alter the manufacturing route so it read as follows;

P1234, 10, RMA, Issue Raw Material
P1234, 20, FOR, Forge to Drawing
P1234, 30, HEA, Heat Treat
P1234, 40, FIN, Final Inspection
P1234, 50, TES, Independent Test
P1235, 10, RMA, Issue Raw Material
P1235, 20, FOR, Forge to Drawing
P1235, 30, HEA, Heat Treat
P1235, 40, FIN, Final Inspection
P1235, 50, TES, Independent Test
P1236, 10, RMA, Issue Raw Material
P1236, 20, FOR, Forge to Drawing
P1236, 30, HEA, Heat Treat
P1236, 40, FIN, Final Inspection
P1236, 50, TES, Independent Test

I thought the most controlled way to do this would be to delete all operations except operation 10 (this is the same for all parts) and then use the insert statement to add the following;

partid, 20, FOR, Forge to Drawing
partid, 30, HEA, Heat Treat
partid, 40, FIN, Final Inspection
partid, 50, TES, Independent Test

My problem is I have only ever inserted multiple rows with a select statement when I have populated an empty table with fields from a second table.

Is it possible to insert multiple rows into a single table based on values from that table i.e

insert into routingoperations

(partid, operationnumber, machine, descrption)

values

(partid, 20, FOR, Forge to Drawing)

where SUBSTRING(partid, 1, 1) = "P"

Matt

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-10 : 05:36:38

Let's say you are starting with a table routingoperations with only entries for operationnumber=10.
Then you can do this:
insert routingoperations(partid, operationnumber, machine, descrption)
select partid, 20, 'FOR', 'Forge to Drawing' from routingoperations where left(partid,1)='P' and operationnumber=10 union all
select partid, 30, 'HEA', 'Heat Treat' from routingoperations where left(partid,1)='P' and operationnumber=10 union all
select partid, 40, 'FIN', 'Final Inspection' from routingoperations where left(partid,1)='P' and operationnumber=10 union all
select partid, 50, 'TES', 'Independent Test' from routingoperations where left(partid,1)='P' and operationnumber=10

But be sure to have a test!

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-10 : 06:34:56

why don't you just do a update on the operationnumber ?


update t
set    operationnumber = n.operationnumber
from   yourtable t
       inner join
       (
           select operationnumber = 10, machine = 'RMA' union all
           select operationnumber = 20, machine = 'FOR' union all
           select operationnumber = 30, machine = 'HEA' union all
           select operationnumber = 40, machine = 'FIN' union all
           select operationnumber = 50, machine = 'TES' 
       ) n on t.machine = n.machine
where  t.operationnumber  <> n.operationnumber

KH
[spoiler]Time is always against us[/spoiler]

Matt-21
Starting Member

11 Posts

Posted - 2011-03-10 : 07:33:31

Many thanks webfred/khtan
You guys are brilliant! Wise words about testing though and my sentiments exactly. The table is actually a test table created to display only the routes that match certain material, size and process. This is why I like the idea of deleting all rows but the first (which we always be "issue raw material") and then adding what we consider Best Practice for the rest of the routes. All I need to do now is delete the rows in the live routing table and insert the rows from my new table which I think I know how to do.

Matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources