December 30, 2011 at 8:41 am
Hello All,
I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date. Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A
id name address meter_flag end_date
23 john 1201 salt lake dr no 2011-12-28
24 tom 1222 gibson ln yes 2011-12-16
25 alex 1334 qayak dr no 2011-12-17
In Table B
23 john 1344 mc kinney st yes 2011-12-18
24 tom 1222 gibson ln yes 2011-12-16
56 gary 1335 pruitt rd no 2011-12-18
25 alex 1334 qayak dr no 2011-12-17
So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 tables.
Please assist How to do this
Thanks for any help
December 30, 2011 at 8:45 am
For things like this I usually rely on RedGate Data Compare.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 30, 2011 at 9:19 am
You're looking for the MERGE statement, which was introduced in SQL 2008. You can find more information in BOL. We still don't have SQL 2008, so I can't tell you how to use it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 30, 2011 at 9:47 am
What are you planning on doing with the data?
Ultimately does table B need to match table A?
If so you could do a snapshot replication, a trunk and load, merge statement as was mentioned depending on your version, or a group of insert, update and delete statements.
Or is the end goal entirely just to find what's changed where without modification to any data?
Would simple statements like the following be useful for you?
select * from table1 a, table2 b
where
a.primarykey = b.primarykey
and a.column1 <> b.column1
or a.primarykey = b.primarykey
and a.column2 <> b.column2
select * from table1 awhere
a.primarykey not in
(select b.primarykey
from table2 b
)
Is it always going to be for the same tables? How often are you going to need to see the differences?
Do you care about every column or are there specific columns you care more about like a quantity or price? How much is this going to scale? Does it need to be particularly performant?
December 30, 2011 at 9:56 am
If you just want the differences without updating, the easiest approach is to use the EXCEPT statement.
SELECT [id], [name], [address], meter_flag, end_date
FROM TableB
EXCEPT
SELECT [id], [name], [address], meter_flag, end_date
FROM TableA
This will give you every record from TableB that does not match exactly any record from TableA.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 30, 2011 at 1:21 pm
Yup. I used merge statement. It worked. Except was not giving the accurate results. That was weird.
December 31, 2011 at 9:04 am
You probably already know this but you can get a SQL Server 2008 R2 Developer Edition for under $50 USD.
I recently purchased it from Amazon. I have a 30 credit so I paid $13 and change including shipping.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 3, 2012 at 8:13 am
Welsh Corgi (12/31/2011)
@Drew,You probably already know this but you can get a SQL Server 2008 R2 Developer Edition for under $50 USD.
Yes, I already have it installed on my laptop at home, but I'm usually not on my laptop when I'm reading the forums and I also don't have large sets of test data on my laptop if a poster doesn't include their own data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 4, 2012 at 9:11 am
I had the same issue recently, so I wrote the following script. It assumes the first column is the PK, so you can change the logic if that is not correct. It works for different tables, and also for different databases in the same instance, in case you have a restored or TEST version of the same database and need to know how the records have been modified since the backup.
It is a script to create a script to find the differences in existing records between the two tables, and records in each of the tables that are not in the other one.
Hope it helps.
/* ====================================================================================================================== */
/*
By: VikingDBA
Date: 01/04/2012
This script creates a script to find the differences in two tables from either same database or two different databases on the
same instance (like a test version of a database, being tested by another group of people or with another program).
Also creates the scripts to find records in one table that are not in the other, for both tables.
Just set the Schema Name, Table Name, and Database Names for the two tables/databases, and the USE setting to set the context to the primary database.
(search for the word Reset to find them)
*/
-- Reset this value
USE <dbname1>-- Set this as the primary database name
GO
SET NOCOUNT ON
DECLARE @sn1 varchar(200)
DECLARE @tn1 varchar(200)
DECLARE @sn2 varchar(200)
DECLARE @tn2 varchar(200)
DECLARE @db1 varchar(200)
DECLARE @db2 varchar(200)
DECLARE @collist varchar(4000)
DECLARE @collist2 varchar(4000)
DECLARE @ordinalposition int
DECLARE @schemaname varchar(200)
DECLARE @tablename varchar(200)
DECLARE @dataelement varchar(200)
DECLARE @datatype varchar(200)
DECLARE @maxlen char(10)
DECLARE @prec char(10)
DECLARE @scale char(10)
DECLARE @comparevals varchar(8000)
DECLARE @ds varchar(8000)
DECLARE @wow varchar(400)
DECLARE @numelements int
DECLARE @cmd varchar(2000)
/* =============================================================================================================== */
/* Reset these values*/
SET @db1 = '<dbname1>'--Database 1 (a)
SET @sn1 = 'dbo'--Schema Name
SET @tn1 = '<tablename1>'--Table Name
SET @db2 = '<dbname2>'--Database 2 (b)
SET @sn2 = 'dbo'--Schema Name
SET @tn2 = '<tablename2>'--Table Name
/* =============================================================================================================== */
SET @collist = ''--leave blank
SET @collist2 = ''--leave blank
--only gets the column information from the first set
SELECT ORDINAL_POSITION AS 'OrdinalPosition',
TABLE_SCHEMA AS 'SchemaName',
TABLE_NAME AS 'TableName',
COLUMN_NAME AS 'DataElement', DATA_TYPE AS 'DataType',
ISNULL(CONVERT(char(10),CHARACTER_MAXIMUM_LENGTH),'') AS 'MaxLen', ISNULL(CONVERT(char(10),NUMERIC_PRECISION),'') AS 'Prec', ISNULL(CONVERT(char(10),NUMERIC_SCALE),'') AS 'Scale',
CONVERT(varchar(200),'') as ColNames,
CONVERT(varchar(400),'') as CompareVals
INTO #dummytable1
FROM information_schema.columns gg
WHERE TABLE_SCHEMA=@sn1 AND TABLE_NAME = @tn1
ORDER BY [SchemaName], TABLE_CATALOG, TABLE_NAME, ORDINAL_POSITION
SELECT @collist2=@collist2 + DataElement + ',' FROM #dummytable1 ORDER BY OrdinalPosition
SET @collist2 = SUBSTRING(@collist2,1,LEN(@collist2)-1)
UPDATE #dummytable1
SET ColNames = 'a.' + DataElement + ' AS a' + DataElement + ',b.' + DataElement + ' AS b' + DataElement + ','
SELECT @collist=@collist + ColNames FROM #dummytable1 ORDER BY OrdinalPosition
SET @collist = SUBSTRING(@collist,1,LEN(@collist)-1)
SELECT @numelements=MAX(OrdinalPosition) FROM #dummytable1
/* ============================================================================================== */
DECLARE backupFiles CURSOR FOR
SELECT OrdinalPosition,SchemaName,TableName,DataElement,DataType,MaxLen,Prec,Scale
FROM #dummytable1
ORDER BY OrdinalPosition
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @ordinalposition,@schemaname,@tablename,@dataelement,@datatype,@maxlen,@prec,@scale
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ds = ''
if @datatype = 'char' OR @datatype='nchar' or @datatype='varchar' or @datatype='nvarchar' or @datatype = 'varbinary'
SET @ds = 'ISNULL(a.' + @dataelement + ','''') <> ISNULL(b.' + @dataelement + ','''')'
if @datatype = 'datetime'
SET @ds = 'ISNULL(a.' + @dataelement + ',CONVERT(datetime,''01/01/1800'')) <> ISNULL(b.' + @dataelement +',CONVERT(datetime,''01/01/1800''))'
if @datatype = 'int' OR @datatype = 'smallint' OR @datatype = 'bigint' OR @datatype='tinyint'
SET @ds = 'ISNULL(a.' + @dataelement + ',0) <> ISNULL(b.' + @dataelement + ',0)'
if @datatype = 'decimal'
SET @ds = 'ISNULL(a.' + @dataelement + ',0.0) <> ISNULL(b.' + @dataelement + ',0.0)'
if @datatype = 'money'
SET @ds = 'ISNULL(a.' + @dataelement + ',0.0) <> ISNULL(b.' + @dataelement + ',0.0)'
if @datatype = 'bit'
SET @ds = 'ISNULL(a.' + @dataelement + ',0) <> ISNULL(b.' + @dataelement + ',0)'
if @datatype = 'text'
SET @ds = 'ISNULL(CONVERT(varchar(4000),a.' + @dataelement + '),'''') <> ISNULL(CONVERT(varchar(4000),b.' + @dataelement + '),'''')'
if @ds = '' --AND @datatype <> 'text'
SET @ds = 'a.' + @dataelement + ' <> b.' + @dataelement
if @ds <> ''
BEGIN
UPDATE #dummytable1
SET CompareVals=@ds
WHERE OrdinalPosition=@ordinalPosition
END
FETCH NEXT FROM backupFiles INTO @ordinalposition,@schemaname,@tablename,@dataelement,@datatype,@maxlen,@prec,@scale
END
CLOSE backupFiles
DEALLOCATE backupFiles
/* ============================================================================================== */
SET @wow = (SELECT DataElement FROM #dummytable1 WHERE OrdinalPosition=1)
PRINT '--Find records that are different in ' + @db1 + '.' + @sn1 + '.' + @tn1 + ' and ' + @db2 + '.' + @sn2 + '.' + @tn2
PRINT 'SELECT ' + @collist
PRINT 'FROM ' + @db1 + '.' + @sn1 + '.' + @tn1 + ' a JOIN ' + @db2 + '.' + @sn2 + '.' + @tn2 + ' b'
PRINT 'ON a.' + @wow + ' = b.' + @wow
PRINT 'WHERE'
DECLARE backupFiles CURSOR FOR
SELECT OrdinalPosition,SchemaName,TableName,DataElement,DataType,MaxLen,Prec,Scale,CompareVals
FROM #dummytable1
ORDER BY OrdinalPosition
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @ordinalposition,@schemaname,@tablename,@dataelement,@datatype,@maxlen,@prec,@scale,@comparevals
WHILE @@FETCH_STATUS = 0
BEGIN
if @ordinalposition=@numelements
SET @ds = @comparevals
else
SET @ds = @comparevals + ' OR'
if @comparevals <> ''
PRINT @ds
FETCH NEXT FROM backupFiles INTO @ordinalposition,@schemaname,@tablename,@dataelement,@datatype,@maxlen,@prec,@scale,@comparevals
END
CLOSE backupFiles
DEALLOCATE backupFiles
--SELECT * FROM #dummytable1
PRINT ''
PRINT '--Find records in ' + @db1 + '.' + @sn1 + '.' + @tn1 + ' that are not in ' + @db2 + '.' + @sn2 + '.' + @tn2
PRINT 'SELECT ' + @collist2
PRINT 'FROM ' + @db1 + '.' + @sn1 + '.' + @tn1
PRINT 'WHERE ' + @wow + ' NOT IN (SELECT a.' + @wow + ' FROM ' + @db2 + '.' + @sn2 + '.' + @tn2 + ' a)'
PRINT ''
PRINT ''
PRINT '--Find records in ' + @db2 + '.' + @sn2 + '.' + @tn2 + ' that are not in ' + @db1 + '.' + @sn1 + '.' + @tn1
PRINT 'SELECT ' + @collist2
PRINT 'FROM ' + @db2 + '.' + @sn2 + '.' + @tn2
PRINT 'WHERE ' + @wow + ' NOT IN (SELECT b.' + @wow + ' FROM ' + @db1 + '.' + @sn1 + '.' + @tn1 + ' b)'
DROP TABLE #dummytable1
SET NOCOUNT OFF
June 18, 2015 at 9:02 am
Nice Work VikingDBA!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply