Jump to content

Keys and identities in SQL Server


Recommended Posts

I have the following stored procedure:

CREATE PROCEDURE [dbo].[usp_report1](  @startRowIndex int,  @maxRows int)ASDECLARE @TempItems TABLE(  temp_id int IDENTITY(1,1) PRIMARY KEY,  sid int)DECLARE @maxRow intSET @maxRow = (@startRowIndex + @maxRows) - 1SET ROWCOUNT @maxRowINSERT INTO @TempItems (sid)SELECT c.id AS sid FROM content_session AS c ORDER BY c.uid, c.cidSET ROWCOUNT @maxRowsSELECT t.temp_id, t.sid AS t_sid, s.*, c.title, c.launchcount, c.timetocomplete, c.credits, u.fname, u.lnameFROM @TempItems AS t  INNER JOIN content_session AS s ON	s.id = t.sid  INNER JOIN content AS c ON	s.cid = c.cid  INNER JOIN users AS u ON	s.uid = u.uidWHERE t.temp_id >= @startRowIndexSET ROWCOUNT 0GO

This creates a table variable, with two columns called temp_id and sid. temp_id is defined as an integer, identity column, and primary key for the table variable. The sid column is also an integer. Data gets inserted into the table variable from another table. The other table (content_session) contains a column called id that is also an integer identity primary key. The goal is to insert a list of IDs from content_session as the sid in the table variable, and have the temp_id column increment when each row is inserted.The problem I'm having is that the temp_id and sid columns in the table variable always match. That is, if I insert 5 values into the sid column, maybe 3, 8, 11, 14, and 20, then the temp_ids for those rows will also be 3, 8, 11, 14, and 20. I want them to the 1, 2, 3, 4, 5, I don't want this column to be a foreign key. Is there a setting I need to change or something else I can do to make this work the way I need it to?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...