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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 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
52326 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
52326 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
52326 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
Aged Yak Warrior

USA
550 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
52326 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  
 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.09 seconds. Powered By: Snitz Forums 2000