CODEWITHSUNDEEP
sql-servert-sqlvarbinarymax
Maurice
Maurice

Reputation: 75

T-SQL - Validate BLOB / varbinary(max) data

Data is being migrated from Table A to Table B.

Table A has 2 columns - an identity column and a column Content defined as varbinary(max).

How can I validate that Table B has been loaded with correct data for Content column? Is T_SQL's EXCEPT operator good enough? Are there alternate methods?

Thanks

Upvotes: 0

Views: 413

Answers (1)

Alan Burstein
Alan Burstein

Reputation: 7918

EXCEPT is the tool for this job. Note that unlike other SQL Server set operators, the order makes a difference. Here's one way to validate using EXCEPT: 

-- sample data
DECLARE @table1 TABLE (id int identity, content varbinary(max));
DECLARE @table2 TABLE (id int identity, content varbinary(max));

INSERT @table1(content) VALUES (12), (15), (20);
INSERT @table2(content)
SELECT content 
FROM @table1;

--solution
(
  SELECT id, content FROM @table1 
  EXCEPT 
  SELECT id, content FROM @table2
)
UNION ALL 
(
  SELECT id, content FROM @table2
  EXCEPT 
  SELECT id, content FROM @table1
);

Upvotes: 1

Related Questions