AlexF777
asked on
looking for a subtle way to increment variable in SQL 2005
hello experts,
i am trying to increment counter within SQL 2005
for example, instead of doing
SELECT 1 UNION
SELECT 2 UNION
SELECT 3
I want to do
DECLARE @i smallint; SET @i = 1; -- instant initialization in SQL 2005 does not fly, i gather
INSERT INTO temp
SELECT @i UNION
SELECT @i++ UNION -- this is just an improvisation
SELECT @i++ -- ????
i think, in 2008 and 2012 they have cool shortcuts to do this, but what about SQL 2005
i would prefer not to do SET @i = @i + 1 in between every time
thanks in advance
i am trying to increment counter within SQL 2005
for example, instead of doing
SELECT 1 UNION
SELECT 2 UNION
SELECT 3
I want to do
DECLARE @i smallint; SET @i = 1; -- instant initialization in SQL 2005 does not fly, i gather
INSERT INTO temp
SELECT @i UNION
SELECT @i++ UNION -- this is just an improvisation
SELECT @i++ -- ????
i think, in 2008 and 2012 they have cool shortcuts to do this, but what about SQL 2005
i would prefer not to do SET @i = @i + 1 in between every time
thanks in advance
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jim Horn
You're going to have to paint us a bigger picture of what you're trying to pull off here.
ASKER
autoincrement: i want each consequitive SELECT statement to use the next value: i.e.
1, 2, 3, ...
1, 2, 3, ...
>i would prefer not to do SET @i = @i + 1 in between every time
Unless you can paint us a bigger picture of what your trying to pull off, this is the only solution that is guaranteed to work.
You could have an identity column in your table, but it will incriment on rows, not on INSERT statements that created those rows, so that works only if your INSERTS insert one row at a time.
Unless you can paint us a bigger picture of what your trying to pull off, this is the only solution that is guaranteed to work.
You could have an identity column in your table, but it will incriment on rows, not on INSERT statements that created those rows, so that works only if your INSERTS insert one row at a time.
ASKER
no, i want to increment my variable in memory
with SQL 2008 i could say
SELECT @i UNION
SELECT @i+= 1 UNION
actually, i probably could use an identity column: i could insert all my rows into the temp
table, then do an INSERT into a live table FROM SELECT and that will probably do it ...
with SQL 2008 i could say
SELECT @i UNION
SELECT @i+= 1 UNION
actually, i probably could use an identity column: i could insert all my rows into the temp
table, then do an INSERT into a live table FROM SELECT and that will probably do it ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.