The sql below allows to insert into multiple tables. What this query suppose to do is to create a single record in Vendor table and multiple records on PV and PLVendor depending on user seleccion. Please if some one has any ideas will be appreciate it.
TABLES:
1.-Vendor (This table just accept one record with unique VendorID)
2.-PV (This table could be inserted with more than 1 record the relationship is ProductID to VendorID in other words one vendorID could have many products )
3.-PLVendor (This table could be inserted with more than 1 record the relationship is PlacesID to VendorID in other words one vendorID could have many places )
STORE PROCEDURE
BEGIN
TRANSACTION-- All types first insert into People table
INSERT
INTO Vendor
(
VendorName,
VendorAddress,
VendorCity,
VendorState,
VendorZipCode,
VendorPhone,
VendorEmail,
VendorWebSite,
ServiceType1,
ServiceType2,
IsActive
)
VALUES
(
LTRIM(@VendorName),
@VendorAddress,
@VendorCity,
@VendorState,
@VendorZipCode,
@VendorPhone,
@VendorEmail,
@VendorWebSite,
@ServiceType1,
@ServiceType2,
ISNULL(@IsActive, 1)
)
--print 'Inserted new record into PLVendor'
SET @VendorID = SCOPE_IDENTITY()
BEGIN
BEGIN
INSERT
INTO PLVendor
(
PlacesID,
VendorID
)
VALUES
(
@PlaceID,
@VendorID
)
END
--INSERT print 'Inserted new record into PV'
BEGIN
INSERT
INTO PV
(
ProductID,
VendorID
)
VALUES
(
@ProductID,
@VendorID
)
END
END
COMMIT TRANSACTION