Auto-Increment on a non-primary key column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Auto-Increment on a non-primary key column

Author

Topic

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-09-30 : 13:23:29

I have a junction table that keeps track of questions associated with an audit.

This is what it looks like right now:
*Audit_ID***Question_ID
***101*********_1_*****
***102*********_2_*****
***103*********_3_*****

Is there a way to add a a SortOrder column that will auto-increment based on the Audit_ID? So for each new audit, it reseeds back to one after inserting a new record.

Audit_ID***Question_ID***SortOrder
**101***********10*********_1_*****
**101***********11*********_2_*****
**101***********12*********_3_*****
**102***********10*********_1_*****
**102***********11*********_2_*****
**102***********12*********_3_*****
**103***********12*********_1_*****

Note: I can just drop and recreate the table for now so there's no need to use ALTER.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 13:28:35

[code]
SELECT ROW_NUMBER() OVER (PARTITION BY Audit_ID ORDER BY Question_ID ASC) AS SortOrder,Audit_ID,Question_ID
FROM Table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-09-30 : 13:44:44

quote:
Originally posted by visakh16

SELECT ROW_NUMBER() OVER (PARTITION BY Audit_ID ORDER BY Question_ID ASC) AS SortOrder,Audit_ID,Question_ID
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

So is this used for creating a view? I need it to be an actual column that can be updated. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 13:52:34

you can either form a view with this

or add a new column SortOrder in table and update it like


UPDATE t
SET SortOrder = rn
FROM (SELECT SortOrder,ROW_NUMBER() OVER (PARTITION BY Audit_ID ORDER BY Question_ID ASC) AS rn
      FROM Table)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

memorykills
Starting Member

18 Posts

Posted - 2011-09-30 : 13:55:33

create trigger table_insert on temptable FOR INSERT AS
BEGIN
update temptable
set sortorder = b.sortorder
from
temptable
inner join
(
select row_number() OVER( Partition by audit_id order by question_id) as sortorder, audit_id, question_id
from temptable
) b
on temptable.audit_id = b.audit_id and temptable.question_id =b.question_id
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 14:01:53

quote:
Originally posted by memorykills

create trigger table_insert on temptable FOR INSERT AS
BEGIN
update temptable
set sortorder = b.sortorder
from
temptable
inner join
(
select row_number() OVER( Partition by audit_id order by question_id) as sortorder, audit_id, question_id
from temptable
) b
on temptable.audit_id = b.audit_id and temptable.question_id =b.question_id
END

this will end up updating all records in temptable for each insertion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

memorykills
Starting Member

18 Posts

Posted - 2011-09-30 : 14:05:04

yes. I know. but if there is an auto-increase identity column by which we can find the latest added record, then the trigger can just update sortorder column for the records with same audit_id as the latest record. :-)

quote:
Originally posted by visakh16

quote:
Originally posted by memorykills

create trigger table_insert on temptable FOR INSERT AS
BEGIN
update temptable
set sortorder = b.sortorder
from
temptable
inner join
(
select row_number() OVER( Partition by audit_id order by question_id) as sortorder, audit_id, question_id
from temptable
) b
on temptable.audit_id = b.audit_id and temptable.question_id =b.question_id
END

this will end up updating all records in temptable for each insertion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-09-30 : 14:07:42

quote:
Originally posted by memorykills

yes. I know. but if there is an auto-increase identity column by which we can find the latest added record, then the trigger can just update sortorder column for the records with same audit_id as the latest record. :-)
quote:
Originally posted by visakh16

quote:
Originally posted by memorykills

create trigger table_insert on temptable FOR INSERT AS
BEGIN
update temptable
set sortorder = b.sortorder
from
temptable
inner join
(
select row_number() OVER( Partition by audit_id order by question_id) as sortorder, audit_id, question_id
from temptable
) b
on temptable.audit_id = b.audit_id and temptable.question_id =b.question_id
END

this will end up updating all records in temptable for each insertion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes, there is an identity column in the table. I will try both suggestions and report back to you; hopefully by the end of the day. Thanks!

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-10-03 : 11:31:08

So I tried using the following trigger:


CREATE TRIGGER SortAuditQuestions ON jntbl_AuditToQuestion FOR INSERT AS
BEGIN
UPDATE jntbl_AuditToQuestion
SET SortOrder = b.SortOrder
FROM
jntbl_AuditToQuestion
INNER JOIN
(
SELECT ROW_NUMBER() OVER( Partition BY Audit_ID ORDER BY ID) as SortOrder, Audit_ID, Question_ID
FROM jntbl_AuditToQuestion 
) b
ON jntbl_AuditToQuestion.Audit_ID = b.Audit_ID and jntbl_AuditToQuestion.Question_ID =b.Question_ID
END

It worked good but the problem is that, like Visakh16 said, is that it updates all rows after each INSERT. That's a problem because I need to allow users to modify the SortOrder of the questions.

So my question is this, is it possible to write a SQL INSERT like this:


INSERT INTO jntbl_AuditToQuestion (Audit_ID, Question_ID, SortOrder) VALUES (1234,101, [MAX ROW_NUMBER() OVER( Partition BY Audit_ID ORDER BY ID))

My syntax is incorrect, but do you know if that's possible?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 11:53:15

[code]
INSERT INTO jntbl_AuditToQuestion (Audit_ID, Question_ID, SortOrder)
SELECT 1234,101, ROW_NUMBER() OVER( Partition BY Audit_ID ORDER BY ID)
FROM ...
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-10-03 : 13:01:27

So I decided that for every INSERT, it doesn't include the SortOrder and then have a trigger that will UPDATE the new record with the max row_number + 1. This seems to work and doesn't reset the SortOrder when new records are inserted.

Below is the trigger. Thanks for all the help!!


CREATE TRIGGER SortAuditQuestions ON jntbl_AuditToQuestion FOR INSERT AS
BEGIN
DECLARE @AuditID as integer
SET @AuditID = (SELECT Audit_ID
FROM    jntbl_AuditToQuestion
WHERE   ID = (SELECT MAX(ID)  FROM jntbl_AuditToQuestion))
PRINT @AuditID

UPDATE jntbl_AuditToQuestion 
SET SortOrder = (SELECT count(*) 
FROM jntbl_AuditToQuestion
WHERE Audit_ID= @AuditID) 
WHERE Audit_ID= @AuditID
And SortOrder Is Null
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 13:02:33

quote:
Originally posted by tkepongo

So I decided that for every INSERT, it doesn't include the SortOrder and then have a trigger that will UPDATE the new record with the max row_number + 1. This seems to work and doesn't reset the SortOrder when new records are inserted.

Below is the trigger. Thanks for all the help!!

CREATE TRIGGER SortAuditQuestions ON jntbl_AuditToQuestion FOR INSERT AS
BEGIN
DECLARE @AuditID as integer
SET @AuditID = (SELECT Audit_ID
FROM jntbl_AuditToQuestion
WHERE ID = (SELECT MAX(ID) FROM jntbl_AuditToQuestion))
PRINT @AuditID

UPDATE jntbl_AuditToQuestion
SET SortOrder = (SELECT count(*)
FROM jntbl_AuditToQuestion
WHERE Audit_ID= @AuditID)
WHERE Audit_ID= @AuditID
And SortOrder Is Null
END

why it doesnt include sortorder?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-10-03 : 16:12:31

quote:
Originally posted by visakh16

quote:
Originally posted by tkepongo

So I decided that for every INSERT, it doesn't include the SortOrder and then have a trigger that will UPDATE the new record with the max row_number + 1. This seems to work and doesn't reset the SortOrder when new records are inserted.

Below is the trigger. Thanks for all the help!!

CREATE TRIGGER SortAuditQuestions ON jntbl_AuditToQuestion FOR INSERT AS
BEGIN
DECLARE @AuditID as integer
SET @AuditID = (SELECT Audit_ID
FROM jntbl_AuditToQuestion
WHERE ID = (SELECT MAX(ID) FROM jntbl_AuditToQuestion))
PRINT @AuditID

UPDATE jntbl_AuditToQuestion
SET SortOrder = (SELECT count(*)
FROM jntbl_AuditToQuestion
WHERE Audit_ID= @AuditID)
WHERE Audit_ID= @AuditID
And SortOrder Is Null
END

why it doesnt include sortorder?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I didn't include the SortOrder in the INSERT because it would allow the trigger to update the only record with a NULL value. The trigger will find the Audit_ID of the last record inserted and then set the SortOrder to the Audit's Count(*)--which essentially assigns the new question to be the last SortOrder for that particular Audit.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 00:44:06

sorry i didnt understand. Also you've written trigger in such a way that for every insert you're taking max id value alone and then using it to get audit_id and getting count. have you considered fact that there can be insertion of multiple records. In such cases i doubt whether it will as you need.

If your requirement is just taking max(rowno) + 1 why not doing it in insert itself rather than putting trigger

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-04 : 08:01:45

the following link got what exactly what you want

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/46b76698-10a5-4801-9c39-ae49fc8f4758

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources