How do i insert and update the data if already exisits in database ?

Hi Experts,

We have c# datatable and need to import to the SQL Table . And am aware of SQLBulkCopy Method.but it doesnot update the data if already presents . can i have any alternative which can update if present as well as and inserts if not presents

Thanks in Advance
Hari ShankarWeb DeveloperAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Merge is an another very good option (Thanks Tomas Helgi). @Author - If you want to you want to use MERGE and do not want to use IF EXISTS method then here is a live example for you-

Table Creation and Data generation.
CREATE TABLE dbo.Reserves
(
     AccountNumber INT ,
     TransactionDate DATETIME,
     SalesRep VARCHAR(50),
     Amount NUMERIC(30,2)
)
GO
 
INSERT INTO dbo.Reserves VALUES
(1,GETDATE()-4,'Due',100),
(2,GETDATE()-2,'Parsons',100),
(3,GETDATE()-1,'Haverly',1000),
(4,GETDATE()-3,'Jason',100)
GO
 
CREATE TABLE dbo.FinalReserves
(
     AccountNumber INT ,
     TransactionDate DATETIME,
     SalesRep VARCHAR(50),
     Amount NUMERIC(30,2)
)
GO
 
INSERT INTO dbo.FinalReserves VALUES
(2,GETDATE()-2,'Parsons',100),
(3,GETDATE()-1,'Haverly',1000)
GO
 
SELECT * FROM dbo.FinalReserves
SELECT * FROM dbo.Reserves

Open in new window


SOLUTION
MERGE dbo.FinalReserves AS T
USING dbo.Reserves  AS S
ON (T.AccountNumber = S.AccountNumber AND CAST(T.TransactionDate AS DATE) = CAST(S.TransactionDate AS DATE) and  T.SalesRep = S.SalesRep)
WHEN NOT MATCHED 
    THEN INSERT(AccountNumber, TransactionDate,SalesRep,Amount) 
    VALUES(S.AccountNumber, S.TransactionDate,S.SalesRep,S.Amount)
WHEN MATCHED 
    THEN UPDATE SET T.Amount = S.Amount
;
GO

Open in new window


OUTPUT
/*------------------------
MERGE dbo.FinalReserves AS T
USING dbo.Reserves  AS S
ON (T.AccountNumber = S.AccountNumber AND CAST(T.TransactionDate AS DATE) = CAST(S.TransactionDate AS DATE) and  T.SalesRep = S.SalesRep)
WHEN NOT MATCHED 
    THEN INSERT(AccountNumber, TransactionDate,SalesRep,Amount) 
    VALUES(S.AccountNumber, S.TransactionDate,S.SalesRep,S.Amount)
WHEN MATCHED 
    THEN UPDATE SET T.Amount = S.Amount
;
GO
------------------------*/
 
(4 row(s) affected)
 
 
/*------------------------
SELECT * FROM dbo.FinalReserves
------------------------*/
AccountNumber TransactionDate         SalesRep                                           Amount
------------- ----------------------- -------------------------------------------------- ---------------------------------------
2             2017-10-05 14:24:20.463 Parsons                                            100.00
3             2017-10-06 14:24:20.463 Haverly                                            1000.00
1             2017-10-03 14:24:20.453 Due                                                100.00
4             2017-10-04 14:24:20.453 Jason                                              100.00
 
(4 row(s) affected)

Open in new window

1
 
Pawan KumarDatabase ExpertCommented:
You can use below-

If exists ( select top 1 1 from yourtable where col1 = @val1 and col2 = @col2.. )
Begin
     Update statement..
End
Else
Begin
    Insert statement..
End
0
 
Ryan ChongCommented:
And am aware of SQLBulkCopy Method.but it doesnot update the data if already presents
you still can perform a Bulk copy , but try to populate the data into a temporary (staging) table first, and then do a mass updates for the fields based on unique key verification. And finally do the data insertion for new entries.
1
 
awking00Commented:
What dbms are you using?
0
 
Tomas Helgi JohannssonCommented:
Hi!

As you mentioned SQLBulkCopy I presume you are using SQL Server.
The method you want to use on data that is already in the table as you insert new and/or same data is the MERGE statement.
In those databases where MERGE statement (or similar functionality with different command-name) is supported has proven to outperform the
"if record exist then update else insert" strategy by more than 50% in both CPU cost and execution time for large amount of data in most if not all of the DBMS that support MERGE.

Other links to look at
https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/
https://www.essentialsql.com/introduction-merge-statement/
https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

Talk about performance of Merge vs INSERT
https://technet.microsoft.com/en-us/library/cc879317(v=sql.105).aspx
http://www.sqlservercentral.com/articles/MERGE/103127/

Regards,
    Tomas Helgi
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.