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

Question

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

I tried the SQL EXCEPT and that worked! However, I have been looking for other methods that could potentially identify differences. Do you have any suggestions? — Maurice, Dec 08 '16 at 18:23
Join A&B on the "identity" field, have a where clause which selects `A.data <> B.data` to select data that would differ. — TT., Dec 08 '16 at 18:49

score 1 · Answer 1 · answered Dec 08 '16 at 18:55

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
);

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

1 Answers1