Insert Data Into a New Table

Last Post 10 Apr 2002 07:10 AM by Vengador. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
EJenkins
New Member
New Member

--
09 Apr 2002 09:20 AM
I have a list of a 200 SalesPerson ID’s in a table (SalesIDs) that I want to insert into a new table. However, I want the new table to contain each ID 12 times—once for each month of the year. So the new table would look something like this:

ID1, 01, 2002
ID1, 02, 2002
ID1, 03, 2002
.
.
.
ID200,10,2002
ID200,11,2002
ID200,12,2002

I can’t figure out what how to step through the SalesID table and create 12 new rows for each ID. Any clues would be appreciated.

Thanks,
Eugene Jenkins
Vengador
New Member
New Member

--
10 Apr 2002 07:10 AM
Declare @nYear int

Set @nYear = 2002

Select SalesPersonID, Month, Year
From SalesID ,
( Select 1 Month , @nYear Year
Union
Select 2 Month, @nYear Year
Union
Select 3 Month , @nYear Year
Union
.
.
Select 12 Month, @nYear Year
) Calendar

Jim_SQL
New Member
New Member

--
10 Apr 2002 07:15 AM
you can brute force this using a cursor to doing twelve inserts per SalesId
or you can find a common value to all the Salesperson records that enables you to link to another table with twelve records in it... for example, in our database customers are associated to one company, so I can create a table with twelve records for the companycode '01' ; then using select I get twelve rows per IDCustomer

insert into tblname
select IDCustomer
from tblCustomers
join tblTempCompany
on tblCustomers.companycode = tblTempCompany.companycode
-- this returns one row with IDCustomer for each row it links to in tblTempCompany

If you already have a common value, such as year, then you could link to a table of months, etc.

EJenkins
New Member
New Member

--
10 Apr 2002 09:11 AM
Thanks. That was very helpful.

You are not authorized to post a reply.

Acceptable Use Policy