SQL INSERT INTO multiple tables, one SProc

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

https://suneethasdiary.wordpress.com/tag/disabling-auto-commit-mode-in-sql-server-management-studio/


https://msdn.microsoft.com/pt-br/library/ms175976.aspx?f=255&MSPPError=-2147217396

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

https://msdn.microsoft.com/en-us/library/ms188929.aspx

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.

Walter,

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
Sam

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?

Walter,

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

BEGIN

BEGIN TRANSACTION;

BEGIN TRY
    -- Do all my INSERTS...
END TRY
BEGIN CATCH
    SELECT my error messages...

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

END

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
Sam

Yes, that is true.

Walter,

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

a) with  SET_IMPLICIT_TRANSACTIONS checked, I -MUST- explicitly COMMIT TRANSACTIONS in ALL SProcs.

and

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!
Sam

Walter,

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
Sam

I got this code from Microsoft MSDN https://msdn.microsoft.com/en-us/library/ms175976.aspx, about half way down the page.
TeamAdd-2015-0403-1834.sql

Sam,
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.

Walter,

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.
Sam

try
            {
                Conn.Open();
                SqlCmd.ExecuteNonQuery();
                Conn.Close();
                Conn.Dispose();
                SqlCmd.Dispose();
                return Out.Value;
            }
            catch (SqlException sqlex) // newly added in an attempt to see sql errors
            {
                Conn.Close();
                Conn.Dispose();
                SqlCmd.Dispose();
                throw sqlex;
            }
            catch (Exception err)
            {
                Conn.Close();
                Conn.Dispose();
                SqlCmd.Dispose();
                Exception Ex = new Exception("Tony Error = SQL Server Error(no requesting page, objType");
                //Error_Logging.LogError(err, "Procedures", true);
                throw err;
                //return null;
            }
TeamAdd-2015-0404-0925.sql

SamCash:

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
or
use NOT EXISTS with your inserts
https://msdn.microsoft.com/en-us/library/ms188336(v=sql.105).aspx

Thanks much.  I have been on another assignment, just got back to this, thanks again.