How to increment a variable in an insert statment

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)

How to increment a variable in an insert statment

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-04-29 : 21:24:10

Hello All,

I’m declaring and setting a variable to 200, I want this variable to increment by one in my insert statement. A sample of my insert statement is shown. Please advice.

Example:
declare @Count_Order INT
set @ Count_Order = 200

INSERT INTO Table (Name, Count_Order) VALUES (Lisa, @ Count_Order )
INSERT INTO Table (Name, Count_Order) VALUES (Tom, @ Count_Order)
INSERT INTO Table (Name, Count_Order) VALUES (Sue, @ Count_Order)

Result:
NAME Count_Order
Lisa 200
Tom 201
Sue 202

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-29 : 21:41:14

INSERT INTO Table (Name, Count_Order) VALUES (Lisa, 200 )
INSERT INTO [table] (Name, Count_Order) VALUES(Tom, (SELECT MAX(Count_Order)+1 FROM [table]));
INSERT INTO [table] (Name, Count_Order) VALUES(Sue, (SELECT MAX(Count_Order)+1 FROM [table]));

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-30 : 01:04:17

u can have a identity function on that column by identity(200,1)
identity(value,incrementingcount)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-04-30 : 03:02:23

quote:
Originally posted by whitefang

INSERT INTO Table (Name, Count_Order) VALUES (Lisa, 200 )
INSERT INTO [table] (Name, Count_Order) VALUES(Tom, (SELECT MAX(Count_Order)+1 FROM [table]));
INSERT INTO [table] (Name, Count_Order) VALUES(Sue, (SELECT MAX(Count_Order)+1 FROM [table]));

This is not entirely correct unless 200 is the current maximum Count_order, which in case it would make more sense to do this:

INSERT INTO [table] (Name, Count_Order) VALUES (Lisa, (SELECT MAX(Count_Order)+1 FROM [table]))
INSERT INTO [table] (Name, Count_Order) VALUES (Tom, (SELECT MAX(Count_Order)+1 FROM [table]))
INSERT INTO [table] (Name, Count_Order) VALUES (Sue, (SELECT MAX(Count_Order)+1 FROM [table]))

Having count_order as an IDENTITY column would be even better in this situation.

- Lumbago

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-30 : 05:37:09

quote:
Originally posted by pras2007

Hello All,

I’m declaring and setting a variable to 200, I want this variable to increment by one in my insert statement. A sample of my insert statement is shown. Please advice.

Example:
declare @Count_Order INT
set @ Count_Order = 200

INSERT INTO Table (Name, Count_Order) VALUES (Lisa, @ Count_Order )
INSERT INTO Table (Name, Count_Order) VALUES (Tom, @ Count_Order)
INSERT INTO Table (Name, Count_Order) VALUES (Sue, @ Count_Order)

Result:
NAME Count_Order
Lisa 200
Tom 201
Sue 202

declare @Count_Order INT
set @ Count_Order = 200

INSERT INTO Table (Name, Count_Order) VALUES (Lisa, @ Count_Order )
INSERT INTO Table (Name, Count_Order) VALUES (Tom, @ Count_Order+1)
INSERT INTO Table (Name, Count_Order) VALUES (Sue, @ Count_Order+2)

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-30 : 05:48:55

For the given example that makes no sense because you can type 201 faster then typing @count_order+1.
What if the real insert is selecting the data from another table?


declare @offset int = 199
insert Table (Name, Count_Order)
select
Name,
row_number() over (order by something)+@offset
from input_table
where ...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources