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