June 24, 2008 at 2:07 am
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
June 24, 2008 at 2:13 am
this is my attempt at this problem
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
June 24, 2008 at 2:48 am
Try this..
DECLARE @resourceId int
SET @resourceId = (SELECT MAX(resourceId) AS resourceID from Resources)+1
INSERT INTO #tempResources (resourceId, FileName, projectid)
SELECT @resourceId, FileName, projectid from #tempOld
WHERE projectid = 20
June 24, 2008 at 2:56 am
Hi Matt,
I tried your code, however the resourceID is NULL
Also, if I am inserting for example 1200 rows, the @resourceId will always be the same number no? Ie MAX(resourceID) + 1
To do it that way, I need to use a cursor I think
June 24, 2008 at 3:26 am
Hi Johann,
Give the below a try. It will insert one record using the MAX(projectid) from another table.
I think the most important part is the SET IDENTITY_INSERT ON which will allow you to insert an identity value.
IF OBJECT_ID('tempdb.dbo.#tempResources') IS NOT NULL
DROP TABLE #tempResources
GO
IF OBJECT_ID('tempdb.dbo.#Resources') IS NOT NULL
DROP TABLE #Resources
GO
CREATE TABLE #tempResources
(
resourceId INTEGER IDENTITY,
FileName VARCHAR(40),
projectid INTEGER
)
CREATE TABLE #Resources
(
projectId INTEGER IDENTITY,
filename VARCHAR(40)
)
INSERT INTO #Resources (filename) values ('File1')
INSERT INTO #Resources (filename) values ('File2')
INSERT INTO #Resources (filename) values ('File2')
DECLARE @resourceId int
SELECT @resourceId = MAX(projectId) from #Resources --get your MAX value from the other table
/* This is the important part!*/
SET IDENTITY_INSERT #tempResources ON -- This will allow you to insert an IDENTITY value into resourceId
INSERT INTO #tempResources (resourceId, --Here's your insert using the value 1 greater than the MAX(projectId)
filename,
projectid)
SELECT
@resourceId+1,
FileName,
projectid FROM #Resources
WHERE projectid = 3
SELECT * FROM #tempResources -- return your results.
Cheers!!
Jim.
P.S. I'm using 2005 and was trying to do this using a table variable @tempResources and noted that I couldn't use SET IDENTITY_INSERT @tempResources ON. Anyone care to answer as an aside?
June 24, 2008 at 3:30 am
Hi
Basically I already thougth of that solution, ie 2 inserts, however I thought there was something more elegant, since I am quite new to SQL
However thanks for the help
Johann
June 24, 2008 at 5:28 am
I'm not sure this is what you're looking for, but maybe using the OUTPUT clause?:
INSERT INTO Person
OUTPUT PersonId INTO #Temp
.....
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply