INSERT multiple rows with SELECT and an array

Question

In EXCEL/VBA I can program my way out of a thunderstorm, but in SQL I am still a novice. So apologies, after much Googling I can only get partway to a solution which I presume ultimately will be pretty simple, just not wrapping my head around it.

I need to create an INSERT script to add multiple rows in a 3-column table. A simple insert would be:

INSERT INTO table VALUES(StoreId, ItemID, 27)

First hurdle is dynamically repeat this for every StoreID in a different table. Which I think becomes this:

INSERT INTO table 
SELECT (SELECT StoreID FROM Directory.Divisions), ItemID, 27)

If that is actually correct and would effectively create the 50-60 rows for each store, then I'm almost there. The problem is the ItemID. This will actually be an array of ItemIDs I want to feed in manually. So if there are 50 stores and 3 ItemIDs, it would enter 150 rows. Something like:

ItemID = (123,456,789,246,135)

So how can I merge these two ideas? Pull the StoreIDs from another table, feed in the array of items for the second parameter, then my hardcoded 27 at the end. 50 stores and 10 items should create 500 rows. Thanks in advance.

Quick note about site culture here: you don't need to apologize for your lack of experience in a particular domain; it's okay (preferred, even) just to focus on the details of the problem you're facing. — Jerry Federspiel
– Jerry Federspiel, Commented Aug 31, 2015 at 17:54
VALUES implies you are doing manual input. Are StoreID and ItemID coming from a specific table? If so, you are probably going to have to do INSERT INTO [table] SELECT StoreID, ItemID, 27 as [column_name] FROM [other_table] — Sam CD
– Sam CD, Commented Aug 31, 2015 at 18:02
No, the ItemIDs I want as an array I can edit directly in the script. The StoreIDs I am pulling from another table as shown. I thought perhaps setting an array at the top of some kind, so it's easy to edit those item ids when needed. — Jerry Beaucaire
– Jerry Beaucaire, Commented Aug 31, 2015 at 18:05

Vamsi Prabhala · Accepted Answer · 2015-08-31 21:43:18Z

1

You can use into to insert into the target table. To generate itemid's you will have to use union all with your values and cross join on the divisions table.

select 
d.storeid, 
x.itemid, 
27 as somecolumn
into targettablename
from Directory.Divisions d
cross join (select 123 as itemid union all select 456 union all select 789...) x

Edit: If the table to insert into isn't created yet, it should be created before inserting the data.

create table targettable as (store_id varchar(20), item_id varchar(20), 
                             somecolumn int);

insert into targettable (store_id, item_id, somecolumn)
select 
d.storeid, 
x.itemid, 
27
from Directory.Divisions d
cross join (select 123 as itemid union all select 456 union all select 789...) x

edited Aug 31, 2015 at 21:43

answered Aug 31, 2015 at 18:07

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

5 Comments

Jerry Beaucaire Over a year ago

This looks perfect. When I run it without the INTO line, I get a perfect output showing all stores linked to all items, 1000s of correct entries. But when I add the INTO line, I get an error An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name.

Vamsi Prabhala Over a year ago

i modified the query to use an alias for 27. try it.

Jerry Beaucaire Over a year ago

Great, thanks. It seems like this is trying to create a new table rather than insert into an existing table. targettablename is an existing table, when I run the script it says: There is already an object named 'targettablename' in the database.

Jerry Beaucaire Over a year ago

Ok, worked that out. The Syntax looks solid, but I'm running into foreign key constraints, I'll figure out where that is coming from. Thanks.

Jerry Beaucaire Over a year ago

Worked perfectly in the end. Thanks again.

NigelK · Accepted Answer · 2015-08-31 18:08:16Z

Firstly you need your array of item ids in a table of some sort. Either a permanent table, table variable or temporary table. For example using a temporary table, which you prefix with a hash symbol:

CREATE TABLE #ItemIds (item_id int)
INSERT INTO #ItemIds VALUES (1)
INSERT INTO #ItemIds VALUES (2)
...
INSERT INTO #ItemIds VALUES (10)

Then this should do the trick:

INSERT INTO table
SELECT StoreId, item_Id, 27
FROM Directory.Divisions, #ItemIds

The results set from the SELECT will be inserted into 'table'. This is an example of a cartesian join. Because there is no join condition, every row from Directory.Divisions is joined to every row in #ItemIds. Hence if you have 50 stores and 10 items, that will result in 50 x 10 = 500 rows.

sarh · Accepted Answer · 2015-08-31 22:06:34Z

0

You may declare table variable for item IDs and use CROSS JOIN to multiply division records to items: http://sqlfiddle.com/#!3/99438/1

create table Divisions(StoreId int)
insert into Divisions values (1), (2), (3)

declare @items table(ItemID int)
insert into @items values (5), (6), (7)

-- insert into target(stireid, itemid, otherColumn)
select d.StoreId, i.ItemID, 27
from Divisions d 
cross join @items i

answered Aug 31, 2015 at 22:06

sarh

6,7174 gold badges28 silver badges37 bronze badges

Collectives™ on Stack Overflow

INSERT multiple rows with SELECT and an array

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related