Bulk Insert and Bulk Update in single SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Bulk Insert and Bulk Update in single SP

Author

Topic

akki1563
Starting Member

10 Posts

Posted - 2012-02-28 : 08:59:03

Hi,

I am new to sql server 2008. I need a store procedure which does bulk insert new records and update the old records in the database.

I achieved bulk insert using xmldoc. I have converted the List of object into xmldoc and passing xmldoc as parameter to the store procedure.

But, I need to insert only new records and update old records.

How to achieve it.
Is my approach right or should I need to follow any other approach.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-28 : 11:04:29

MERGE is your friend

MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerID, ProductID, LastPurchaseDate)
VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

akki1563
Starting Member

10 Posts

Posted - 2012-03-01 : 13:55:41

Hi yosiasz,

I updated my query as shown below and When i execute the query I get below error. plz, can u correct me where i am doing wrong.

--- Error

Procedure [usp_Items_InsertUpdate]
Incorrect syntax near 'dbo'.
Procedure [usp_Items_InsertUpdate]
Incorrect syntax near the keyword 'AS'.
Procedure [usp_Items_InsertUpdate]
The name "Source.Item_Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

--- Error

-- SP ----

ALTER PROCEDURE [dbo].[usp_Items_InsertUpdate]
(
@ItemListXmlData xml
)
AS
BEGIN
SET NOCOUNT ON;

-- Add Handle to XML Data
DECLARE @xmlDataHandle Int
Exec sp_xml_preparedocument @xmlDataHandle Output, @ItemListXmlData

CREATE TABLE #TEMP
(
Item_Name varchar(25)
, Item_Quantity int
, Item_Rate decimal(9)
)

INSERT INTO #TEMP(Item_Name, Item_Quantity, Item_Rate)
(SELECT Item_Name, Item_Quantity, Item_Rate
FROM OpenXml(@xmlDataHandle, '/ArrayOfItems/Items', 2)
With(
Claim_ID int
, Manufacturer_ID int
, Product_ID int
, MarketEvent_ID int
) )

-- Remove Handle to Free-Up Memory
Exec sp_xml_removedocument @xmlDataHandle

MERGE dbo.Item AS Target
USING (SELECT * FROM #TEMP) AS Source
ON (Target.Item_Id = Source.Item_Id )

WHEN MATCHED THEN
UPDATE SET
Target.Item_Name = Source.Item_Name
, Target.Item_Quantity = Source.Item_Quantity
, Target.Item_Rate = Source.Item_Rate

WHEN NOT MATCHED BY TARGET THEN

INSERT INTO Item(Item_Name, Item_Quantity, Item_Rate)
VALUES (Source.Item_Name, Source.Item_Quantity, Source.Item_Rate)

OUTPUT $action, Inserted.*, Deleted.*;

SET NOCOUNT OFF

Return @retval

END

akki1563
Starting Member

10 Posts

Posted - 2012-03-01 : 14:46:01

For your information I am using sql server 2005

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-03-01 : 15:25:25

try this


ALTER PROCEDURE [dbo].[usp_Items_InsertUpdate]
(
@ItemListXmlData xml
)
AS
BEGIN
SET NOCOUNT ON;

-- Add Handle to XML Data
DECLARE @xmlDataHandle Int
Exec sp_xml_preparedocument @xmlDataHandle Output, @ItemListXmlData

CREATE TABLE #TEMP
(
Item_Name varchar(25)
, Item_Quantity int
, Item_Rate decimal(9)
)

INSERT INTO #TEMP(Item_Name, Item_Quantity, Item_Rate)
SELECT Item_Name, Item_Quantity, Item_Rate
FROM OpenXml(@xmlDataHandle, '/ArrayOfItems/Items', 2)
With(
Claim_ID int
, Manufacturer_ID int
, Product_ID int
, MarketEvent_ID int
) 

-- Remove Handle to Free-Up Memory
Exec sp_xml_removedocument @xmlDataHandle

UPDATE a 
SET
a.Item_Name =b.Item_Name
,a.Item_Quantity = b.Item_Quantity
, a.Item_Rate = b.Item_Rate
from
	dbo.item a
inner join
	#Temp b
on		a.Item_Id = b.Item_Id 


Insert into item(Item_Name, Item_Quantity, Item_Rate)
SELECT a.Item_Name, a.Item_Quantity, a.Item_Rate
FROM 
#TEMP a
left join dbo.Item b
on a.Item_Id = b.Item_Id 
where b.item_id is null


END

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

akki1563
Starting Member

10 Posts

Posted - 2012-03-01 : 23:25:22

Thank you Vinnie881 for the solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources