Is this a good query?
-
I have the query below setup in an ASP script that inserts data from a web form into the SQL Server database.
It works, but I just want to get some thoughts on it and/or ways to improve it.
The query is fairly self-explainatory, but basically it inserts a date and an automatically generated/incremented primary key into a master ID table, then it grabs the max ID from that table(which should be the key that was just automatically generated) and puts it into a variable.
It then inserts everything from the form including the primary key contained within that variable into a separate table.
--
INSERT INTO FG_ID(SRdateAdded) VALUES(GetDate())
GO
Declare @NewKey Int
Set @NewKey=(SELECT MAX(SRid) FROM FG_ID)
INSERT INTO FG_attorney_info(SRattorneyID, SRattorneyNameFirst, SRattorneyNameLast, SRattorneyAddress1,
SRattorneyAddress2, SRattorneyCity, SRattorneyState, SRattorneyZip, SRattorneyPhone, SRcopyOnReports,
SRattorneyContact) VALUES
(@NewKey, 'Jones', 'Marc', '301 Maxtrix Way', 'Suite 999', 'Metropolis', 'NY', '11111', '393-203-0232',
1, 1)
-
Rather than using this
Set @NewKey=(SELECT MAX(SRid) FROM FG_ID)
Use this
Set @NewKey= scope_identity()
Check out this excellent article for more info on this:
http://qa.sqlservercentral.com/columnists/awarren/checkyoursqlserveridentity.asp
Regards
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
That is excellent Phil! Thanks for reading and passing on that article...
CREATE TABLE #Test( [ID] integer IDENTITY(1,1),
NewDate datetime)
INSERT INTO #Test( NewDate) VALUES( GETDATE())
DECLARE @NewKey integer
SET @NewKey= SCOPE_IDENTITY()
SELECT @NewKey
DROP TABLE #Test
I wasn't born stupid - I had to study.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply