Multiple insert into table with incrementing id? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple insert into table with incrementing id?

Author

Topic

SriSql13
Starting Member

3 Posts

Posted - 2008-12-30 : 14:30:24

i am trying to do multiple inserts from a table to the same table by changing a field value and i am also have the increment the id ( primary key ) by 1 with each insertion.

How can i increment the recordid with each insertion?
( i should not use identity auto increment )

i tried …

insert into table (id,…..)
select (max(id) + 1), …. from table

ERROR: Violation of PRIMARY KEY constraint ‘PK_IDtable1'. Cannot insert duplicate key in object ‘dbo.table1'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:35:58

isnt primary key of identity type? then it would autoincrement by itself.
Otherwise you have to use temporary table. something like


CREATE TABLE #temp
(
ID int identity(1,1),
--your fields
)

INSERT INTO #Temp (your fields)
SELECT... --your query here

INSERT INTO YourTable (ID,...)
SELECT (SELECT MAX(ID) FROM YourTable)+ t.ID,... other values
FROM #temp
DROP TABLE  #temp

SriSql13
Starting Member

3 Posts

Posted - 2008-12-30 : 15:17:19

quote:
Originally posted by visakh16

isnt primary key of identity type? then it would autoincrement by itself.
Otherwise you have to use temporary table. something like

CREATE TABLE #temp
(
ID int identity(1,1),
--your fields
)

INSERT INTO #Temp (your fields)
SELECT... --your query here

INSERT INTO YourTable (ID,...)
SELECT (SELECT MAX(ID) FROM YourTable)+ t.ID,... other values
FROM #temp
DROP TABLE #temp

i cant use the identity auto increment as it is possible to get gaps within the column.. and that is a requirement here

it still gives the same error as again i am trying to put it in my table without incrementing the id column...

is this possible...???
to directly set the seed of the identity....

CREATE TABLE [dbo].[temp1](
[ID] [int] identity (select max(id)+1 from ClientFieldMaster,1),
........ )

then i can just copy the table directly :)

Thank you .... :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 02:49:35

nope. its not possible. i didnt understand what you mean by gaps. I added identity column to temp table, not your final table. and its dropped and created each time, then how do you think you will get gaps?

SriSql13
Starting Member

3 Posts

Posted - 2008-12-31 : 13:42:40

quote:
Originally posted by visakh16

nope. its not possible. i didnt understand what you mean by gaps. I added identity column to temp table, not your final table. and its dropped and created each time, then how do you think you will get gaps?

Hello....

i was actually telling u that i cant use identity in my original table...
the temp table identity always starts with 1 .. but i need temp table it to start with the max id no: of my original table... and from there i can copy it to my original table...

so i kind of did it but not sure if its the rite way...

CREATE TABLE #temp
(
ID int identity(1,1),
--your fields
)
--resetting the temp identity to start with max id of my original table
DECLARE @TEMP int
SELECT @TEMP =
max(id) + 1
FROM OriginalTable
DBCC CHECKIDENT('temp1', RESEED,@TEMP)

INSERT INTO #Temp (your fields)
SELECT... --your query here

INSERT INTO YourTable (fields...)
SELECT (...)
FROM #temp
DROP TABLE #temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources