SQL INSERT INTO multiple tables, one SProc

I am brand new to sql.  I have been INSERTing INTO multiple TABLEs with one SProc with a series of: SELECT INTO (a table), SELECT INTO the (next table) and so on... (the database is for a web site)

I see that if there is a failure along the way only the tables processed will be processed and the balance skipped.  I am confident databases do not work this way.  I do not think locking will work as a failure could leave everything locked up, I have read about Foreign Keys (one partial sentence "sql will make sure everything is ok before committing' ) but could not find any more about my requirement.  Usually because I am not sure what I am looking for and goggling the wrong terms.  I am running out of time.  So I am seeking answers here.

(note: I have not set up specific relationships with SSMS, I just learned about them during this research.  I do have relationships that I use effectively throughout this project with all my JOINs,  Do I need to set up these specific relationships in SSMS? If so, how do they help?)

The attached file is actual: first it checks for uniqueness with 5 fields, then, then inserts into the main table, returning the new Identity int, with the new Identity int inserts into the remaining tables.  This works when everything works, otherwise, duh...

Thank You for your coaching
Who is Participating?

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

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.

Walter RitzelSenior Software EngineerCommented:
So, answering your questions:
1) If the joins you made are among the primary and foreign keys of the tables, would be nice to have that specified, so the database can fail whenever a constraint problem raises;
2) If whenever one of the insert fails, the previous inserts are saved and just the failed one and next are not processed, this means that you have auto commit on. You should set auto commit off and use the commit command explicitly
3) If you want your procedure to behave more gracefully on errors, you can try to work with exceptions


Mark ElySenior Coldfusion DeveloperCommented:
You are confusing a few ideas.  

Creating foreign key constraints will indeed prevent unrelated data from being created; however having foreign key constraints does not mean everything is ok.  It simply means that is the data entered into the table has a matching reference  somewhere else.  

Your above scenario sounds like a candidate for using TRANSACTIONS


Whereas you would be able to rollback or commit your data based upon the success or failure of your expectations.  

SQL 2008 introduced MERGES and INSERTS which is remarkably powerful.  However there are a few kinks.  Instead of a cursory insert or update it attempts to insert or update multiple records at one time.  This has the advantage of being extremely fast but disadvantage of producing duplication's or phantom records.  This can be avoided with detailed programming.  

If you want to know more about SSMS you should read the MICROSOFT BOOK 70-463 which focuses on this subject.
SamCashAuthor Commented:

Thanks again you assisted me previously.

I checked and you are correct the SET_IMPLICIT_TRANSACTIONS is NOT checked.  

To be sure I interpret your answer correctly.  If I "check" SET_IMPLICIT_TRANSACTIONS, sql will handle my whole sproc as a transaction (instead of doing each part discreetly), making sure the whole thing will execute before committing the transaction.  

So with this set I do not have to worry about only part of an sproc executing?

That will be awesome!

Thanks Again

I did a little Goggling, it looks like I do not have to alter any of my sprocs with this set, sql will handle all my sprocs as one transaction, ie I do not have to use BEGIN TRANSACTION and COMMIT TRANSACTION at the beginning and end of my sproc?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

SamCashAuthor Commented:

Correction, I think...  After I check SET_IMPLICIT_TRANSACTIONS I will have to:



    -- Do all my INSERTS...
    SELECT my error messages...

    IF @@TRANCOUNT > 0



So if I do not check SET_IMPLICIT_TRANSACTIONS can I just do this explicitly to my critcle sprocs that INSERT, UPDATE or DELETE and leave the many SELECT sprocs alone for now?  

Thanks Much
Walter RitzelSenior Software EngineerCommented:
Yes, that is true.
SamCashAuthor Commented:

"Yes, that is true."  Both items are true or which one is true?



b) with  SET_IMPLICIT_TRANSACTIONS -NOT- checked, I -MAY- explicitly invoke TRANSACTIONS where needed by adding BEGIN TRANSACTION... to the INSERT, UPDATE and DELETE SProcs.

Thanks again, Have a Happy Easter!
SamCashAuthor Commented:

I have implemented Transactions on one Sproc.  It inserts to two tables and works.  I renamed the second table to break things (testing).  It fails and leaves my first table locked(I think) as when I attempt to "Edit top 200 rows" to verify the first table did not get written to SSMS times out for a few minutes.  When it does come back it is unaltered, good. (note: I did not check SET IMPLICIT_TRANSACTIONS, I found a doc that said I can do it Explicitly as desired???  I am not comfortable with the source though)

Thanks Much

I got this code from Microsoft MSDN https://msdn.microsoft.com/en-us/library/ms175976.aspx, about half way down the page.
Walter RitzelSenior Software EngineerCommented:
The best practice suggests that you use explicit transactions, using commit and rollback whenever needed. So, I can only imagine the "lock" you mention as a result of not using the rollback.
Explicit transactions means that you need to start  and finish transactions writing in the code.
Your code seems correct, my only suggestion, taking best practices into consideration, would be start the transactions as late as possible. SELECTs doesnt need transactions.
SamCashAuthor Commented:

Thanks so much, I did not expect an answer over the Easter Weekend.  I am working todayas I have a deadline to make Monday morning, and plan to spend Sunday with family.

To further my understanding of the issues I am working with I deleted all the Transaction related code, ran it with the table removed...
1) No entry was made in the first table (even though it was valid and first in the procedure)
2) No entry was made in the second (because it was not there, as expected)
3) sql error "Invalid object name 'dbo.Managers'." was thrown.  I expected the error.  I also expected the first table to get the insert which did not happen.  Some how sql either checked first, found the error and did nothing, or rolled back implicitly??.

I am changing the code so BEGIN TRANACTION occurs as you suggested.

Good, no more lock ups, thanks.  I now do not get any errors at my c# code.  I am commenting out the TRANSACTION control (so I get errors again) and moving ahead on other areas, at least I am not getting bad data, and errors when things do not work.  I want to keep pursuing this to proper best practices, when I ignored things like this in the past, because they worked, they hurt me later.  Below is my c#.  Attached is the sql.

Again Thanks and Happy Easter.

                return Out.Value;
            catch (SqlException sqlex) // newly added in an attempt to see sql errors
                throw sqlex;
            catch (Exception err)
                Exception Ex = new Exception("Tony Error = SQL Server Error(no requesting page, objType");
                //Error_Logging.LogError(err, "Procedures", true);
                throw err;
                //return null;
Walter RitzelSenior Software EngineerCommented:

Your C# code seems ok.
About your SQL code, I would say it is fine as well, with one change: the commit command, I think it is better to put it  inside the begin try.

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
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:

can you please clarify what is the problem and post errors?

do you have problems when insert duplicated records,  ?
 if 'yes' :
you can validate if such record is already in the table using one of the above posted methods
use NOT EXISTS with your inserts
SamCashAuthor Commented:
Thanks much.  I have been on another assignment, just got back to this, thanks again.
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.