October 15, 2009 at 2:16 pm
I have a query and I'm trying to loop through the results; however, I'm only getting 1 result, whereas I should get about 550. Can you point me in the right direction on this one?
declare @unitid varchar(16),
@assetdesc varchar(41),
@acqdate datetime,
@cost float
BEGIN
SELECT @unitid = UNITID,
@cost = PURCCOST,
@acqdate = PURCDATE,
@assetdesc = VEHDESC
FROM INFOR.TRAIN.IMSV7.COMPVEH LEFT JOIN FA00100
ON COMPVEH.UNITID = FA00100.ASSETID
WHERE FA00100.ASSETID IS NULL
SELECT @unitid AS UNITID, @cost AS COST, @acqdate AS ACQDATE, @assetdesc AS ASSETDESC
END
Thank you for your time,
Jordon
October 15, 2009 at 2:18 pm
Where's the loop? I see no WHILE statement before the BEGIN.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 15, 2009 at 2:20 pm
I'm new to T-SQL, so I'm sure my code isn't right. I want to loop through all the records and then display them.
October 15, 2009 at 2:26 pm
Welcome Newbie !! 🙂
I will counsel you to turn away from the dark side of procedural thinking and embrace the set-based truth.
SELECT UNITID, PURCCOST as COST, PURCDATE as ACQDATE, VEHDESC as ASSETDESC
FROM INFOR.TRAIN.IMSV7.COMPVEH LEFT JOIN FA00100
ON COMPVEH.UNITID = FA00100.ASSETID
WHERE FA00100.ASSETID IS NULL
Simply put, loops are sorta kinda built in to SQL. It functions best when acting against sets of data, rather than you trying to force it to do one row at a time. Makes your job easier too.
Come back with some questions. Lots of people here will help you get off on the right foot.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 15, 2009 at 2:42 pm
So, here is the deal. I know that I can do it the way that you have described; however, I didn't put my full project here. The reason I'm using varibles is due to the fact, I'm going to be putting them in an insert statement. I figured if I can figure out how to loop through them just to view them, then I will have no problem with the insert statement. I know that I can use a select in my insert statement; however, I'm inserting more than just these varibles, these are just ones that I need to be looped and why I wrote it the way that I did. Does it make sense? Maybe this will help a little more. This is my full code. I want to copy all records to the FA00100 table that are in the compveh table, but aren't in the fa00100 table; however, I also have some fields that will always be the same.
declare @unitid varchar(16),
@assetindex int,
@assetidsuf smallint,
@shrtname char(15),
@assetdesc varchar(41),
@masterassetid char(19),
@structureid char(31),
@assetclassid char(15),
@locatnid char(15),
@acqdate datetime,
@cost float,
@assettype smallint,
@assetstatus smallint,
@proptype smallint,
@assetqty int,
@assetbegqty int,
@assetcurrmaint numeric(19,5),
@assetytdmaint numeric(19,5),
@assetltdmaint numeric(19,5),
@lastmaintdate datetime,
@assessedvalue numeric,
@mfgrname char(25),
@serlnmbr char(21),
@modelnumber char(21),
@warrentydate datetime,
@custodian char(25),
@noteindex numeric(19,5),
@dateadded datetime,
@deletedate datetime,
@location_id char(15),
@assetlabel char(19),
@verifieddate datetime,
@pin char(15),
@lastpurchline int,
@lastmntdate datetime,
@lastmnttime datetime,
@lastnmtuser char(15)
SELECT @assetindex = MAX(ASSETINDEX)+1 FROM FA00100
SELECT @unitid = UNITID,
@cost = PURCCOST,
@acqdate = PURCDATE,
@assetdesc = VEHDESC
FROM INFOR.TRAIN.IMSV7.COMPVEH LEFT JOIN FA00100
ON COMPVEH.UNITID = FA00100.ASSETID
WHERE FA00100.ASSETID IS NULL
SELECT @assetclassid = GPID FROM INFOR.TRAIN.IMSV7.compgp
WHERE HANID = '37'
SET @assetidsuf = '1';
SET @shrtname = ' ';
SET @masterassetid = ' ';
SET @structureid = ' ';
SET @locatnid = ' ';
SET @assettype = '1';
SET @assetstatus = '1';
SET @proptype = '1' ;
SET @assetqty = '1';
SET @assetbegqty = '1';
SET @assetcurrmaint = '0.00000';
SET @assetytdmaint = '0.00000';
SET @assetltdmaint = '0.00000';
SET @lastmaintdate = '1900-01-01 00:00:00.000';
SET @assessedvalue = '0.00000';
SET @mfgrname = ' ';
SET @serlnmbr = ' ';
SET @modelnumber = ' ';
SET @warrentydate = '1900-01-01 00:00:00.000';
SET @custodian = ' ';
SET @noteindex = '0.00000';
SET @dateadded = GETDATE();
SET @deletedate = '1900-01-01 00:00:00.000';
SET @location_id = ' ';
SET @assetlabel = ' ';
SET @verifieddate = '1900-01-01 00:00:00.000';
SET @pin = ' ';
SET @lastpurchline = '1';
SET @lastmntdate = GETDATE();
SET @lastmnttime ='1900-01-01 00:00:00.000';
SET @lastnmtuser ='sa'
INSERT INTO [FTEST].[dbo].[FA00100]
([ASSETINDEX]
,[ASSETID]
,[ASSETIDSUF]
,[SHRTNAME]
,[ASSETDESC]
,[EXTASSETDESC]
,[Master_Asset_ID]
,[STRUCTUREID]
,[ASSETCLASSID]
,[LOCATNID]
,[ACQDATE]
,[Acquisition_Cost]
,[ASSETTYPE]
,[ASSETSTATUS]
,[PROPTYPE]
,[ASSETQTY]
,[ASSETBEGQTY]
,[ASSETCURRMAINT]
,[ASSETYTDMAINT]
,[ASSETLTDMAINT]
,[LASTMAINTDATE]
,[ASSESSEDVALUE]
,[MFGRNAME]
,[SERLNMBR]
,[MODELNUMBER]
,[WARRENTYDATE]
,[CUSTODIAN]
,[NOTEINDX]
,[DATEADDED]
,[DELETEDATE]
,[Physical_Location_ID]
,[Asset_Label]
,[Verified_Date]
,[PIN]
,[LASTPURCHLINESEQ]
,[LASTMNTDDATE]
,[LASTMNTDTIME]
,[LASTMNTDUSERID])
VALUES
(@assetindex
,@unitid
,@assetidsuf
,@shrtname
,@assetdesc
,@assetdesc
,@masterassetid
,@structureid
,@assetclassid
,@locatnid
,@acqdate
,@cost
,@assettype
,@assetstatus
,@proptype
,@assetqty
,@assetbegqty
,@assetcurrmaint
,@assetytdmaint
,@assetltdmaint
,@lastmaintdate
,@assessedvalue
,@mfgrname
,@serlnmbr
,@modelnumber
,@warrentydate
,@custodian
,@noteindex
,@dateadded
,@deletedate
,@location_id
,@assetlabel
,@verifieddate
,@pin
,@lastpurchline
,@lastmntdate
,@lastmnttime
,@lastnmtuser)
October 15, 2009 at 2:49 pm
You're still thinking procedurally... do the INSERT as a set.
declare @InsertTimeStamp datetime
set @InsertTimeStamp = getdate() -- because we don't want this to change
INSERT INTO dbo.SomeTable (UnitID,AcqCost,AcqDate,AssetDesc,InsertTimeStamp)
SELECT UNITID, PURCCOST as COST, PURCDATE as ACQDATE, VEHDESC as ASSETDESC,@InsertTime
FROM INFOR.TRAIN.IMSV7.COMPVEH LEFT JOIN FA00100
ON COMPVEH.UNITID = FA00100.ASSETID
WHERE FA00100.ASSETID IS NULL
It's really a lot simpler once you get your mind around the concept. All I had to do was add one INSERT statement, and set up a variable so all inserted rows would have the exact same timestamp.
Trust me, this is how it's done. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 15, 2009 at 2:56 pm
I guess I'm not explaining myself. I know how to do it the way that you just suggested; however, did you see my variables that I have set to a static data? I don't know how to insert into all my fields at one time with the select statement as the fields that I need static.
October 15, 2009 at 2:59 pm
Edit: hit post too early. Working .....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2009 at 3:00 pm
Hi,
Look at the INSERT..SELECT syntax in Books On Line. You don't have to read all rows in a loop and have a bunch of variables to keep constant values and then insert row by row.
If you use INSERT..SELECT syntax, you can pass any values you want, not only taken from the source table. I didn't put all columns in the following query, but last two columns show how to pass a variable value and a constant to the destination table.
insert [FTEST].[dbo].[FA00100]([ASSETID], [Acquisition_Cost], [ACQDATE], [ASSETDESC], [LASTMAINTDATE], [LASTMNTDTIME])
SELECT UNITID, PURCCOST as COST, PURCDATE as ACQDATE, VEHDESC as ASSETDESC, @lastmaintdate, '1900-01-01 00:00:00.000'
FROM INFOR.TRAIN.IMSV7.COMPVEH LEFT JOIN FA00100
ON COMPVEH.UNITID = FA00100.ASSETID
WHERE FA00100.ASSETID IS NULL
Regards
Piotr
...and your only reply is slàinte mhath
October 15, 2009 at 3:04 pm
Jordon, look at how I used the @InsertTime variable in the insert.
The stuff you want to keep constant, variables are just fine.
I gotta run. I'm gonna call up a support group for you now. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 15, 2009 at 3:06 pm
I completely understand now! Thank you all! I'm going to try it, but I'm pretty sure that I can get it from here!
October 15, 2009 at 3:06 pm
jordon.shaw (10/15/2009)
I guess I'm not explaining myself. I know how to do it the way that you just suggested; however, did you see my variables that I have set to a static data? I don't know how to insert into all my fields at one time with the select statement as the fields that I need static.
You can mix static variables and columns in an insert...select statement
SELECT @assetclassid = GPID FROM INFOR.TRAIN.IMSV7.compgp
WHERE HANID = '37'
SET @assetidsuf = '1';
SET @shrtname = ' ';
SET @masterassetid = ' ';
SET @structureid = ' ';
SET @locatnid = ' ';
SET @assettype = '1';
SET @assetstatus = '1';
SET @proptype = '1' ;
SET @assetqty = '1';
SET @assetbegqty = '1';
SET @assetcurrmaint = '0.00000';
SET @assetytdmaint = '0.00000';
SET @assetltdmaint = '0.00000';
SET @lastmaintdate = '1900-01-01 00:00:00.000';
SET @assessedvalue = '0.00000';
SET @mfgrname = ' ';
SET @serlnmbr = ' ';
SET @modelnumber = ' ';
SET @warrentydate = '1900-01-01 00:00:00.000';
SET @custodian = ' ';
SET @noteindex = '0.00000';
SET @dateadded = GETDATE();
SET @deletedate = '1900-01-01 00:00:00.000';
SET @location_id = ' ';
SET @assetlabel = ' ';
SET @verifieddate = '1900-01-01 00:00:00.000';
SET @pin = ' ';
SET @lastpurchline = '1';
SET @lastmntdate = GETDATE();
SET @lastmnttime ='1900-01-01 00:00:00.000';
SET @lastnmtuser ='sa'
INSERT INTO [FTEST].[dbo].[FA00100]
([ASSETINDEX]
,[ASSETID]
,[ASSETIDSUF]
,[SHRTNAME]
,[ASSETDESC]
,[EXTASSETDESC]
,[Master_Asset_ID]
,[STRUCTUREID]
,[ASSETCLASSID]
,[LOCATNID]
,[ACQDATE]
,[Acquisition_Cost]
,[ASSETTYPE]
,[ASSETSTATUS]
,[PROPTYPE]
,[ASSETQTY]
,[ASSETBEGQTY]
,[ASSETCURRMAINT]
,[ASSETYTDMAINT]
,[ASSETLTDMAINT]
,[LASTMAINTDATE]
,[ASSESSEDVALUE]
,[MFGRNAME]
,[SERLNMBR]
,[MODELNUMBER]
,[WARRENTYDATE]
,[CUSTODIAN]
,[NOTEINDX]
,[DATEADDED]
,[DELETEDATE]
,[Physical_Location_ID]
,[Asset_Label]
,[Verified_Date]
,[PIN]
,[LASTPURCHLINESEQ]
,[LASTMNTDDATE]
,[LASTMNTDTIME]
,[LASTMNTDUSERID])
SELECT
@assetindex,
UnitID,
@assetidsuf,
@shrtname,
VEHDESC,
VEHDESC,
@masterassetid,
@structureid,
@assetclassid,
@locatnid,
PURCDATE,
PURCCOST,
@assettype,
@assetstatus,
@proptype,
@assetqty,
@assetbegqty,
@assetcurrmaint,
@assetytdmaint,
@assetltdmaint,
@lastmaintdate,
@assessedvalue,
@mfgrname,
@serlnmbr,
@modelnumber,
@warrentydate,
@custodian,
@noteindex,
@dateadded,
@deletedate,
@location_id,
@assetlabel,
@verifieddate,
@pin,
@lastpurchline,
@lastmntdate,
@lastmnttime,
@lastnmtuser
FROM INFOR.TRAIN.IMSV7.COMPVEH LEFT JOIN FA00100
ON COMPVEH.UNITID = FA00100.ASSETID
WHERE FA00100.ASSETID IS NULL
Does that do what you're trying to do? If not, can you explain in more detail please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2009 at 3:37 pm
One last question and I'll leave everybody alone. In this script, I'm inserting VEHDESC from IMSV7.COMPVEH into ASSETDESC in FA00100; however, VEHDESC is a datatype of varchar(256) and ASSETDESC is datatype varchar(41). Is there a way to tell SQL to only insert the first 41 characters?
October 15, 2009 at 3:39 pm
You have to explicitly convert the source value to the destination data type in this case, like this:
select convert(varchar(5), '123123123123')
Regards
Piotr
...and your only reply is slàinte mhath
October 15, 2009 at 3:43 pm
Worked perfectly, thanks!
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply