How to create a number of record in an SQL query? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to create a number of record in an SQL query?

Tell me please how to get the sequental number of record in query. I need to create a query that contains a field in that is the sequental number of the record in the query. I’ve found an idea only how to do it in update but I need it in insert. Update after insert is impossible because I need to store such number of record into a primary key. That idea consists in using construnction @intCounter = fieldname = @intCounter + 1 but insert operator can’t use = operator. I need in this query insert into one table data selected from other one. cogito ergo sum
How about using identity column for the sequential number.
Refer to books online for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
You can use the IDENTITY() function – but only in an INSERT query. You could try something like this: SELECT T.MyColumn, IDENTITY(INT,1,1) AS Ctr
INTO #Temp
FROM dbo.MyTable AS T SELECT * FROM #Temp
You can also use the following method if you are inserting from ASP – there is the problem from ASP that it is not possible to grab @@IDENTITY or more appropriately scope_identity() (which I’ve found no one seems to use in the db world I move around in!) ASP Pseudo Code.
do iIdentityVariable = Select max(sequentialcolumnname) from tablex
insert into tablex (sequentialcolumnname, col1, col2, col3 etc) values (iIdentityVariable, val1, val2, val3 etc) while oConn.Error <> 0 ‘ i.e continue selecting max and attempting insert until no error Obviously you must set a Unique constraint on the sequentialcolumnname column to ensure an error is thrown on a duplicate insert attempt. This is pretty much the only version of sequentialidentities I’ve seen that’s usable from ASP – i.e. usable in the sense of knowing what value the identity is so you can insert it into other related tables. Way too many times I’ve seen people doing the above the other way round with no error checking – i.e insert into table, then select max(x) which obviously can give the wrong identity column if you have more than 1 user inserting at the same time. If anybody sees any problems with what I’ve described above let me know – seems to be a good solution to me. My preferred solution actually is to not use identity type columns at all, but to use a GUID – much easier to transport data between databases then. And you don’t have to do the error check above then too. Dave.

More than one way to skin that cat. You might get some ideas herehttp://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1&NoWebContent=1
–Frank
http://www.insidesql.de

I think Ilya was just looking for a "line counter" in the result set. I can certainly appreciate the concerns about inserting identity values in general, but there is no real problem here, as long as the code is used for the purpose for which it was created.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />I think Ilya was just looking for a "line counter" in the result set. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes, I think so, too. And my usual <b>answer</b> to such questions is to do this in your presentational layer. <br />Anyway, Ilya now has several ways to choose from.<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />I think Ilya was just looking for a "line counter" in the result set. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes, I think so, too. And my usual <b>answer</b> to such questions is to do this in your presentational layer. <br />Anyway, Ilya now has several ways to choose from.<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Yep.
Thank you for information but the function INDENTITY requires SELECT INTO construction that as I’ve understood going to create new table but I need to add into existing table so such construction I don’t know how to make working. Written onhttp://support.microsoft.com/support/kb/articles/q186/1/33.asp&NoWebContent=1&NoWebContent=1 for some reason returns in this column value 1 in all rows instead of sequental number. My be I’ve made an error in this construction:
SELECT rank = count(*), students.surname, students.stname, students.patronymic, students.foreing,
students.gender, psprstud.birthdate, psprstud.hsaddress,
psprstud.fam_pos, psprstud.dwell_type, psprstud.children, psprstud.nation,
psprstud.par_not
FROM students LEFT OUTER JOIN
psprstud ON students.reg_num = psprstud.reg_num
group by students.surname, students.stname, students.patronymic, students.foreing,
students.gender, psprstud.birthdate, psprstud.hsaddress,
psprstud.fam_pos, psprstud.dwell_type, psprstud.children, psprstud.nation,
psprstud.par_not
order by 1 cogito ergo sum
If all records in the existing table are to have a new value, why not declare that field as INT with IDENTITY – it’s already INT, right? Just insert data into all the other fields.
]]>