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
378 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now