[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
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
?
418 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

650 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