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

    Regards

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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