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
407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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