CODEWITHSUNDEEP
sqlsql-serversql-insert
Mr Zach
Mr Zach

Reputation: 515

Insert multiple rows from multiple rows

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

Example:

Let's say I have three tables:

  1. Customers (CustomerId, Name)
  2. Gifts (GifId, GiftName)
  3. GiftsToCustomers (GiftId, CustomerId)

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.

Upvotes: 1

Views: 133

Answers (1)

Mureinik
Mureinik

Reputation: 312267

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

Upvotes: 3

Related Questions