CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Crezzer7
Crezzer7

Reputation: 2335

SQL Server - Insert with multiple sub queries (3 tables)

I have a table called BankHoliday which contains a Date field along with the Id field.. This currently has all of the dates for this year's and next year's Bank Holidays.

I also have a table called Staff which contains StaffID, StaffName, Active

Now using SQL I am trying to write an insert statement into a table called Calendar in which these Bank Holiday Dates are inserted. This table contains CalendarDate, CalendarID and StaffID,

The StaffID is where my problem is...

The Question

How can I write an INSERT statement in SQL Server where it will fetch a list of the Active Staff Members along with each individual Bank Holiday Date, and then INSERT them into the Calendar table?

Example Data would return:

CalendarID  CalendarDate    StaffID
     1       31/08/2015        1
     2       31/08/2015        2
     3       31/08/2015        3
     4       31/08/2015        4
     5       31/08/2015        5
     6       25/12/2015        1
     7       25/12/2015        2
     8       25/12/2015        3
     9       25/12/2015        4
     10      25/12/2015        5

So to clarify the CalendarDate above will contain a list of BankHoliday Dates that are not currently in this table (WHERE NOT EXISITS)

The StaffID is retrieved from the Staff table where a list contains only Active members

enter image description here

Upvotes: 0

Views: 316

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Ther is an accepted answer already, so I'm to late - never mind. Just one hint: I think you could improve the design:

  1. You should create a date-table not only for bank holidays, but a running list of all dates. These dates you can mark as "IsBankHoliday". Such a date list is very usefull in many cases.
  2. I don't know what you are going to do with this, but normally you would not write data into a physical table, which you can retrieve that easily. So - probably! - it's not the best idea to do this...

Just my solution to show you the connection of the tables. In fact this is nothing else then the other solution with CROSS JOIN... Person4 is not active, therefore is missing:

declare @bankholidays TABLE(ID INT,TheDate DATE);
insert into @bankholidays values(1,{ts'2015-08-31 00:00:00'}),(2,{ts'2015-12-25 00:00:00'});

declare @staff TABLE(StaffID INT,StaffName VARCHAR(100could),Active BIT);
insert into @staff VALUES(1,'Person1',1),(2,'Person2',1),(3,'Person3',1),(4,'Person4',0),(5,'Person5',1);

declare @target TABLE(targetID INT,CalendarID INT,CalendarDate DATE,StaffID INT);

INSERT INTO @target
SELECT ROW_NUMBER() OVER(ORDER BY bh.TheDate,s.StaffID)
      ,bh.ID
      ,bh.TheDate
      ,s.StaffID
FROM @staff AS s,@bankholidays AS bh
WHERE s.Active=1;

SELECT * FROM @target

Upvotes: 1

StoicFnord
StoicFnord

Reputation: 193

I think you are looking for a CROSS JOIN.

INSERT INTO Calendar 
SELECT  b.CalendarDate, s.StaffID FROM BankHoliday b
CROSS JOIN Staff s
WHERE s.Active = 1

This will get a row for every bank holiday and every active staff member.

Upvotes: 4

Related Questions