February 11, 2008 at 12:17 pm
Im very lost, I need to write a stored procedure that would compare the contents of two identical tables and perform Insert if new records exist, Update of existing records and a Delete based on WHERE criteria.
I have referred to http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx.In this site table name and column names are passed within the query.This is exactly what I need as I would need to use the stored procedure for other sets of Identical Tables.I have used Adventureworks table Person.Address as the old table and Person.Address.New[same schema as Person.Address] as the new table.Here is what I have come up with, I am just uncertain how to fit it with the code that is already there.
My Code
IF ('[Person.Address.New].[AddressID]'!='[Person].[Address].[AddressID]')--INSERT NEW RECORDS
insert into [Person.Address.New]
SELECT [AddressID],[AddressLine1],[AddressLine2]
,[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate]
FROM (SELECT '[Person].[Address]' AS TableName,
[AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate]
FROM [Person].[Address]
UNION ALL
SELECT '[Person.Address.New]' As TableName, [AddressID],[AddressLine1],[AddressLine2]
,[City],[StateProvinceID]
,[PostalCode]
,[rowguid],[ModifiedDate]
FROM [Person.Address.New]
) A
GROUP BY [AddressID],[AddressLine1],[AddressLine2]
,[City],[StateProvinceID]
,[PostalCode],[rowguid]
,[ModifiedDate]
HAVING COUNT(*) = 1
END
ELSE IF ('[Person.Address.New].[AddressID]'='[Person].[Address].[AddressID]')--UPDATE EXISTING RECORDS
UPDATE [Person.Address.New]
SET [Person.Address.New].[AddressID]=[Person].[Address].[AddressID]
,[Person.Address.New].[AddressLine1]=[Person].[Address].[AddressLine1]
,[Person.Address.New].[AddressLine2]=[Person].[Address].[AddressLine2]
,[Person.Address.New].[City]=[Person].[Address].[City]
,[Person.Address.New].[StateProvinceID]=[Person].[Address].[StateProvinceID]
,[Person.Address.New].[PostalCode]=[Person].[Address].[PostalCode]
,[Person.Address.New].[rowguid]=[Person].[Address].[rowguid]
,[Person.Address.New].[ModifiedDate]=[Person].[Address].[ModifiedDate]
FROM [Person.Address.New],[Person].[Address]
Code that is in Place
Create PROCEDURE CompareTables
(@table1 varchar(100),
@table2 Varchar(100),
@T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS
declare @SQL varchar(8000);
IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList
set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' +
@t1ColumnList + ' FROM ' + @Table1 + ' UNION ALL SELECT ''' +
@table2 + ''' As TableName, ' + @t2ColumnList + ' FROM ' + @Table2
set @SQL = 'SELECT Max(TableName) as TableName, ' +
@t1ColumnList + ' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'
How do I include my code into the above code.Do give me some directions.Thank You!
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
February 11, 2008 at 12:30 pm
This is concerning SQL2005 right ?
You may want to check on these new join methodes:
EXCEPT and INTERSECT
http://msdn2.microsoft.com/en-us/library/ms188055.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 11, 2008 at 2:29 pm
Ok, I have tried the following
Create PROCEDURE CompareTables
(@table1 varchar(100),
@table2 Varchar(100),
@T1PK varchar(50), --Primary Key
@T2PK varchar(50), --Primary Key
@T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS
declare @SQL varchar(8000);
declare @SQL1 varchar(8000);
declare @SQL2 varchar(8000);
IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList
IF @T2PK = '' SET @T2PK = @T1PK
IF(@T2PK!=@T1PK)---INSERT NEW RECORD
set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' +
@t1ColumnList + ' FROM ' + @Table1 + ' UNION ALL SELECT ''' +
@table2 + ''' As TableName, ' + @t2ColumnList + ' FROM ' + @Table2
set @SQL1='INSERT INTO'''+ @table2 +''
set @SQL2='SELECT Max(TableName) as TableName, ' +
@t1ColumnList + ' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'
IF(@T2PK=@T1PK)--UPDATE EXISTING RECORD
set @SQL=''
set @SQL='UPDATE'''+ @table2 +'''SET'+ @t2ColumnList + '''='' '+ @t1ColumnList + '
FROM '+ @Table1 + ',''+ @Table2 + '
--print @SQL
exec ( @SQL)
exec ( @SQL1)
exec ( @SQL2)
Go
NO ERRORS IN CODE BUT WHEN I EXECUTE
exec CompareTables
'[Person].[Address]','[Person.Address.New]',
'[Person].[Address].[AddressID]','[Person.Address.New].[AddressID]',
'[AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate]'
I receive the following Error's
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '[Person.Address.New]'.
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string '+ @Table2 + '.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string '[Person.Address.New]'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '[Person.Address.New]'.
Im using table Person.Address in Adventureworks and created an identical table Person.Address.New .
I really need help as I am uncertain where I am going wrong.:crying:
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
February 11, 2008 at 2:48 pm
Rather than trying to come up with a query for this, which may take you a huge amount of time, why not use one of the products available on the market for comparing databases and tables?
Redgate has one (SQL Compare), ApexSQL has one (SQLDiff), there might be others.
You'll spend a few bucks (after the free trial), and you'll have a much more complete tool.
If you really, really, really have to build this on your own, it looks like a relatively major query to build.
First, you have the need to compare data from multiple tables (since the table names and columns are input parameters). This means building something flexible enough to deal with that. This means the proc will need to be able to tell which columns in all possible tables are the primary key, and then be able to build a string based on that to determine which rows to insert, which to update and which to delete. That, by itself, is a relatively major undertaking (or seems so to me).
If I had to compare data, I'd either use replication, or get one of the compare programs available on the market.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2008 at 2:51 pm
Hi There,
I have no choice but to use the stored procedure as my company is not wiling to spend the big bucks.Hope you can provide me with some suggestions on the code.:sick:
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
February 11, 2008 at 3:16 pm
"The big bucks" is about $400. If you're in the usual range of pay for a database developer, that comes out to less than a week's pay. Tell them it will take more than a week to build (which is true), or they can buy. ROI on that one is pretty clear.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2008 at 3:19 pm
The best thing I can suggest for getting this script moving in the right direction is add a Try Catch function, and in the Catch include "select @SQL as [Script]". Then you'll be able to see what it's trying to run. Copy and paste that into a new connection and see if you can clean up the errors in it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2008 at 3:26 pm
Also - Andy Leonard's article from today outlines pretty much all of the steps for doing all of this using SSIS. You're describing an incremental load pretty much to a T.
You might get a whole lot farther with that than with a stored procedure you don't fully understand.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 11, 2008 at 4:41 pm
Since you are trying to make both tables equal, wouldn't something like this work?
TRUNCATE TABLE Table2
INSERT Table2 SELECT * FROM Table1
Best Regards,
Chris Büttner
February 11, 2008 at 6:20 pm
Absolutely perfect, Chris... I've always wondered why people try to do it the hard way.
Another way to do this is to simply have 2 tables and a view. While you're filling up one table, the view is pointing to the other. Once you have the table filled, just alter the view to point at the new table.
I believe that SQL Server 2k5 also has "synonyms"... that would be the most effective way to do this. Total "downtime" for repointing a view or a synonym is something less than 30 milliseconds... and there's no blocking... period.
--Jeff Moden
February 12, 2008 at 2:05 am
Thanks for the compliments!
I like days that start like this:-)
Best Regards,
Chris Büttner
February 12, 2008 at 6:52 am
Hi Guys, I was busy still trying to figure out how to make this work.
I have no choice but to use stored proedure, as my boss is insisting on using it .Here is the original scenario.
We have two database with identical tables.1[Current db] and 1[Past db].What he wants me to do is to implement this sp as to compare and load each identical tables after new input is given by customers into Current db.Meaning compare and reload all tables that has Update/Insert in Past db based on recent operations on Current db.So Past db would always have historical data and Current db would have new input from Customer on daily basis.
Did some modification to this sp, but still has 1 error.
CREATE PROCEDURE CompareTables
(@table1 varchar(100),
@table2 Varchar(100),
@T1PK varchar(50),
@T2PK varchar(50),
@T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS
declare @SQL varchar(8000);
declare @SQL1 varchar(8000);
declare @SQL2 varchar(8000);
declare @Col1Count int,@Col2Count int
IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList
IF @T2PK = '' SET @T2PK = @T1PK
IF(@T2PK!=@T1PK)--INSERT NEW RECORD
BEGIN
set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' +
@t1ColumnList + ' FROM ' + @table1 + ' UNION ALL SELECT ''' +
@table2 + ''' As TableName, ' + @t2ColumnList + ' FROM ' + @Table2
set @SQL1='INSERT INTO '+ @table2 +
'
SELECT Max(TableName) as TableName, ' +
@t1ColumnList + ' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'
EXEC @SQL1
END
IF(@T2PK=@T1PK)--UPDATE EXISTING RECORDS
BEGIN
set @SQL=''
set @Col1Count=(LEN(@t1ColumnList)-LEN(REPLACE(@t1ColumnList,',',''))) + 1
set @Col2Count=(LEN(@t2ColumnList)-LEN(REPLACE(@t2ColumnList,',',''))) + 1
IF @Col1Count <> @Col2Count
PRINT 'Column lists are not same'
ELSE
BEGIN
DECLARE @Col1 varchar(50),@Col2 varchar(50)
WHILE @t1ColumnList IS NOT NULL AND @t2ColumnList IS NOT NULL
BEGIN
SELECT @Col1=LEFT(@t1ColumnList,CASE WHEN CHARINDEX(',',@t1ColumnList)>0 THEN CHARINDEX(',',@t1ColumnList) ELSE LEN(@t1ColumnList)+1 END -1),
@Col2=LEFT(@t2ColumnList,CASE WHEN CHARINDEX(',',@t2ColumnList)>0 THEN CHARINDEX(',',@t2ColumnList) ELSE LEN(@t1ColumnList)+1 END-1)
set @SQL2=COALESCE(@SQL2,'')+ @table2+'.'+ @Col1 + '='+ @table1+'.'+@Col2 + ','
SELECT @t1ColumnList=SUBSTRING(@t1ColumnList,CASE WHEN CHARINDEX(',',@t1ColumnList)>0 THEN CHARINDEX(',',@t1ColumnList) ELSE NULL END +1,LEN(@t1ColumnList)),
@t2ColumnList=SUBSTRING(@t2ColumnList,CASE WHEN CHARINDEX(',',@t2ColumnList)>0 THEN CHARINDEX(',',@t2ColumnList) ELSE NULL END+1,LEN(@t2ColumnList))
END
set @SQL='UPDATE '+ @table2 + ' SET '+ LEFT(@SQL2,LEN(@SQL2)-1) + '
FROM '+ @Table1 --+ ','''+ @Table2 + ''
END
Exec (@SQL)
END
Go
Im receiving this error now,
Msg 203, Level 16, State 2, Procedure CompareTables, Line 27
The name 'INSERT INTO [Person.Address.New]
SELECT Max(TableName) as TableName, [AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate] FROM (SELECT '[Person].[Address]' AS TableName, [AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate] FROM [Person].[Address] UNION ALL SELECT '[Person.Address.New]' As TableName, [AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate] FROM [Person.Address.New]) A GROUP BY [AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[Modi' is not a valid identifier.
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
February 12, 2008 at 7:09 am
Hello,
why at all do you need to make this process dynamic?
Why not just create the INSERT & UPDATE statements per each table?
And for what reason does he want to have an SP like you did it? To make sure noone will understand the code anymore in a few days later?
Btw, the time you would have to invest in writing the code per each table is probably a lot less than you are spending right now on the dynamic solution.
Best Regards,
Chris Büttner
February 12, 2008 at 7:29 am
Hi Chris,
I totally get you, but my employer is just over the hill to use a dynamic stored procedure.If only I can get this working, as I have a strict deadline.Do have a look at my code and let me know if there is anything I can do to get it work.
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
February 12, 2008 at 7:40 am
If you are using SQL 2005, use TableDiff.exe, more information see the SQL BOL. It is a very good tool provided by Microsoft.
Just try it.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply