Rollback the inserts

• gary1

  SSC-Insane

  Points: 21152

  More actions

  April 10, 2012 at 2:20 pm

  #256234

  Hi Using the below script to insert database into a table.

  But the data inserted is wrong and I want to rollback.

  But it looks like, it's already been committed.

  I want to set the default option to NOT to COMMIT automatically. If the count is correct, then I want issue the COMMIT command manually and if the count is wrong, I want to issue rollback.

  Please advise.

  Use Mydb

  go

  SET IDENTITY_INSERT [USR_GRP] ON

  INSERT [USR_GRP] ([ID_USG], [CODE_USG], [NAME_USG], [DESCRIPTION_USG], [STATUS_USG], [CREATED_BY_USG], [MODIFIED_BY_USG], [CREATED_DATE_USG], [MODIFED_DATE_USG], [EFFECTIVE_DATE_USG]) VALUES (1, N'GRP1', N'GRP1', N'GRP', 3, 1, 2, CAST(0x00009CDC00F7865 AS DateTime), CAST(0x0000V01D00D12733 AS DateTime), CAST(0x00009FCB00000000 AS DateTime))

  SET IDENTITY_INSERT [USR_GRP_USG] OFF

  Thanks

• Lowell

  SSC Guru

  Points: 323518

  More actions

  April 10, 2012 at 2:24 pm

  #1471509

  i use SSMS tools to always use a template that looks like this:

  SET XACT_ABORT ON

  BEGIN TRAN

  --do stuff like the insert

  --check your row counts

  --highlight and execute one of the two commands below, based on whether you like the rowcounts.

  --ROLLBACK TRAN

  --COMMIT TRAN

  you must explicitly declare a transaction in order to have the option to rollback; so in the future you just need to make it a habit to code it like this;

  one more reason to use that free tool.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jared

  SSC Guru

  Points: 61793

  More actions

  April 10, 2012 at 2:24 pm

  #1471510

  What count do you mean? If your data was not in a BEGIN TRAN...COMMIT or ROLLBACK block, it is probably committed. Still not sure what you are trying to do.

  Jared
  CE - Microsoft

• CapnHector

  SSCoach

  Points: 16143

  More actions

  April 10, 2012 at 2:28 pm

  #1471512

  gmamata7 (4/10/2012)

  ---

  Hi Using the below script to insert database into a table.

  But the data inserted is wrong and I want to rollback.

  But it looks like, it's already been committed.

  I want to set the default option to NOT to COMMIT automatically. If the count is correct, then I want issue the COMMIT command manually and if the count is wrong, I want to issue rollback.

  Please advise.

  Use Mydb

  go

  SET IDENTITY_INSERT [USR_GRP] ON

  INSERT [USR_GRP] ([ID_USG], [CODE_USG], [NAME_USG], [DESCRIPTION_USG], [STATUS_USG], [CREATED_BY_USG], [MODIFIED_BY_USG], [CREATED_DATE_USG], [MODIFED_DATE_USG], [EFFECTIVE_DATE_USG]) VALUES (1, N'GRP1', N'GRP1', N'GRP', 3, 1, 2, CAST(0x00009CDC00F7865 AS DateTime), CAST(0x0000V01D00D12733 AS DateTime), CAST(0x00009FCB00000000 AS DateTime))

  SET IDENTITY_INSERT [USR_GRP_USG] OFF

  Thanks

  you can use error catching. something similar to this:

  Use Mydb

  go

  BEGIN TRANSACTION

  SET IDENTITY_INSERT [USR_GRP] ON

  INSERT [USR_GRP] ([ID_USG], [CODE_USG], [NAME_USG], [DESCRIPTION_USG], [STATUS_USG], [CREATED_BY_USG], [MODIFIED_BY_USG], [CREATED_DATE_USG], [MODIFED_DATE_USG], [EFFECTIVE_DATE_USG]) VALUES (1, N'GRP1', N'GRP1', N'GRP', 3, 1, 2, CAST(0x00009CDC00F7865 AS DateTime), CAST(0x0000V01D00D12733 AS DateTime), CAST(0x00009FCB00000000 AS DateTime))

  SET IDENTITY_INSERT [USR_GRP_USG] OFF

  IF @@Error <> 0 --make sure there are no errors you may want to omit this step if you dont want to check errors of any other kind than the count being off

  ROLLBACK TRANSACTION

  ELSE -- no errors

  BEGIN

  IF @@Rowcount <> @MyValue --check your row counts or the count you want to check

  ROLLBACK TRANSACTION

  ELSE Commit Transaction

  END

  but any previous transactions are all ready commited if as SQLKnowItAll said they were not in the begin commit rollback statements

  ---

  For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

  For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

  Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
  Jeff Moden's Cross tab and Pivots Part 1[/url]
  Jeff Moden's Cross tab and Pivots Part 2[/url]