Insert 7 records into temptable for each employee? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert 7 records into temptable for each employee?

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-11-12 : 12:38:26

I have 40 employees and need to insert 7 entries into a temp table for each employee but with out writing 7 separate INSERT INTO SELECT statements I am not sure how to do this. The goal would be to insert the numbers 1 through 7, EmployeeID, and Date through Date + 7. So after the insert the temp table would have data like

ID EMPID DATE
1 1 10-1-2013
2 1 10-2-2013
.....
1 2 10-1-2013
2 2 10-2-2013

And so on for all the employees. The EmoployeID would be taken from a table and the Date would be a variable passed in from the calling application. Is 7 different insert statements the only/best way to do this?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-12 : 12:52:19

this?


;With Numbers
AS
(
SELECT 0 AS N
UNION ALL
SELECT N + 1
FROM Numbers
WHERE N+ 1 <=7
)
SELECT t.ID,t.EMPID,t.[Date] + N
FROM Table t
CROSS JOIN Numbers n

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-11-12 : 12:56:08

I have never seen a statement like that, do you mind breaking it out for me to understand?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-12 : 12:58:54

quote:
Originally posted by Eagle_f90

I have never seen a statement like that, do you mind breaking it out for me to understand?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

I'm generating a number table using CTE (Common Table Expression) to generate 0 to 7 numbers. this is then cross joined with your table to repeat each record 0 to 7 . then i'm printing all fields out and adding number to date to make it date,date+1,date+2,...date+7 for different rows.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-11-12 : 13:02:46

I see, and how would I convert that to an INSERT statement? Would I just add INSERT INTO TABLE (COLS) before the WITH statement?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-12 : 13:11:02

nope...just before select


;With Numbers
AS
(
SELECT 0 AS N
UNION ALL
SELECT N + 1
FROM Numbers
WHERE N+ 1 <=7
)
INSERT INTO TableName(col1,col2,col3)
SELECT t.ID,t.EMPID,t.[Date] + N
FROM Table t
CROSS JOIN Numbers n

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-11-12 : 13:34:21

Thanks for the help, this is working perfectly.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-11-12 : 13:38:45

FYI, 0 to 7 would generate 8 rows; use 0-6 or 1-7 instead

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-12 : 13:56:06

quote:
Originally posted by ScottPletcher

FYI, 0 to 7 would generate 8 rows; use 0-6 or 1-7 instead

I know that

But OPs requirement said

EmployeeID, and Date through Date + 7

As per that I hope OP wanted 8 rows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources