![]() Let’s group them together in a transaction. If any one of them fails, then all statements should fail. Insert data as using a transactionĪll of these statements need to run as a single atomic transaction. In a real scenario, you would need to ascertain the IDs of the records that were inserted and specify them explicitly in case new customer and address records had been inserted since you ran your original code. Note: This code only works because you are the only user working in the database. WHERE AddressID = IDENT_CURRENT('SalesLT.Address') WHERE CustomerID = IDENT_CURRENT('SalesLT.Customer') Enter the following T-SQL code into the new query window and run it to delete the inconsistent data:.To fix this, you’ll need to delete the two rows that were inserted. The database is now inconsistent as there’s no link between the new customer and their address. However, the insert for the CustomerAddress table failed with a duplicate key error. SELECT * FROM SalesLT.Customer ORDER BY ModifiedDate DESC Ī row for Norman Newcustomer was inserted into the Customer table (and anotherw as in serted into the Address table). Enter the following T-SQL code into the new query window:.Two of the statements appear to have succeeded, but the third failed. Note the output messages, which should look like this:Ĭonversion failed when converting date and/or time from character string.Select ⏵Run at the top of the query window, or press the F5 key to run the code.INSERT INTO SalesLT.CustomerAddress (CustomerID, AddressID, AddressType, rowguid, ModifiedDate) VALUES ('6388 Lake City Way', 'Burnaby','British Columbia','Canada','V5A 3A6',NEWID(), GETDATE()) INSERT INTO SalesLT.Address (AddressLine1, City, StateProvince, CountryRegion, PostalCode, rowguid, ModifiedDate) INSERT INTO SalesLT.Customer (NameStyle, FirstName, LastName, EmailAddress, PasswordHash, PasswordSalt, rowguid, ModifiedDate) Enter the following T-SQL code into the query window:.A new query window is displayed with a connection to the AdventureWorks database. ![]() Right click the AdventureWorks server and select New Query.A green dot will appear when the connection is successful. In the Connections pane, double-click the AdventureWorks server.In this exercise you’ll use a transaction to ensure that when a row is inserted into the Customer and Address tables, a row is also added to the CustomerAddress table. A customer without an address will cause problems for the shipping when orders are made. As part of the customer registration, data about a customer and their address need to stored. Insert data without transactionsĬonsider a website that needs to store customer information. Note: If you’re familiar with the standard AdventureWorks sample database, you may notice that in this lab we are using a simplified version that makes it easier to focus on learning Transact-SQL syntax. For your reference, the following diagram shows the tables in the database (you may need to resize the pane to see them clearly). In this lab, you’ll use T-SQL statements to see the impact of using transactions in the AdventureWorks database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |