2020. 2. 29. 14:28ㆍ카테고리 없음
My problem is one of my query is returning partycodes. Now, I also want a column which returns serial numbers along with it.
Life View Suite Serial Number Cs3
The serial numbers are not stored anywhere, they should be auto-generated. My query is combining two different databases and it's using union in it so I cannot use count in it. Is there any other way I can achieve this? For ex, now my query output ispartycode-R0R06791(3 row(s) affected)I want it like:partycode serial number- -R06048 1R06600 2R06791 3(3 row(s) affected)The serial number column should be auto generated in the select statement itself. Is there any system rowid I can use? I gave you three options below:1. If the column you are working with is a rowquidThen just just use newid exampleDECLARE @myid uniqueidentifierSET @myid = NEWIDPRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)2.
One is below if tyou are wanting to work with an idenity column:SELECT lastvalue FROM sys.identitycolumns WHERE objectid objectid('tablename'). Note: this os ok for 2005 but may not work in other versions3. If you ant to know the last row you just inserted useDECLARE @NewId AS intINSERT INTO HRs.Employees( /. column names./)VALUES ( /. column values./)SELECT @NewId = @@IdentityThe @newid will hold the last inserted record identity.I would recommend that you create a stored proc so that just in case in thenext version the method you are using changes it will not affect you code.Only the stored proc performing the function will be effected.Mark.the.dba@gmail.com.