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