Insert multiple rows from multiple rows

Question

How can I insert multiple rows from another table with multiple rows without using a cursor?

Example:

Let's say I have three tables:

The table customers and gifts can contain more then one row.

Now let's say I have 3 gifts in the table Gifts and I want to give this to all my customers.

Is there a way I can do this without using an cursor/loop through either the Customers or Gifts table?

I'm not looking for something like

insert into GiftsToCustomers 
    Select GiftId, @CustomerId 
    from Gifts

Where I have to do it on every row in customers/gifts.

Mureinik · Accepted Answer · 2015-06-15 20:25:12Z

3

A cross join should do the trick - you can use it to match each row in gifts with each row in custmers:

INSERT INTO GiftsToCustomers (GiftId, CustomerId)
SELECT      GiftId, CustomerId
FROM        Gifts
CROSS JOIN  Customers

answered Jun 15, 2015 at 20:25

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

1 Comment

Thank you. This is what i was looking for. I know it had to be some better way than using cursors.