Manual autonumber on INSERT - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Manual autonumber on INSERT

Author

Topic

dehahnes
Starting Member

5 Posts

Posted - 2011-03-07 : 13:27:52

I have this SP running as a scheduled task:

--------------------------------------------------------------------------------

USE [redkl002_1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ZIP_autorun_AddParticipationHistoryRecord]
AS
BEGIN
SET NOCOUNT ON;

INSERT participationhistory (participationhistoryid,groupschoolid,campaignid,participationtypeid,status)
SELECT {0 AS participationhistoryid, groupschoolid, 3 AS campaignid, 2 AS participationtypeid, 'Confirmed' AS status
FROM groupschool
WHERE (status = 'Active') AND (groupschoolid NOT IN
(SELECT groupschoolid
FROM participationhistory
WHERE (campaignid = 3) AND (participationtypeid = 2)))

END

--------------------------------------------------------------------------------

On its own, this works fine, but ideally I would prefer the field 'participationhistoryid' to function as an autonumber (instead of each record being set to '0'). The front end application which is used for this database does not allow me to set autonumber at SQL level. Therefore it would need to evaluate for each record that is inserted the MAX(participationhistoryid) + 1.

Is there any way I can do this?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-07 : 13:44:43

make participationhistoryid as an identity field.

dehahnes
Starting Member

5 Posts

Posted - 2011-03-07 : 13:50:29

quote:
Originally posted by pk_bohra

make participationhistoryid as an identity field.

I understand this would be the easiest thing to do, BUT....

The tables are created via a front end application. I cannot change the table definitions, as it will cause the front end application to stop working

The solution therefore needs to be found within the INSERT statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources