insert data from single table to multiple tables [Resolved]

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

Hi,

Can you please mark it as answer....

Regards,
Shree M.

Kavya Shree Mandapalli

Varun66, if this helps please login to Mark As Answer. | Alert Moderator

Can you explain in detail with sample data...
"one table into multiple tables" --> on which criteria you wish to move one table data to multiple tables? May be based on referential constraints or what?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Varun66, if this helps please login to Mark As Answer. | Alert Moderator

-- Sample Stored Procedure for your need
[code]CREATE PROCEDURE InsertFromTwoTables
AS
BEGIN

-- Insert into Table-1 using Table3
INSERT INTO Tab1(col1, col2)
SELECT Pcol1, Pcol2 FROM Tab3;

-- Insert into Table-2 using Table3
INSERT INTO Tab2(col3, col4, col5)
SELECT Pcol3, Pcol4, Pcol5 FROM Tab3;
END[/code]
GO
EXEC InsertFromTwoTables;
Note: You can do like the above code. There you can include any conditions also..
Explain what is your actual requirement. we will provide you exact solution....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Varun66, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Thank you so much...
This is the query that i am searching for.


Thanks,
Varun.

Varun66, if this helps please login to Mark As Answer. | Alert Moderator

I think we can do this type of tasks without using CURSOR in sql server...
@Varun, post us back if you have any issues.

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Varun66, if this helps please login to Mark As Answer. | Alert Moderator

Hi

I think the below link will help you. you can use SELECT and Insert INTO in same query to insert multiple tables.

http://asp-net-corner.blogspot.in/2013/08/how-to-use-insert-query-using-select.html

Varun66, if this helps please login to Mark As Answer. | Alert Moderator

This can be easily achieved by using dynamic SQL. I would not suggest CURSOR for this. Anyway great and first answer from @Kmandapalli..

S Devi Prasad

Varun66, if this helps please login to Mark As Answer. | Alert Moderator