auto increment if there is no value

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 if there is no value

Author

Topic

donjt81
Starting Member

11 Posts

Posted - 2010-05-17 : 12:45:03

Hi,

I am trying to create a table and also write the records for this table dynamically from vb.net.

These are my columns
ID
Created by
Create time

So from my code I will be saying something like "Insert into aboveTable VALUES (SWRTK, johnDoe, June 1st)"

But the problem is I might not always have an ID to write to the table. In those cases I want the ID to start with 9000 and auto increment everytime there is not an ID available. for ex 90001, 90002... etc.

So my final table would look something like this

SWRTK, myName, June 1st
STTJO, yourName, June 1st
90001, thisName, June 1st
SPPSD, johnDoe, June 1st
SJCVV, name1, June 1st
90002, nameA, June 1st
90003, nameX, June 1st

How do i do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:47:54

i really cant understand why you need to store alphabetical as well as sequence values in same column. you can use IDENTITY property to get sequence values.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:56:46

for your conditional value assignment what you can do is to create a trigger on table for insert and do like this


CREATE TRIGGER YourTrigger
ON TableName
AFTER INSERT
AS
BEGIN
DECLARE @Temp Table
(
ID int IDENTITY(1,1),
CreatedBy,
CreatedTime
) 

DECLARE @MaxID int

INSERT @Temp
(CreatedBy,
CreatedTime
)
SELECT CreatedBy,
CreatedTime
FROM INSERTED
WHERE ID IS NULL

SELECT @MaxID=MAX(ID)
FROM Table
WHERE ISNUMERIC(ID)=1

UPDATE t
SET t.ID =COALESCE(@MaxID,9000) + (tmp.ID -1)  
FROM YourTable t
INNER JOIN @Temp tmp
ON tmp.CreatedBy = t.CreatedBy
AND tmp.CreatedTime = t.CreatedTime
AND t.ID IS NULL

END

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

donjt81
Starting Member

11 Posts

Posted - 2010-05-17 : 13:01:27

Sorry my mistake. Those should not be alphabetical. They wll be smaller ID numbers like 27, 49, 33, etc...

so it will look like this
27, myName, June 1st
49, yourName, June 1st
90001, thisName, June 1st
33, johnDoe, June 1st
45, name1, June 1st
90002, nameA, June 1st
90003, nameX, June 1st

Thanks for the reply, i will try out what you posted above

Nikhil1home
Starting Member

23 Posts

Posted - 2010-05-17 : 20:14:26

quote:
Originally posted by donjt81

Sorry my mistake. Those should not be alphabetical. They wll be smaller ID numbers like 27, 49, 33, etc...

so it will look like this
27, myName, June 1st
49, yourName, June 1st
90001, thisName, June 1st
33, johnDoe, June 1st
45, name1, June 1st
90002, nameA, June 1st
90003, nameX, June 1st

Thanks for the reply, i will try out what you posted above

Nikhil1home
Starting Member

23 Posts

Posted - 2010-05-17 : 20:37:59

You should use Instead Of Trigger so the ID gets assigned before the records are being written to the disk. That way you do not have to perform the additional update operation.
Also try to avoid using in-memory table-variable in the trigger so the trigger does not affect server performance when you insert too many records at once.

CREATE TRIGGER YourTrigger
ON TableName
INSTEAD OF INSERT
AS
BEGIN
DECLARE @Max_ID INT

DECLARE @CustomID_Offset INT

SET @CustomID_Offset = 90000

SET @Max_ID = (SELECT MAX(ID) FROM TableName WHERE ID > @CustomID_Offset)

INSERT INTO TableName
SELECT ISNULL(@Max_ID, @CustomID_Offset)+ROW_NUMBER() AS ID,
CreatedBy, CreatedTime
FROM INSERTED
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-18 : 00:58:22

quote:
Originally posted by Nikhil1home

You should use Instead Of Trigger so the ID gets assigned before the records are being written to the disk. That way you do not have to perform the additional update operation.
Also try to avoid using in-memory table-variable in the trigger so the trigger does not affect server performance when you insert too many records at once.

CREATE TRIGGER YourTrigger
ON TableName
INSTEAD OF INSERT
AS
BEGIN
DECLARE @Max_ID INT

DECLARE @CustomID_Offset INT

SET @CustomID_Offset = 90000

SET @Max_ID = (SELECT MAX(ID) FROM TableName WHERE ID > @CustomID_Offset)

INSERT INTO TableName
SELECT ISNULL(@Max_ID, @CustomID_Offset)+ROW_NUMBER() AS ID,
CreatedBy, CreatedTime
FROM INSERTED
END

the above code wont complie at all. you cant use ROW_NUMBER like this

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-18 : 11:26:13

Just for fun I'll ask the question.. Why do you need to fill in Unknown values? It would seem that you have a design issue. I'm not sure if it's an exercise you'd like to go through. But, if you care to, you could try to describe what you are doing and maybe we can help with a better design? Up to you.

Nikhil1home
Starting Member

23 Posts

Posted - 2010-05-18 : 11:43:30

quote:
Originally posted by visakh16

quote:
Originally posted by Nikhil1home

You should use Instead Of Trigger so the ID gets assigned before the records are being written to the disk. That way you do not have to perform the additional update operation.
Also try to avoid using in-memory table-variable in the trigger so the trigger does not affect server performance when you insert too many records at once.

CREATE TRIGGER YourTrigger
ON TableName
INSTEAD OF INSERT
AS
BEGIN
DECLARE @Max_ID INT

DECLARE @CustomID_Offset INT

SET @CustomID_Offset = 90000

SET @Max_ID = (SELECT MAX(ID) FROM TableName WHERE ID > @CustomID_Offset)

INSERT INTO TableName
SELECT ISNULL(@Max_ID, @CustomID_Offset)+ROW_NUMBER() AS ID,
CreatedBy, CreatedTime
FROM INSERTED
END

the above code wont complie at all. you cant use ROW_NUMBER like this

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

Visakh,

I haven't tried the code and do not have the syntex memorized but I am sure with a minor tweaking, this can be a very efficient way to achieve what he is trying to do. If ROW_NUMBER() does not work directly in the INSERT query, you can use a CTE in the Insert query to make use of the ROW_NUMBER()

Something like this....(again not sure about the syntex)

With NewRecords (NewRecordID, CreatedBy, CreatedTime) AS
(
SELECT ROW_NUMBER(), CreatedBy, CreatedTime
FROM INSERTED
)
INSERT INTO TableName
SELECT ISNULL(@Max_ID, @CustomID_Offset)+NewRecordID AS ID,
CreatedBy, CreatedTime
FROM NewRecords

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-18 : 14:02:53

even then it does have a problem I think. dont you require this to be done only for ids which doesnt have passed small ID number?

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

Nikhil1home
Starting Member

23 Posts

Posted - 2010-05-18 : 15:32:31

quote:
Originally posted by visakh16

even then it does have a problem I think. dont you require this to be done only for ids which doesnt have passed small ID number?

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

True and that can be taken care of by a simple case statement in the insert query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources