Post reply

SQL Insert Statement that increments based on Line Seq

  • February 17, 2016 at 2:04 pm

    #311447

    To all:

    I am looking for help to create an insert statement that will import records from one table into another. When importing, the code will need to check to see if the Account already exists, if it does, figure out the next number in the "Line Sequence" to increment the next field value.

    Can someone help me understand the best way to do this?

    Here is a copy of the sample tables and data I would like to use:

    -- Create Temp Tables

    CREATE TABLE #GetNewEmails (GID int, AccountID int, Email varchar(255))

    CREATE TABLE #EmailAddrTbl (EID int, AccountID int, LineSeq int, Email varchar(255))

    -- Existing Table #EmailAddrTbl

    INSERT INTO #EmailAddrTbl (EID, AccountID, LineSeq, EMail) VALUES (1, 1234, 1, 'test1@test.com')

    INSERT INTO #EmailAddrTbl (EID, AccountID, LineSeq, EMail) VALUES (2, 1234, 2, 'test2@test.com')

    INSERT INTO #EmailAddrTbl (EID, AccountID, LineSeq, EMail) VALUES (3, 1234, 3, 'test3@test.com')

    INSERT INTO #EmailAddrTbl (EID, AccountID, LineSeq, EMail) VALUES (4, 4444, 1, 'test4@test.com')

    INSERT INTO #EmailAddrTbl (EID, AccountID, LineSeq, EMail) VALUES (5, 5555, 1, 'test5@test.com')

    INSERT INTO #EmailAddrTbl (EID, AccountID, LineSeq, EMail) VALUES (6, 6666, 1, 'test6@test.com')

    INSERT INTO #EmailAddrTbl (EID, AccountID, LineSeq, EMail) VALUES (7, 7777, 1, 'test7@test.com')

    INSERT INTO #EmailAddrTbl (EID, AccountID, LineSeq, EMail) VALUES (8, 8888, 1, 'test8@test.com')

    INSERT INTO #EmailAddrTbl (EID, AccountID, LineSeq, EMail) VALUES (9, 8888, 2, 'test9@test.com')

    INSERT INTO #EmailAddrTbl (EID, AccountID, LineSeq, EMail) VALUES (10,9999, 1, 'test10@test.com')

    -- Add these emails to Table #EmailAddrTbl and increment LineSeq column by AccountID if record already exists

    INSERT INTO #GetNewEmails (GID, AccountID, Email) VALUES (1, 1234, 'test11@test.com')

    INSERT INTO #GetNewEmails (GID, AccountID, Email) VALUES (1, 1234, 'test12@test.com')

    INSERT INTO #GetNewEmails (GID, AccountID, Email) VALUES (1, 1234, 'test13@test.com')

    INSERT INTO #GetNewEmails (GID, AccountID, Email) VALUES (1, 8888, 'test14@test.com')

    INSERT INTO #GetNewEmails (GID, AccountID, Email) VALUES (1, 9999, 'test15@test.com')

    INSERT INTO #GetNewEmails (GID, AccountID, Email) VALUES (1, 0000, 'test16@test.com')

    SELECT * FROM #GetNewEmails

    SELECT * FROM #EmailAddrTbl

  • February 17, 2016 at 2:45 pm

    #1859392

    Probably something like this:

    INSERT INTO #EmailAddrTbl WITH(TABLOCKX)

    (AccountID,

    LineSeq,

    Email)

    SELECT AccountID,

    MaxLineSeq + ROW_NUMBER() OVER( PARTITION BY AccountID ORDER BY GID),

    Email

    FROM #GetNewEmails n

    CROSS APPLY( SELECT ISNULL( MAX(LineSeq), 0) MaxLineSeq

    FROM #EmailAddrTbl e

    WHERE e.AccountID = n.AccountID)x;

    The query hint is there to prevent incorrect values caused by concurrency.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply