HOW TO COMPARE 2 SQL TABLES 1

[ashishsmith]

How to Compare 2 MS SQL Tables, that if they are same or not? I have a temp table which has old data and a new table which deletes old data and updates with new data.

 

[NoCoolHandle]

I am assuming you want to compare both tables and see only rows that don't exist in both..

Is is speciflcly 1 side or both sides you are looking at?

The following script finds rows that don't exist on either side... J

If is has a null that table doesn't have a row..

If you just want rows that don't exist in a then use a "Left Outer Join"

If it is in B change to "Right Outer Join"


HTH

Rob

CREATE TABLE A (C1 INT ,C2 INT ,C3 INT)
GO
CREATE TABLE B  (C1 INT ,C2 INT ,C3 INT)
GO
INSERT INTO A VALUES (1,1,3)
INSERT INTO A VALUES (2,1,3)
INSERT INTO A VALUES (3,1,3)
INSERT INTO A VALUES (4,1,3)
INSERT INTO A VALUES (5,1,3)
INSERT INTO A VALUES (6,1,3)
INSERT INTO A VALUES (1,2,3)
INSERT INTO A VALUES (2,3,3)
INSERT INTO A VALUES (3,3,3)
INSERT INTO A VALUES (4,3,3)
INSERT INTO A VALUES (5,3,3)
INSERT INTO A VALUES (6,3,3)

INSERT INTO b VALUES (1,4,3)
INSERT INTO B VALUES (2,4,3)
INSERT INTO B VALUES (3,4,3)
INSERT INTO B VALUES (4,1,3)
INSERT INTO B VALUES (5,1,3)
INSERT INTO B VALUES (6,1,3)
INSERT INTO B VALUES (1,2,3)
INSERT INTO B VALUES (2,3,3)
INSERT INTO B VALUES (3,3,3)
INSERT INTO B VALUES (4,3,3)
INSERT INTO B VALUES (5,4,3)
INSERT INTO B VALUES (6,3,3)
GO
SELECT A.*,b.* FROM A FULL OUTER JOIN B ON A.C1 = B.C1 AND A.C2 = B.C2 AND A.C3 = B.C3
WHERE A.C1 IS NULL OR B.C1 IS NULL

 

[ashishsmith]

Hello NoCoolHandle,

Thank you for your post. But I think you didn't get my question. What happens is, I want update my tables every friday through a DTS package. Now the data base is big hence, I want to check automatically that did it get transferred or not. First this database gets transfered into a temp sql table from a flat text file on another server. Now after finishing that task another DTS package runs and temp sql table gets transfered into the final sql table, which we use. I just want to check my final sql table with the flat file and see if every record matches and everything got transfered or not. Because sometimes DTS package don't run for some unexplanable reasons. Do you know if we can check it through SQL Query? or we can run two DTS packages and then compare two sql table, I don't know and still confused but if you have some ideas than it would be highly appreciated.

 

[NoCoolHandle]

Maybe a silly question (but I suspect very important), but just how much data are we playing with? 100's of megs? Millions of rows?

 

[ashishsmith]

yes there are atleast 100000 rows and that is minimum. Most of the data is same always.

 

[NoCoolHandle]

I think you can you the msaccess driver to open the file directly via an openrowset function. THen you could treat it (the flat file) as a sql table and do a direct outerjoin.

I will check the syntax for opening a textfile.. (and get back)

Rob

 

[NoCoolHandle]

The web is a wonderfull place took about 10 seconds to find this code snippet.

Select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
  DefaultDir=D:\folder;','select  * from mytext.txt') [i]myfile[/i]

At that point you have sql opening a textfile called mytextfile.text, it is being aliased as myfile in the query and can be joined to other tables as a table called myfile..

One Quick word of warning. There is a nasty textwrap that could cause a bad (hard to find) error. You need to make sure that ther is a space between the semicolon and the * in
"Driver (*.txt; *.csv)" -> reason: the name of the driver has a space in it, no space, no driver

I have used this with comma sep and it seems to work great, I would also think fixed col should work.

1 last thought. Have you looked at "bulk insert"? It is really, really fast and you could run a quick proc that would check values every day.. Then it would be a matter of populate the table run the query and drop the table.. Quite easy in the logic of a stored proc.

syntax for bulk insert

BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM 'data_file' }
[ WITH
(
[ BATCHSIZE [ = batch_size ] ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ]
[ [ , ] DATAFILETYPE [ =
{ 'char' | 'native'| 'widechar' | 'widenative' } ] ]
[ [ , ] FIELDTERMINATOR [ = 'field_terminator' ] ]
[ [ , ] FIRSTROW [ = first_row ] ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ]
[ [ , ] LASTROW [ = last_row ] ]
[ [ , ] MAXERRORS [ = max_errors ] ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH [ = rows_per_batch ] ]
[ [ , ] ROWTERMINATOR [ = 'row_terminator' ] ]
[ [ , ] TABLOCK ]
)

 

[ashishsmith]

Thank you very much for your valuable suggestions. I will try these on my server see if it works. Thanx again for your prompt reply.

 

[NoCoolHandle]

I forgot-> another word of warning with the openrowset..
If you are opening a comma seperated textfile the comma's tend to "create" spontanious columns well ok

That is one thing for the Bulk Insert. I think you have a alot more control over how the information is processed.

Rob