A stored procedure to insert data
You are creating a stored procedure which will insert data into the table shown in the Database schema exhibit.
You need to insert a new customer record into the tables as a single unit of work. Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange the, in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
Select and Place:
Answer Given:
Input from Zoran Barac:
First populate tables with PK then rest with FK constraints. You need BEGIN TRAN and COMMIT TRAN since they want all in one transaction, and insert into PERSON.BusinessEntity needs to go first since you need to have PK in the BusinessEntity since you have FK constraint on two tables. And since there are six SQL segments and you can use just 5 we can assume that you can use already existing AddressTypeID from AddressType(Person) for customer you are inserting. so it should be BEGIN TRAN INSERT INTO PERSON.BusinessEntity INSERT INTO PERSON.BusinessEntityContact INSERT INTO PERSON.BusinessEntityAddress COMMIT TRAN
Input from Gerard Heijnen:
First you need to INSERT into AddressType and BusinessEntity, as this will generate or fill the AddressTypeId and BusinessEntityId. After that you can use these Id's to refer to when you INSERT into BusinessEntityAddress and BusinessEntityContact.
I made 3 assumptions:
1. You are able to connect - the BusinessEntityAddress rows to the appropriate AddressType rows - the BusinessEntityAddress rows to the appropriate BusinessEntity rows - the BusinessEntityContact rows to the appropriate BusinessEntity rows;
2. There are 1:n relationships defined in the database for - AddressType to BusinessEntityAddress - BusinessEntity to BusinessEntityAddress - BusinessEntity to BusinessEntityContact;
3. The space in "Person. BusinessEntity" is a typo.
If you have only room for 5 SQL segments I would skip AddressType (though not 100% suređ). It's similar to table ContactType (which column ContactTypeID in BusinessEntityContact suggests there is). Table ContactType is also not used (it's not even given in the assignment). Apparently ContactTypeId and AddressTypeId are already existing.