Solved

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

Posted on 2006-07-17
6
384 Views
Last Modified: 2012-05-05
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


0
Comment
Question by:dimsis
6 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 17121673
SELECT IDENTITY(int, 1,1) AS ID_Num, COLUMN1, COLUMN2, COLUMN3
INTO NewTable
FROM OldTable
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17121699
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17121701
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


0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 200 total points
ID: 17121705

AND IF YOU HAVE STRING VALUE 15 THEN USE THE BELOW SCRIPT


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


DECLARE C CURSOR FOR SELECT MEDIAID, PUBDATE FROM YOURTABLE

SET @ISSUE =  15
OPEN C
FETCH NEXT FROM C INTO @MEDIAID, @PUBDATE
WHILE @@FETCH_STATUS = 0
BEGIN

SET @ISSUE = @ISSUE +1
INSERT INTO ISSUE (MEDIAID, PUBDATE, ISSUE)
SELECT MEDIAID, PUBDATE, @ISSUE

FETCH NEXT FROM C INTO @MEDIAID, @PUBDATE
END
CLOSE C
 DEALLOCATE C
GO
0
 
LVL 25

Accepted Solution

by:
Mr_Peerapol earned 300 total points
ID: 17121718
If you use SQL Server 2005, try this:

INSERT INTO ISSUE (MEDIAID, PUBDATE, ISSUE)
SELECT MEDIAID, PUBDATE,  @STARTING_VALUE + ROW_NUMBER() OVER(ORDER BY 1)
FROM your_table

If SQL Server 2000, you might need to use a table variable like this (so you don't need to worry about having problems with duplicate naming issues as well as avoiding cursor):

DECLARE @TempTable (MEDIAID INT, PUBDATE DATETIME, SeqNo INT IDENTITY(1, 1))
INSERT INTO @TempTable (MEDIAID, PUBDATE)
SELECT MEDIAID, PUBDATE
FROM your_table

INSERT INTO ISSUE (MEDIAID, PUBDATE, ISSUE)
SELECT MEDIAID, PUBDATE,  @STARTING_VALUE + SeqNo - 1
FROM @TempTable
0
 

Author Comment

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question