Link to home
Start Free TrialLog in
Avatar of webdork
webdork

asked on

SQL Multi Insert Statement

Some EE geniuses structured a color search query for me here:


Now I need an insert statement to add many rows without having to do so one by one.

Like so:

Insert to ProductColor

ColorName = 'Red' Where ProductID = 1-100
Avatar of Jerry Miller
Jerry Miller
Flag of United States of America image

Are you wanting to insert new rows or update existing?

To insert:
INSERT INTO ProductColor(fieldname1, fieldname2......ColorName)
VALUES(fieldname1Value, fieldname2Value,........'RED')
WHERE ProductID Between 1 and 100

To update:
UPDATE ProuctColor
SET ColorName = 'RED'
WHERE ProductID Between 1 and 100
Avatar of webdork
webdork

ASKER

I want to insert rows.

Table: Products
ProductID|ProductName
100|Garnet 1
101|Garnet 2
102|Garnet 3

Table: ProductColor

PID|ColorName
100|Red
101|Red
102|Red
Avatar of webdork

ASKER

Say I want to insert 4 rows with one statement. I enter a range of numbers and the rows are magically created.

ProductId range = 100-103
ColorName = 'RED'

INSERT INTO ProductColor(PID, ColorName)
VALUES(100,'RED')
VALUES(101,'RED')
VALUES(102,'RED')
VALUES(103,'RED')
You can do one thing. Create a temporary table as:

Create table #temp (PID int not null identity(minRange,1), colorName varchar(50) )

Put 100 as your example in the place of minRange in above schema. Then take the difference of the range and execute the following batch statement

Insert into #temp(colorName)
values ('Red')
Go 4 --Put the range of the value i.e. 103-100+1

Now insert into main table
INSERT INTO ProductColor(PID, ColorName)
select PID,colorName from #temp
Avatar of webdork

ASKER

Create the temp table manually or programmatically?
Avatar of webdork

ASKER

I don't understand this:

Go 4 --Put the range of the value i.e. 103-100+1
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
CORRECTION:

I accidentally swapped the two columns on the SELECT; the code should be:


INSERT INTO ProductColor ( PID, ColorName )
SELECT @PIDstart + num, @ColorName


[rather than
SELECT @ColorName, @PIDstart + num
]


Btw, the 9999 (really 10,000) is the # of rows to be inserted, not the highest PID value that can be inserted.
Avatar of webdork

ASKER

How do I drop the temp table?
I would just do

insert ProductColor(PID, ColorName)
select ProductID, 'Red'
from Products between 100 and 102 --start and end range
You can put the following syntax before creating temp table

If object_id('tempdb..#temp') is not null
        Drop table #temp
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of webdork

ASKER

thank you all.