
Hi,
I have a sample query you can use it for inserting data from one table into multiple tables.
CREATE PROCEDURE GenerateOrder
AS
BEGIN
DECLARE @BuyerEmail VARCHAR(50),
@BuyerName VARCHAR(50),
@ShippingAddressID INT,
@BillingAddressID INT,
@CustomerID INT,
@RecipientName VARCHAR(50),
@City VARCHAR(50),
@Country VARCHAR(50),
@State VARCHAR(50),
@ShipAdd1 VARCHAR(100),
@ShipAdd2 VARCHAR(100),
@Now DATETIME,
@AmazonOrderID INT,
@OrderID VARCHAR(MAX),
@Currency VARCHAR(10),
@CurrencyID INT,
@ProductName VARCHAR(200),
@ProductID INT,
@Count INT,
@ProductVariantID INT,
@ItemPrice NUMERIC(16,5),
@AffiliateID INT
SET @Now = GETDATE()
UPDATE AmazonOrders
SET [ship-state] = REPLACE([ship-state],'.','')
DECLARE Cr_AmzOrd CURSOR
FOR
SELECT ID,currency,[product-name],[item-price]
FROM [AmazonOrders]
OPEN Cr_AmzOrd
FETCH NEXT FROM Cr_AmzOrd
INTO @AmazonOrderID,@Currency,@ProductName,@ItemPrice
WHILE @@FETCH_STATUS = 0
BEGIN
--Billing Address
INSERT INTO [Address]
SELECT [buyer-name] FirstName,
NULL LastName,
[buyer-email] Email,
NULL Company,
NULL CountryId,
NULL StateProvinceId,
NULL City,
NULL Address1,
NULL Address2,
NULL ZipPostalCode,
[buyer-phone-number] PhoneNumber,
NULL FaxNumber,
@Now CreatedOnUtc
FROM AmazonOrders (NOLOCK)
WHERE ID = @AmazonOrderID
SELECT @BillingAddressID = @@IDENTITY
SELECT @CurrencyID = ID
FROM Currency (NOLOCK)
WHERE CurrencyCode = @Currency
INSERT INTO Affiliate VALUES(@BillingAddressID,0,0)
--Customer
INSERT INTO [Customer](PasswordFormatId,
CustomerGuid,
LanguageId,
CurrencyID,
BillingAddress_Id,
TaxDisplayTypeId,
IsTaxExempt,
VatNumberStatusId,
UseRewardPointsDuringCheckout,
Active,
Deleted,
IsSystemAccount,
CreatedOnUtc,
LastActivityDateUtc
)
VALUES (0,NEWID(),1,@CurrencyID,@BillingAddressID,0,0,0,0,0,0,0,GETDATE(),GETDATE())
SELECT @CustomerID = @@IDENTITY
INSERT INTO [CustomerAddresses] (Customer_Id,Address_Id) VALUES (@CustomerID,@BillingAddressID)
-- Recipient Address
INSERT INTO [Address]
SELECT AO.[recipient-name] FirstName,
NULL LastName,
NULL Email,
NULL Company,
C.ID CountryId,
SP.ID StateProvinceId,
AO.[ship-city] City,
AO.[ship-address-1] Address1,
AO.[ship-address-2] Address2,
AO.[ship-postal-code] ZipPostalCode,
AO.[ship-phone-number] PhoneNumber,
NULL FaxNumber,
@Now CreatedOnUtc
FROM AmazonOrders AO(NOLOCK)
INNER JOIN Country C (NOLOCK)
ON C.TwoLetterIsoCode = AO.[ship-country]
INNER JOIN StateProvince SP(NOLOCK)
ON SP.CountryId = C.Id
AND UPPER(AO.[ship-state]) = CASE
WHEN LEN(AO.[ship-state]) = 2 THEN UPPER(SP.Abbreviation)
ELSE UPPER(SP.Name)
END
WHERE AO.ID = @AmazonOrderID
SELECT @ShippingAddressID = @@IDENTITY
INSERT INTO Affiliate VALUES(@ShippingAddressID,0,0)
SELECT @AffiliateID = @@IDENTITY
UPDATE [Customer]
SET [ShippingAddress_Id] = @ShippingAddressID
WHERE Id = @CustomerID
INSERT INTO [CustomerAddresses] (Customer_Id,Address_Id) VALUES (@CustomerID,@ShippingAddressID)
--ShoppingCartItem
SELECT @Count = COUNT(*)
FROM Product (NOLOCK)
WHERE [Name] = @ProductName
IF @Count=0
BEGIN
INSERT INTO Product(Name,
ProductTemplateId,
ShowOnHomePage,
AllowCustomerReviews,
ApprovedRatingSum,
NotApprovedRatingSum,
ApprovedTotalReviews,
NotApprovedTotalReviews,
SubjectToAcl,
Published,
Deleted,
CreatedOnUtc,
UpdatedOnUtc)
VALUES(@ProductName,0,0,0,0,0,0,0,0,0,0,@Now,@Now)
SELECT @ProductID = @@IDENTITY
INSERT INTO [ProductVariant](ProductId,
IsGiftCard,
GiftCardTypeId,
RequireOtherProducts,
AutomaticallyAddRequiredProductVariants,
IsDownload,
DownloadId,
UnlimitedDownloads,
MaxNumberOfDownloads,
DownloadActivationTypeId,
HasSampleDownload,
SampleDownloadId,
HasUserAgreement,
IsRecurring,
RecurringCycleLength,
RecurringCyclePeriodId,
RecurringTotalCycles,
IsShipEnabled,
IsFreeShipping,
AdditionalShippingCharge,
IsTaxExempt,
TaxCategoryId,
ManageInventoryMethodId,
StockQuantity,
DisplayStockAvailability,
DisplayStockQuantity,
MinStockQuantity,
LowStockActivityId,
NotifyAdminForQuantityBelow,
BackorderModeId,
AllowBackInStockSubscriptions,
OrderMinimumQuantity,
OrderMaximumQuantity,
DisableBuyButton,
DisableWishlistButton,
AvailableForPreOrder,
CallForPrice,
Price,
OldPrice,
ProductCost,
CustomerEntersPrice,
MinimumCustomerEnteredPrice,
MaximumCustomerEnteredPrice,
HasTierPrices,
HasDiscountsApplied,
Weight,
Length,
Width,
Height,
PictureId,
Published,
Deleted,
DisplayOrder,
CreatedOnUtc,
UpdatedOnUtc,
Affiliated)
VALUES(@ProductID,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,@ItemPrice,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,@Now,@Now,0)
SELECT @ProductVariantID = @@IDENTITY
END
ELSE
Kavya Shree Mandapalli
Varun66, if this helps please login to Mark As Answer. | Alert Moderator