June 2, 2005 at 5:34 pm
Im trying to fill a temp table with values that I am selecting from several tables for a maintenance system. We'll drop the normalization and make the table flat for ease of explanation:
tblMaintSched
PK: MaintID
Int: Mileage (3000, 30000, 60000)
VarChar(50): Task (change oil, change filter, change plugs)
so for every 3000 there is an oil change. I'd like to do something like:
INSERT INTO ##tblTmpMaintSched
SELECT Mileage, Task from tblMaintSched
but actually loop the mileage and task up to 100000. So instead of just inserting:
3000 Change Oil
I'm actually inserting:
3000 Change oil
6000 Change Oil
9000 Change Oil
all the way to
99000 Change Oil
I'd like to do this for every record that I select into the temp table,
can anyone think of a way to do this in the BE?
TIA
David
June 2, 2005 at 5:44 pm
create table #mileage (mileage int NOT NULL)
go
insert #milage (mileage) values (3000)
insert #milage (mileage) values (6000)
insert #milage (mileage) values (9000)
insert #milage (mileage) values (12000)
.
.
.
INSERT INTO ##tblTmpMaintSched
SELECT b.Mileage, a.Task from tblMaintSched a, #mileage b
giving yourself a cartesian product result.
June 2, 2005 at 6:40 pm
Thanks, but I believe that would require me to know all of the values taht I am inserting. I cannot hard code, since I only have one value but need to insert all multiples.
I made up a UDF which would work, but I am trying to do this all in the sproc, without a UDF.
--Debug Variables:
DECLARE @Mileage AS INT
DECLARE @Task AS VARCHAR(50)
SET @MileAge = 3000
SET @Task = 'Change oil'
DECLARE @tblTempVMI TABLE (Mileage INT, Task VARCHAR(50))
DECLARE @X AS INT
SET @X = @Mileage
WHILE @X <100000
BEGIN
INSERT INTO @tblTempVMI
SELECT @X, @Task
SET @X = @X + @Mileage
END
SELECT * FROM @tblTempVMI
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply