autoincrement manually - SQL Server Forums

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)

autoincrement manually

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-06-24 : 04:06:25

Hi All,

This may sound quite easy for you, however I am a newbie in SQL.

So I have an identity field, which I wish to increment automatically every time I do an insert. However the starting integer has to be the MAX value from another table.

So for example I am doing an insert in a #temp table
INSERT INTO #temp(name, surname)
SELECT name, surname from table1

Now the personId of the #temp table has to start from the MAX of table2
Ie SELECT MAX(personId) from table2

The SELECT MAX(personId) from table2 can also be NULL, ie the first time I am inserting, so I also have to cater for this scenario.

can anyone help?

Thanks

Johann

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 04:07:51

DBCC CHECKIDENT

can be used to RESEED the table to start with a specific value.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 04:12:01

DBCC CHECKIDENT ( 'dbo.MyTable', RESEED, {New value here} )

E 12°55'05.25"
N 56°04'39.16"

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-06-24 : 04:18:47

This is what I tried to do so far, without luck

DECLARE @resourceId int
SET @resourceId = (SELECT MAX(resourceId) AS resourceID from Resources)

INSERT INTO #tempResources (resourceId, FileName, projectid)
@resourceId+1, SELECT FileName, projectid from #tempOld
WHERE projectid = 20

Can you tell me how your example will work?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 04:21:28

Not without all code.
For example, I have no idea of how #tempResources looks like.

Post full code.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 04:22:14

What is the objective of the procedure?
Copy all records for ProjectID 20 to a new project with an id of 21?

E 12°55'05.25"
N 56°04'39.16"

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-06-24 : 04:24:27

my purpose is this.

I am getting some values from an old database, and inserting it into a new database. For the first project it works fine, however, for the second project, I have to check the max value of the id, so that I can continue to increment with that value, and then force Identity insert in the table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 04:29:25

[code]CREATE TABLE #Temp
(resourceId int IDENTITY(1,1),
FileName varchar(100), projectid int)

DECLARE @resourceId int
SET @resourceId = (SELECT MAX(resourceId) AS resourceID from Resources)
INSERT INTO #Temp (FileName,projectid)
SELECT FileName, projectid from #tempOld
WHERE projectid = 20

INSERT INTO #tempResources (resourceId, FileName, projectid)
SELECT @resourceId+resourceId,FileName, projectid from #Temp [/code]

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-06-24 : 04:51:55

hi visakh

your method works, however is there any other way instead of doing 2 inserts in 2 temp tables?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 04:53:53

quote:
Originally posted by monfu

hi visakh

your method works, however is there any other way instead of doing 2 inserts in 2 temp tables?

Unless you do some extra bit of calculations on your data, i dont think you require the second temp table. You can directly populate it to your destination (final) table.

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-06-24 : 05:08:08

You cannot insert directly into the final table, because if it is not an incrementer, the autoincrement won't work, and if it is an autoincrementer, you will not be able to forse a direct insert in the field, as far as I know.

At the moment, my other option is a cursor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 05:11:46

quote:
Originally posted by monfu

You cannot insert directly into the final table, because if it is not an incrementer, the autoincrement won't work, and if it is an autoincrementer, you will not be able to forse a direct insert in the field, as far as I know.

At the moment, my other option is a cursor

Sorry didnt get you there. Why cant you put results from first temp table directly to final table if you dont have any other calculations or logic to perform? Even if it is identity column, you can insert explicit values to it by setting identity insert on to table.

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-06-24 : 05:13:59

ah yes you can insert the results from the first temp table into the final table, but that constitutes 2 insert statements, and I am trying to do something more elegant if possible

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources