asked on

How can i create an auto increment value inserted into table when i'm using the Insert INTO ... SELECT ?

How can i create an auto increment value inserted into table when i'm using the Insert INTO ... SELECT ?

example:
INSERT INTO ISSUE (MEDIAID, PUBDATE, ISSUE)
SELECT MEDIAID, PUBDATE, ISNULL(MAX(@STARTING_VALUE), 0) + 1

What i want to do is pass a starting value (a number) and then all the records inserted must count from this number .

For example if i pass @STARTING_VALUE=15 and the select returns 3 records, i want the following:

15, 21/7/2006, 15
15, 21/7/2006, 16
15, 21/7/2006, 17

imran_fast

SELECT IDENTITY(int, 1,1) AS ID_Num, COLUMN1, COLUMN2, COLUMN3
INTO NewTable
FROM OldTable

imran_fast

IF YOU HAVE EXISTING TABLE NAME ISSUE THEN USE THIS

DECLARE @MEDIAID VARCHAR(10),
@PUBDATE DATETIME,
@ISSUE INT

DECLARE C CURSOR FOR SELECT MEDIAID, PUBDATE FROM YOURTABLE

SET @ISSUE = ISNULL(MAX(@STARTING_VALUE), 0) + 1
OPEN C
FETCH NEXT FROM C INTO @MEDIAID, @PUBDATE
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO ISSUE (MEDIAID, PUBDATE, ISSUE)
SELECT MEDIAID, PUBDATE, @ISSUE

SET @ISSUE = @ISSUE +1
FETCH NEXT FROM C INTO @MEDIAID, @PUBDATE
END
CLOSE C
DEALLOCATE C
GO

Lowfatspread

use a temptable to order the rows...

SELECT IDENTITY(int, 0,1) AS ID_Num, MEDIAID, PUBDATE
INTO #temp
FROM selectTable
order by ....

then

INSERT INTO ISSUE (MEDIAID, PUBDATE, ISSUE)
SELECT MEDIAID, PUBDATE, @STARTING_VALUE + ID_Num
from #temp

SOLUTION

imran_fast

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Mr_Peerapol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dimsis

ASKER

Thank you all guys for the fast answers. We used Mr_Peerapol method finaly and even before reading the answer :)