Inserting into two tables - little help needed

  • Hello all.

    First of all, I wanted to thank everyone for participating in these forums and helping those who need help. Like me! I know you're taking time out of your day to help others. Sometimes, you just can't get the answer from a book or more importantly, some feedback!

    I am writing a SP which will take data from an old table (tens of thousands of records) and insert it into two related tables. I'm still early int he writing and testing phase so the code below is an early draft and a mock up.

    I have a table which contains basic customer information and account numbers. As a precautionary measure, I plan to take the account numbers out of the new table and store them in a separate table. So my old table may look like this:

    OldCustomers

    -------------

    OldCustomerID

    OldAccountNum

    FullName

    Address

    And my two new tables may look like this:

    NewCustomers

    ------------------

    NewCustomerID

    AccountID (FK)

    FullName

    Address

    Accounts

    ----------------

    AccountID

    AccountNumber

    So my LOOP logic is to:

    *Grab the first Account # from OldCustomers and insert into the Accounts Table.

    *Grab the ID from Accounts that was just created.

    *Insert the rest of the record into NewCustomers and take that ID and insert it as the FK.

    *Go to next record.

    So I have written code like this:

    [font="Courier New"]--INSERT Account Nums into ACCOUNTS Table First

    INSERT INTO ACCOUNTS

    ([AccountNumber],[Active])

    VALUES

    ((

    --Pull fron OldCustomers and get Account #. If empty (''), insert all zeros.

    CASE

    WHEN (SELECT [OldAccountNum] FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5) LIKE '' THEN

    '000000000'

    -- Insert Account # from OldCustomers

    ELSE

    (SELECT [OldAccountNum] FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5)

    END

    ),1)

    --INSERT into NewCustomers Table and also grab and insert ID of record from Accounts.

    INSERT INTO NewCustomers

    ([AccountID],[FullName],[Address])

    (

    SELECT (SELECT IDENT_CURRENT('ACCOUNTS')),[FullName],[Address]

    FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5)

    END[/font]

    In case you're wondering, the BETWEEN 1 and 5 clause is just for testing. Don't want to insert 75,000 records on a test.

    When I try to execute this, SQL Server barks at me:

    [font="Courier New"]sg 512, Level 16, State 1, Procedure sp_Insert...... Line 21

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    The statement has been terminated.

    Msg 547, Level 16, State 0, Procedure sp_Insert........, Line 40

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_NewCustomers_Accounts". The conflict occurred in database "MyDatabase", table "dbo.Accounts", column 'AccountID'.

    The statement has been terminated.[/font]

    I'm going to assume that both errors are coming from my lack of a loop. I am also assuming that it is trying to insert ALL of the Account Numbers from OldCustomer into Accounts FIRST before trying to insert even the first NewCustomer Record.

    Correct?

    Some guidance would be appreciated!!

  • I am not sure I follow your logic in this and I would think about the process flow. I would argue that if this is to unltimatley load 75,000 records you should not be doing and insert values command. Either way your probalem is the embbedded selcect returns more than one row which in not allow in the case statement.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • Thanks. Volume isn't an issue as this will be a one time load. But my logic is certainly questionable! =)

    Logic is simple.

    Take records from old table and insert them into two new tables which have a relationship.

    Any help?

  • my recommendation would be to get a select working to correctly display the information as you will ant in to be displayed in your new table. Then just add and insert statement infront of that select.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • What is the relationship? Can a customer have multiple accounts or can an account have multiple customers.

    >> I am also assuming that it is trying to insert ALL of the Account Numbers from OldCustomer into Accounts FIRST before trying to insert even the first NewCustomer Record.

    Yes, whatever statement you run first will occur first.

    The order or the statements could matter if you have a foreign key relationship created (and you really should).

  • After much tinkering and research, I think I have a prototype that works. Ended up using a cursor and parameters. It's not bulletproof but so far, this works as expected:

    [font="Courier New"]USE AcmeCars;

    GO

    DECLARE @OldAccountNum varchar(10), @FullName varchar(50), @Address varchar(50), @AccountID int;

    DECLARE CustomerCursor CURSOR FOR

    SELECT OldAccountNum, FullName, Address FROM OldCustomers

    OPEN CustomerCursor;

    -- Perform the first fetch.

    FETCH NEXT FROM CustomerCursor

    INTO @OldAccountNum, @FullName, @Address;

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --Insert the Account # from the old table into the new.

    INSERT INTO Accounts([AccountNum], [Active]) VALUES (@OldAccountNum, 1)

    --Get the ID

    SET @AccountID = (SELECT IDENT_CURRENT('ACCOUNTS'))

    --INSERT into NewCustomers Table and also grab and insert ID of record from Accounts.

    INSERT INTO NewCustomers([AccountID],[FullName],[Address])

    VALUES

    (@AccountID, @FullName, @Address)

    -- This is executed as long as the previous fetch succeeds.

    FETCH NEXT FROM CustomerCursor

    INTO @OldAccountNum, @FullName, @Address;

    END

    CLOSE CustomerCursor;

    DEALLOCATE CustomerCursor;

    GO

    [/font]

  • Am I missing something or would something like this will work correctly and much faster than the cursor?

    CREATE TABLE #OldCustomers(

    OldCustomerID int,

    OldAccountNum varchar(100),

    FullName varchar(100),

    Addressvarchar(100))

    CREATE TABLE #NewCustomers(

    NewCustomerID int,

    AccountID int,

    FullName varchar(100),

    Addressvarchar(100))

    CREATE TABLE #Accounts(

    AccountID int IDENTITY,

    AccountNumber varchar(100))

    INSERT INTO #Accounts( AccountNumber)

    SELECT DISTINCT OldAccountNum

    FROM #OldCustomers

    INSERT INTO #NewCustomers(

    NewCustomerID,

    AccountID,

    FullName,

    Address)

    SELECT OldCustomerID,

    AccountID,

    FullName,

    Address

    FROM #OldCustomers c

    JOIN #Accounts a ON c.OldAccountNum = a.AccountNumber

    If you read the article linked on my signature, you can get even better help.:-)

    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
  • I know you posted a solution that works for you, but if you could humor me. . .

    Could you try the following code? I am interested to know if it works. If it does, there should not be an issue with speed. It should insert 75,000 record in under a couple of seconds. Since DLL was not posted, the code is assuming that an insert into Accounts has an Identity field on AccountID that gets incremented.

    INSERT Accounts (AccountNumber)

    (SELECT DISTINCT OldAccountNum

    FROM OldCustomers)

    )

    INSERT NewCustomers (CustomerID, AccountID, FullName)

    (SELECT O.OldCustomerID, A.AccountID, O.FullName

    FROM OldCustomers O

    JOIN Accounts A on A.AccountNumber = O.OldAccountNum

    )

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Luis Cazares (9/18/2013)


    Am I missing something or would something like this will work correctly and much faster than the cursor?

    LinksUp (9/18/2013)


    . . If it does, there should not be an issue with speed. It should insert 75,000 record in under a couple of seconds.

    Wow, talk about being on the same page and just a minute apart!

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • I'll be glad to try these samples tomorrow. I'll post results. Always willing to learn and improve....

  • Thanks guys. Both solutions were almost identical and I was able to get them both to work. It seems I was over complicating the process. Thanks again!

  • I'm glad that we could help.

    After a while we all start looking for the simplest way to do it. 🙂

    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 12 posts - 1 through 11 (of 11 total)

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