CODEWITHSUNDEEP
sqlsql-server
Loofer
Loofer

Reputation: 6963

Can I Insert multiple rows based on a query in SQL Server

I need to insert a load of things into my database

Written out longhand the queries would be something like this (but with more things!)

Insert into MyTable (country_id, event) values (1001, 'up')
Insert into MyTable (country_id, event) values (1001, 'down')
Insert into MyTable (country_id, event) values (1002, 'up')
Insert into MyTable (country_id, event) values (1003, 'down')
....
Insert into MyTable (country_id, event) values (9999, 'up')
Insert into MyTable (country_id, event) values (9999, 'down')

Can I write some kind of magic sql join/merge wonder query that will get all the country_ids and all 'event' types and create the hundreds of insert statements I require?

Edit:
There is a table for country. Which should be inserted into MyTable are a result of an sql query, and each result of that query needs the 'up' and 'down' row in MyTable. There are enough that I don't want to curate the query by hand.

In real there are more than 'up' and 'down' but they could be entered by hand, or as a result of a further query.

Upvotes: 1

Views: 3750

Answers (4)

Satinder singh
Satinder singh

Reputation: 10198

http://sqlfiddle.com/#!3/0355b/10

DEMO SQL FIDDLE

Note: Both table schema must be same

Upvotes: 2

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

Create cartesian product of countries and event list and insert them at once:

insert into MyTable (country_id, event)
select countries.country_id, 
       e.[event]
  from countries
 cross join
 (
   select 'Up' [event]
   union all
   select 'Down'
 ) e

Upvotes: 7

Marco
Marco

Reputation: 57573

You could join VALUES like this:

INSERT INTO MyTable (country_id, event) 
VALUES (1001, 'up'),(1001, 'down'),(1002, 'up'),(1003, 'down')

Upvotes: 2

podiluska
podiluska

Reputation: 51494

You can skip the 

 Insert into MyTable (country_id, event) values

and replace it with

 Insert into MyTable (country_id, event) values (1001, 'down'),
 (1002, 'up'),
 (1003, 'down')

Or you could look at BULK INSERT or bcp if your data is coming from a file.

Or if you know what the countries and events are,

Insert MyTable (country_id, event) 
SELECT countryid, event 
FROM country, events

will put all combinations into your table

Upvotes: 2

Related Questions