I am using SQL Server and want to do Bulk Updates but don't want any row to be re-updated if same values already exists.
For Example Consider following tables with their data
DECLARE @UpdatedIds TABLE
(
BookId INT
)
CREATE TABLE Book
(
[Id] INT,
[Name] NVARCHAR(MAX)
)
INSERT INTO Book([Id], [Name])
SELECT 1, 'Book1'
UNION
SELECT 2, 'Book2'
UNION
SELECT 3, 'Book3'
UNION
SELECT 4, 'Book4'
UNION
SELECT 5, 'Book5'
CREATE TABLE #Book
(
[Id] INT,
[Name] NVARCHAR(MAX)
)
INSERT INTO #Book([Id], [Name])
SELECT 1, 'Book1_Changed'
UNION
SELECT 2, 'Book2_Changed'
UNION
SELECT 3, 'Book3'
UNION
SELECT 4, 'Book4'
UNION
SELECT 5, 'Book5'
What I want is when I run the following query
UPDATE [BO]
SET [Name] = [BU].[Name]
OUTPUT [INSERTED].[Id] INTO @UpdatedIds([BookId])
FROM [Book] [BO]
INNER JOIN #Book [BU] ON [BO].[Id] = [BU].[Id]
SELECT * FROM @UpdatedIds
@UpdatedIds should have records of 1 and 2 not 3, 4, or 5
I have 2 ways of doing this in my mind and would like to have opinions about which one will be fastest/better.
Using Except Keyword and delete rows that already exists
Using Binary_Checksum that is comparing Checksums of records in actual table and in temp table and removing records with equal checksum
Thanks