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.
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);
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);