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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy