SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Insert 7 records into temptable for each employee?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eagle_f90
Constraint Violating Yak Guru

USA
424 Posts

Posted - 11/12/2013 :  12:38:26  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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

Edited by - Eagle_f90 on 11/12/2013 12:39:41

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/12/2013 :  12:52:19  Show Profile  Reply with Quote
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

USA
424 Posts

Posted - 11/12/2013 :  12:56:08  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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

India
52317 Posts

Posted - 11/12/2013 :  12:58:54  Show Profile  Reply with Quote
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

USA
424 Posts

Posted - 11/12/2013 :  13:02:46  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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

India
52317 Posts

Posted - 11/12/2013 :  13:11:02  Show Profile  Reply with Quote
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

USA
424 Posts

Posted - 11/12/2013 :  13:34:21  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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
Constraint Violating Yak Guru

USA
411 Posts

Posted - 11/12/2013 :  13:38:45  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/12/2013 :  13:56:06  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000