June 10, 2011 at 10:04 am
Hi All,
I have a requirement where I need to load data from one server to another server. Destination table is also created. Everyday I truncate that and repopulate data from SourceServer.SourceDatabase.
Sometimes table definition may be changed (new column added/renamed) at Source and in that case I do not load the destination table; rather just display a message saying column miss match. With the help of some old posts I came up with below code which doesn't seem to work for me.
declare @SourceServer nvarchar(50)
declare @DestServer nvarchar(50)
declare @SourceDB nvarchar(50)
declare @DestDB nvarchar(50)
declare @TableList nvarchar(max)
set @SourceServer = 'SrcServer'
set @DestServer = 'DestServer'
set @SourceDB = 'SrcDB'
set @DestDB = 'DestDB'
declare @TableName nvarchar(100)
set @TableName = 'Tables_ToCompare'
declare @nSQL nvarchar(max)
set @nSQL = 'SELECT * FROM Information_Schema.Columns A
FULL OUTER JOIN Information_Schema.Columns B
ON A.Column_Name = B.Column_Name
AND B.Table_Name = ''' + @DestServer +'.' + @DestDB +'.dbo.' + @tableName +'''
AND B.Table_Schema = ''dbo''
WHERE
A.Table_Schema = ''dbo''
AND A.Table_Name = ''' + @SourceServer +'.' + @SourceDB +'.dbo.' + @tableName +'''
AND (A.Column_Name IS NULL OR B.Column_Name IS NULL)'
print @nSQL
exec (@nSQL)
I think the issue is with the way I have defined B.Table_name and A.Table_name. I have a table created at both the servers with different columns but I still don't get any result. Can anyone help me with what am I doing wrong here?
Thanks in advance...
July 3, 2011 at 9:00 pm
I have a suggestion, why are you checking the data types and colunmn names.
Is it possible for you to drop and recreate the table instead on the destination server.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply