February 5, 2010 at 8:17 am
I'm in the need of some help with my script. Basically, I'm pulling data from different tables and putting them into 1 table. This is my script:
USE [TRAIN]
GO
DECLARE @actkey int,
@addby varchar(30),
@adddttm datetime,
@compby varchar(12),
@compdttm datetime,
@compkey int,
@histkey int,
@inspkey int,
@inspno int,
@startdttm datetime,
@failed varchar(1)
SET @failed = 'N';
SELECT@actkey = ACTKEY,
@addby = ADDBY,
@adddttm = ADDDTTM,
@compby = COMPBY,
@compdttm = COMPDTTM,
@compkey = COMPKEY,
@histkey = HISTKEY,
@startdttm = STARTDTTM
FROM [dbo].[2009PassedInspections]
SELECT @inspkey = NEXTVAL +1
FROM IMSV7.ZEQ_132
SELECT @inspno = NEXTVAL +1
FROM IMSV7.ZEQ_133
INSERT INTO IMSV7.INWBFAT
(ACTKEY, ADDBY, ADDDTTM, COMPBY, COMPDTTM, COMPKEY, HISTKEY, STARTDTTM, FAILED, INSPKEY, INSPNO)
VALUES
(@actkey, @addby, @adddttm, @compby, @compdttm, @compkey, @histkey, @startdttm, @failed, @inspkey, @inspno)
UPDATE IMSV7.ZEQ_132
SET NEXTVAL = @inspkey
UPDATE IMSV7.ZEQ_133
SET NEXTVAL = @inspno
The majority of my data is coming from the 2009PassedInspections table. I want to take all of the data from there and put it into the INWBFAT table. At the same time, for each record, this script should pull the nextval +1 from the ZEQ_132 and the ZEQ_133 and post the in the INWBFAT, as well as update those two tables with the new NEXTVAL of +1. The problem is, this is only working for the 1st record and I need it to work for a little over 1000 records.
If I was doing this in PHP, I would just loop through the data to get my results that I'm wanting; however, I'm 100% positive on how to do that in T-SQL.
Can you please help?
Thank you for your time,
Jordon Shaw
February 5, 2010 at 8:33 am
This should work, it uses ROW_NUMBER() function to generate sequential no for each row.
USE [TRAIN]
GO
DECLARE @inspkey int,
@inspno int,
@failed varchar(1),
@recordcount int
SET @failed = 'N'
SELECT @inspkey = NEXTVAL + 1 FROM IMSV7.ZEQ_132
SELECT @inspno = NEXTVAL + 1 FROM IMSV7.ZEQ_133
INSERTIMSV7.INWBFAT( ACTKEY, ADDBY, ADDDTTM, COMPBY, COMPDTTM, COMPKEY, HISTKEY,
STARTDTTM, FAILED, INSPKEY, INSPNO )
SELECTACTKEY, ADDBY, ADDDTTM, COMPBY, COMPDTTM, COMPKEY, HISTKEY, STARTDTTM, @failed,
@inspkey + ROW_NUMBER() OVER( ORDER BY ACTKEY ) AS INSPKEY,
@inspno + ROW_NUMBER() OVER( ORDER BY ACTKEY ) AS INSPNO
FROM[dbo].[2009PassedInspections]
SELECT@recordcount = @@ROWCOUNT
UPDATE IMSV7.ZEQ_132 SET NEXTVAL = @inspkey + @recordcount
UPDATE IMSV7.ZEQ_133 SET NEXTVAL = @inspno + @recordcount
--Ramesh
February 5, 2010 at 8:35 am
jordan i think this example might help;
i'm using the power of set based operations and the row_nubmer() function to generate the needed values;
check out the SELECT statem3ent...if you run it and iut returns the data you would have wanted to insert (1000 rows for example), you could uncomment out the INSERT and run it;
DECLARE @inspkey int,
@inspno int
--get the max key, as it appears to be a field with no identity property
SELECT
@inspkey = NEXTVAL
FROM IMSV7.ZEQ_132
SELECT @inspno = NEXTVAL
FROM IMSV7.ZEQ_133
--get the key plus a rolling counter to insert 1000 at a time
--if the SELECT returns the correct results, uncomment out the INSERT command
--remember to TEST TEST TEST, don't do this on LIVE/Production unless you are sure it is working:
--INSERT INTO IMSV7.INWBFAT (INSPKEY,ACTKEY, ADDBY, ADDDTTM, COMPBY, COMPDTTM, COMPKEY, HISTKEY, STARTDTTM, FAILED, INSPNO)
SELECT
@inspkey + row_number() over (ORDER BY ACTKEY) AS INSPKEY,
ACTKEY,
ADDBY,
ADDDTTM,
COMPBY,
COMPDTTM,
COMPKEY,
HISTKEY,
STARTDTTM,
'N' AS FAILED,
@inspno + row_number() over (ORDER BY ACTKEY) AS INSPNO
FROM [dbo].[2009PassedInspections]
Lowell
February 5, 2010 at 8:35 am
ha; Ramesh and i came up with identical solutions;
Lowell
February 5, 2010 at 8:41 am
That worked perfectly, thank you both!
It's funny that you both came up with the same solution, at the same time!
Once again, thanks!
Jordon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply