CODEWITHSUNDEEP
sql-server
Kevin_H
Kevin_H

Reputation: 33

Use SQL INSERT to insert multiple records from two lists

I have a list of employee numbers and a list of dates. I want to insert a row into a table for each employee number and date.

I could use:

BEGIN TRAN
SET XACT_ABORT ON

INSERT INTO TABLE1 (EMPNUM, DATE)
VALUES (1, '2020-04-06')
       (1, '2020-04-07')
       etc.

but that will require creating an extraordinary number of records manually.

Is there an easier way to accomplish my goal?

Upvotes: 1

Views: 732

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use cross join with values construct :

insert into table1 (empnum, date)
   select e.empnum, d.dates
   from (values(1), (2), (3), (4), (5) 
        ) e(empnum) cross join
        (values ('2020-04-06'), ('2020-04-07'), ('2020-04-08'), ('2020-04-09') 
        ) d(dates);

Upvotes: 2

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89071

That sounds like a CROSS JOIN, eg

with empnums as
(
  select cast(value as int) empnum from openjson('[1,2,3,4,5,6]')
), dates as
(
  select cast(value as date) date from openjson('["20200406","20200407","20200408","20200409"]')
)
select *
from empnums
cross join dates

Upvotes: 1

Related Questions