Is this a good query?

• Magy

  SSCarpal Tunnel

  Points: 4217

  More actions

  August 2, 2005 at 9:06 am

  #88232

  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)

• Phil Parkin

  SSC Guru

  Points: 247125

  More actions

  August 2, 2005 at 9:14 am

  #578878

  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://www.sqlservercentral.com/columnists/awarren/checkyoursqlserveridentity.asp

  Regards

  ---

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  August 2, 2005 at 10:12 am

  #578900

  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.