Need help transferring data from one table to another

I'm having difficulty figuring out how to do something. I have a table called "Temp". It's used as a scratch area for users to make all their mistakes. Then, once they hit a finished button, everything pertaining to that user in the Temp table gets written to a permanent "Orders" table, and those user's records get wiped off the Temp table.

I do not have the Temp table broken out into complete 3rd normal form, since it's for temporary scratch stuff anyway until the user actually thinks they are done with edits, deletes, etc. But the permanant "Orders" table is properly broken out into third normal form, where PhoneNumber, Address and DispatcherID do not go on the Orders table, they go on the OrderInfo table which links to an OrderInfo field in the Orders table.  In the code below, InsertA seems to be fine. Also the next insert called cmd seems to be fine as well. However, what I need to do before I can do cmd properly is to get the ID of the record added by insertA so that it can be used for the OrderInfo field insertion on the Orders table. And if the record already exists and therefore insertA inserted nothing, how do I grab the right ID for OrderInfo field of the Orders table insertion (cmd)? I think I'm close, but just missing a few things. Thanks.

try
            {
                SqlCommand insertA = new SqlCommand("INSERT INTO OrderInfo (PhoneNumber, Address, DispatcherID) SELECT PhoneNumber, Address FROM Temp WHERE Billable = 1 AND NOT EXISTS (SELECT 1 FROM OrderInfo WHERE Addresses.PhoneNumber = Temp.PhoneNumber AND Addresses.Address =Temp.Address)", myConnection);

                insertA.ExecuteNonQuery();
                
                SqlCommand cmd = new SqlCommand("INSERT INTO Orders SELECT PhoneNumber, Address, DollarAmt, Created, CreatedBy, Next, Delivered, Problem, Deleted, Modified, Edits, ModifiedBy, Billable, DispatcherID FROM Temp Where Billable = 1", myConnection);
                SqlCommand cmdD = new SqlCommand("DELETE FROM Temp", myConnection);
                cmd.ExecuteNonQuery();
                cmdD.ExecuteNonQuery();
                myConnection.Close();

            }

Open in new window

LVL 9
BobHavertyComhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
First of all: You're working without schema names. Is Temp placed in the users schema or is it shared between all users? This can be a concurrency issue.

Well, why using a temp table at all? I would use the normal structure and mark the work in progress to distinguish between the playground data and the real one.

And last but not least: You can use the OUTPUT clause to retrieve new values. But this should be completly handled in one stored procedure and not in batches from your client application.
BobHavertyComhAuthor Commented:
Hi ste5an, thanks for your reply.

First of all: You're working without schema names. Is Temp placed in the users schema or is it shared between all users? This can be a concurrency issue.

Temp is shared between all users from all Companies, and they are filtered out by Company and then User to distinguish which records will be copied over to Orders when a User is finished adding and/or editing their orders.

Real life Users add Orders which become part of the Temp scratch table that all users from all companies share. When they are finished adding, editing, deleting etc...and they press a finished button, all records that they added after their edited state, if they edited, at all are added to the permanent Orders table.

So for example, a number of users from a number of different companies might add three orders, and they might edit or delete some, re add some, etc....But once they press the finished button, none of this will be editable by them, and their Temp records will disappear from the Temp table and disappear from their display

Well, why using a temp table at all? I would use the normal structure and mark the work in progress to distinguish between the playground data and the real one.

LOL. Great question. The answer is, I was told to do it this way. I think that there is a feeling that the Temp table will be smaller than the permanent Orders table, which will definitely be true. Therefore, by giving everybody a scratch area while they perform what they need to do for the order, and mark order states, edit orders, delete, re-add etc...., it prevents queries into a much larger Orders table, and it is only added to that table when the Order task has been completed or closed. I suppose the fear is that the larger the table, the longer the query, so this is supposed to cut down on that. Does this sound viable to you, or a waste?

And last but not least: You can use the OUTPUT clause to retrieve new values. But this should be completly handled in one stored procedure and not in batches from your client application.

At some point, I'm going to have a real database expert come in and make corrections like you are speaking of, but for right now, I just need to get a working demo up. So I'm not familiar with OUTPUT, I'll have to see if I can look that up. Thanks.
BobHavertyComhAuthor Commented:
OK, I read about OUTPUT, and I can see where it will help me insert the ID PK value from the record I inserted, into the FK value that I need for the other table that is linked to the FK table. But what about if the record already existed on the PK table, and therefore no insertion was needed?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ste5anSenior DeveloperCommented:
Well, you can say:

CREATE TABLE #Map
    (
      NewOrderID INT NOT NULL ,
      OrderNumber INT NOT NULL
    );

INSERT  INTO Orders
        ( OrderNumber )
OUTPUT  INSERTED.OrderID, INSERTED.OrderNumber
        INTO #Map
        SELECT  T.OrderNumber
        FROM    Temp T;

SELECT  *
FROM    #Map;

Open in new window

ste5anSenior DeveloperCommented:
You need to add those values to the map from above.
CREATE TABLE #Map
    (
      NewOrderID INT NOT NULL ,
      OrderNumber INT NOT NULL
    );

INSERT  INTO Orders
        ( OrderNumber )
OUTPUT  INSERTED.OrderID, INSERTED.OrderNumber
        INTO #Map
        SELECT  T.OrderNumber
        FROM    Temp T;

SELECT  *
FROM    #Map;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BobHavertyComhAuthor Commented:
What happens if this:

SqlCommand insertA = new SqlCommand("INSERT INTO OrderInfo (PhoneNumber, Address, DispatcherID) SELECT PhoneNumber, Address FROM Temp WHERE Billable = 1 AND NOT EXISTS (SELECT 1 FROM OrderInfo WHERE Addresses.PhoneNumber = Temp.PhoneNumber AND Addresses.Address =Temp.Address)", myConnection);

does not insert anything because that info already exists on the table I wish to insert it on?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.