Never been to DZone Snippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

MSSQL 2005 - Add ID value to ID column when INSERTING (See related posts)

// @TableName is obviously the TABLE name u use
// @ColumnName is obviously the COLUMN name u use

---Get next ID number
	DECLARE 
		@ID int
	
	IF (SELECT count(*) FROM @TableName ) > 0
		BEGIN
			SELECT @ID  = max(ColumnName ) from @TableName
			SET @ID = @ID + 1 
		END
	ELSE
	BEGIN
		SET @ID  = 1
	END
to windows select mssql from 2005 INSERT id WHERE by dubby on Tue Sep 11 03:50:29 -0400 2007

Comments on this post

kram posts on Sep 11, 2007 at 08:17
I know that you probably know this, but for those of you who don't, you can define the ID column of your table to be an identity column, which will allow you to set the increment value as you like (defaults to 1).

This allows you to insert into a table without specifying the column at all and SQL Server will handle getting the next available number.

For example, if you had an employee table with 2 columns, ID and NAME, you could define ID as an identity column, then you could do:

INSERT INTO Employee (NAME)
VALUES('Some Name')

Now the ID field will be populated automatically for you on the INSERT

Very nice feature of SQL Server.

Hope that makes sense

You need to create an account or log in to post comments to this site.


Click here to browse all 4861 code snippets

Related Posts