Determining modified data from 2 identical tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Determining modified data from 2 identical tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-07 : 11:01:08

Ken writes "My problem concerns reading rows from 2 identical tables within SQL2000.

The tables store data copied from a Foxpro database, where one table holds a previous copy of the data, and the other table is a copy of the existing data.

What I want to do is copy only changed data to a third table for transmitting over our WAN.
There is no way of easily telling from the data whether the data has been modified, ie there is no date/timestamp column, and I can't modify the Foxpro tables/programs to add this column.

My idea was to use Dynamic SQL.
By using the Information_Schema.Columns I could read the columns from one of the tables into a variable, and then step through the rows reading each column value, equal to the variable, for each of the 2 tables where the unique keys are identical, and if the values in the columns don't match then I know the data has been modified. Well so my theory went, until I discovered that my Select @variable is using the value in the variable rather than using the value as a column name. My SQL is as follows

-- Get column names from Information_Schema.Columns
INSERT INTO [dbo].[tempColumnNames]
(Table_Name, Column_Name, Ordinal_Position)
(SELECT Table_Name, Column_Name, Ordinal_Position
FROM Information_Schema.Columns
WHERE Table_Name = @Table_Name)

DECLARE column_Cursor CURSOR FOR
SELECT Column_Name
FROM [dbo].[tempColumnNames]

SET @sql = ''
SET @sql = @sql + 'DECLARE @Rec_Id numeric(9,0),' + char(10)
SET @sql = @sql + ' @Column_Name varchar(255),' + char(10)
SET @sql = @sql + ' @Loop_Cnt numeric(9,0),' + char(10)
SET @sql = @sql + ' @Match bit' + char(10)
SET @sql = @sql + 'OPEN address1_Cursor' + char(10)
SET @sql = @sql + 'FETCH NEXT FROM address1_Cursor' + char(10)
SET @sql = @sql + 'INTO @Rec_Id' + char(10)
SET @sql = @sql + 'SET @Loop_Cnt = 1' + char(10)
SET @sql = @sql + ' WHILE @Loop_Cnt <= 7 AND' + char(10)
SET @sql = @sql + ' @@FETCH_STATUS = 0' + char(10)
SET @sql = @sql + ' BEGIN' + char(10)
SET @sql = @sql + ' SET @Match = 1' + char(10)
SET @sql = @sql + ' OPEN column_Cursor' + char(10)
SET @sql = @sql + ' FETCH NEXT FROM column_Cursor' + char(10)
SET @sql = @sql + ' INTO @Column_Name' + char(10)
SET @sql = @sql + ' PRINT @Column_Name' + char(10)
SET @sql = @sql + ' PRINT @Match' + char(10)
SET @sql = @sql + ' WHILE @Match = 1 AND' + char(10)
SET @sql = @sql + ' @@FETCH_STATUS = 0' + char(10)
SET @sql = @sql + ' BEGIN' + char(10)
SET @sql = @sql + ' IF ((SELECT @Column_Name' + char(10)
SET @sql = @sql + ' FROM ADDRESS' + char(10)
SET @sql = @sql + ' WHERE REC_ID = @Rec_Id) =' + char(10)
SET @sql = @sql + ' (SELECT @Column_Name' + char(10)
SET @sql = @sql + ' FROM ADDRESS2' + char(10)
SET @sql = @sql + ' WHERE REC_ID = @Rec_Id))' + char(10)
SET @sql = @sql + ' SET @Match = 0' + char(10)
SET @sql = @sql + ' ELSE' + char(10)
SET @sql = @sql + ' SET @Match = 1 ' + char(10)
SET @sql = @sql + ' FETCH NEXT FROM column_Cursor' + char(10)
SET @sql = @sql + ' INTO @Column_Name' + char(10)
SET @sql = @sql + ' PRINT @Column_Name' + char(10)
SET @sql = @sql + ' PRINT @Match' + char(10)
SET @sql = @sql + ' END' + char(10)
SET @sql = @sql + ' PRINT @Rec_id' + char(10)
SET @sql = @sql + ' CLOSE column_Cursor' + char(10)
--SET @sql = @sql + ' PRINT @Rec_id' + char(10)
SET @sql = @sql + ' FETCH NEXT FROM address1_Cursor' + char(10)
SET @sql = @sql + ' INTO @Rec_Id' + char(10)
SET @sql = @sql + ' SET @Loop_Cnt = @Loop_Cnt + 1' + char(10)
SET @sql = @sql + ' END' + char(10)

SET @sql = @sql + 'CLOSE address1_Cursor' + char(10)

EXECUTE (@sql)

The inner loop for the column name value drops out after the first column, as
Select 'column_name

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-07 : 12:26:06

eeeeeeeeeeeeeeeeeeewwwwwwwwwwwwwwwwwwwwWWWWWWWWWWWWWWWWWWW!

Why don't you just do this?

SELECT A1.*
FROM Address A1 INNER JOIN Address2 A2
ON A1.Rec_ID=A2.Rec_ID
WHERE A1.col1 <> A2.col1
OR A1.col2 <> A2.col2
OR A1.col3 <> A2.col3
OR A1.col4 <> A2.col4
OR A1.col5 <> A2.col5 --add an OR statement for each column that needs to be checked

You'll get all of the rows in Address that differ from Address2, regardless of which colum(s) were changed. If you need the rows from Address2 instead, just change it to SELECT A2.* instead of SELECT A1.*. I'd recommend auditing the entire row, not just the modified columns (trust me on this one)

Edited by - robvolk on 05/07/2002 12:28:10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources