DZone 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

Snippets Manager

Snippets has posted 5883 posts at DZone. View Full User Profile

MSSQL 2005 - Add ID Value To ID Column When INSERTING

09.11.2007

| 5531 views |

        // @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

Tags:

Comments

Mark Macumber replied on Thu, 2007/08/16 - 10:20pm

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