October 12, 2004 at 11:42 am
I am completely unfamiliar with cursors but I think I need one now. I am going through BOL...which is how I found info on cursors, but I am still a bit unclear.
I am trying to insert values into a table with a select on another table. However, one of the values needs to autoincrement a key id concatenated with a string.
Bascially:
INSERT INTO EmpLicns (ElcEEID, ElcLicenseID, ElcNumber, ElcSystemID)
select EecEEID, 'DRIVER' as ElcLicenseID, EecUDField04, 'User'+1 as ElcSystemID
FROM EmpComp
My problem is that ElcSystemID needs to say User1, User2, User3, etc. Can someone help?
Thanks
Ryan
October 12, 2004 at 2:17 pm
you could always stage into a temp table:
SELECT IDENTITY( INT, 1, 1) as seq
,* --column list
INTO #stage
FROM sourcetable
INSERT INTO destinationtable( column list)
SELECT ..., 'user'+CONVERT(VARCHAR(10), seq)
FROM #stage
October 12, 2004 at 2:26 pm
select EecEEID, 'DRIVER' as ElcLicenseID, EecUDField04,
ElcSystemID='user' + cast(((select count(*) from EmpComp as emp where emp.EecEEID<EmpComp.EecEEID) +1) as varchar)
FROM EmpComp
order by EecEEID
October 12, 2004 at 2:27 pm
without temp tables, make sure it is sorted:
INSERT INTO EmpLicns (ElcEEID, ElcLicenseID, ElcNumber, ElcSystemID)
select EecEEID, 'DRIVER' as ElcLicenseID, EecUDField04,
ElcSystemID='user' + cast(((select count(*) from EmpComp as emp where emp.EecEEID<EmpComp.EecEEID) +1) as varchar)
FROM EmpComp
order by EecEEID
October 13, 2004 at 6:54 am
Thanks to you both. Both solutions worked properly.
Ryan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply