?
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
Medium Priority
?
415 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 800 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 1200 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

764 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