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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -