Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сравнение  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
Пришлось сравнивать данные, набросал процедуру для сравнения таблиц/резалтсетов.
Мож. кому пригодится.
Так же заранее спс. за критику/ предложения.

Пример:
exec [sp_Compare]
--1. Выражение
'
select * from msdb.sys.objects 
'
--2. Выражение
,
'
select * from master.sys.objects
'
--оперция сравнения
,
'not equal'
--исключить столбцы
,'create_date'
--опции
,''


Параметры:
@tab1 nvarchar(max)	           --Имя первой/левой таблицы или выражение SELECT ...; CTE не поддерживаются			
@tab2 nvarchar(max)	           --Имя второй/правой таблицы или выражение SELECT ...; CTE не поддерживаются			
@operation nvarchar(10)		   --Операция сравнения или её сокращённое обозначение equal (e), not equal (n), left (l), right (r)
@exclude_cols nvarchar(max)  = ''  --Столбцы, которыу нужно исключить  (через запятую)
@options varchar(max)	    = ''  --Опции: таблица OUTPUT; order by ...; noexec; use_tempdb   (;)

Опции подробно:
таблица OUTPUT:  вывод в таблицу. Задавать полное имя база.схема.таблица. 
  Если таблица существует, её столбцы должны соответствовать ожидаемому выводу. 
  Есле не существует, она бкдет создана. 

order by ...  : сортировка вывода. последним столбцом сортировкм всегда будет добавляться столбец Mask. 
  Таким образом различающиеся строки располагаются друг над другом. 

noexec:			вывод динамического выражения 

use_temp:		результаты первого и второго выражения кэшируются        


Тело:
-- =============================================
-- Author:		A.Uz
-- Create date: 16.06.2015
-- 'Сравнение на принципе изложенном в части 3 статьи "Работа с множествами в Transact-SQL": http://www.realcoding.net/articles/rabota-s-mnozhestvami-v-transact-sql.html
-- =============================================
ALTER PROCEDURE [dbo].[sp_Compare] 
	  @tab1 nvarchar(max)				
	, @tab2 nvarchar(max)				
	, @operation nvarchar(10)			
	, @exclude_cols nvarchar(max)	= ''
	, @options varchar(max)			= ''
AS
BEGIN
	SET NOCOUNT ON;
    	
------------------------------

declare @tabcols1 table(cid int, name sysname, tid int)
declare @tabcols2 table(cid int, name sysname, tid int)
declare @from1 nvarchar(max)
declare @from2 nvarchar(max)
declare @colsextern nvarchar(max)
declare @colsintern1 nvarchar(max)
declare @colsintern2 nvarchar(max)
declare @sql nvarchar(max)
declare @op_expr nvarchar(100)
declare @server1 sysname, @database1 sysname, @schema1 sysname, @table1 sysname
declare @server2 sysname, @database2 sysname, @schema2 sysname, @table2 sysname
declare @temptabname1 sysname = '#' + replace(NEWID() ,'-','') 
declare @temptabname2 sysname = '#' + replace(NEWID() ,'-','') 
declare @xml xml
declare @exclude_cols_tab table(name sysname)
declare @options_tab table(value nvarchar(max)) 
declare @mask_column sysname = ''

--caller database
declare @caller_db sysname
select  @caller_db=db_name(resource_database_id) FROM sys.dm_tran_locks WHERE request_session_id = @@SPID and resource_type = 'DATABASE' and request_owner_type = 'SHARED_TRANSACTION_WORKSPACE' and resource_database_id<>db_id()

set @xml = '<c>'+replace(@exclude_cols,',','</c><c>')+'</c>'
insert @exclude_cols_tab select distinct ltrim(rtrim(a.b.value('.','sysname'))) from @xml.nodes('c') a(b)

set @xml = N'<o>'+replace(@options,N';',N'</o><o>')+N'</o>'
insert @options_tab select distinct ltrim(rtrim(a.b.value('.','sysname'))) from @xml.nodes('o') a(b)


if charindex('select', @tab1 ,0)>0
begin
	set @from1 = '(' + @tab1 + ')T'			
end
else
begin
  set @tab1=rtrim(ltrim(replace(replace(@tab1,char(10),''),char(13),'')))  
  set @server1		= parsename(@tab1,4)
  set @database1	= parsename(@tab1,3)
  set @schema1		= parsename(@tab1,2)
  set @table1		= parsename(@tab1,1)

  if @schema1 is NULL
    set @schema1 = 'dbo'


  if @database1 is NULL and @caller_db<>db_name()
    set @database1 = @caller_db
  	
	set @tab1 = '[' + @table1 + ']'

	if @schema1 is not null and @schema1 <> 'dbo'
		set @tab1 = '[' + @schema1 + '].[' + @table1 + ']'
 
	if @database1 is not null 
		set @tab1 = '[' + @database1 + '].[' + @schema1 + '].[' + @table1 + ']'

	if @server1 is not null 
		set @tab1 = '[' + @server1 + '].[' + @database1 + '].[' + @schema1 + '].[' + @table1 + ']'

  set @from1 = @tab1 + ' with(nolock)'
end 
	

if charindex('select', @tab2 ,0)>0
begin
	set @from2 = '(' + @tab2 + ')T'	
end
else
begin
  set @tab2=rtrim(ltrim(replace(replace(@tab2,char(10),''),char(13),'')))
  set @server2		= parsename(@tab2,4)
  set @database2	= parsename(@tab2,3)
  set @schema2		= parsename(@tab2,2)
  set @table2		= parsename(@tab2,1)

  if @schema2 is NULL
    set @schema2 = 'dbo'

  if @database2 is NULL and @caller_db<>db_name()
    set @database2 = @caller_db
  	
	set @tab2 = '[' + @table2 + ']'

	if @schema2 is not null and @schema2 <> 'dbo'
		set @tab2 = '[' + @schema2 + '].[' + @table2 + ']'
 
	if @database2 is not null 
		set @tab2 = '[' + @database2 + '].[' + @schema2 + '].[' + @table2 + ']'

	if @server2 is not null 
		set @tab2 = '[' + @server2 + '].[' + @database2 + '].[' + @schema2 + '].[' + @table2 + ']'

  set @from2 = @tab2 + ' with(nolock)'
end 


set @sql = ''
set @sql += 'select top 0 * into '+@temptabname1+' from ' + @from1 + ';' + char(13)
set @sql += 'select column_id, name, user_type_id from tempdb.sys.columns where object_id=object_id(''tempdb.dbo.'+@temptabname1+''') order by column_id'

 
insert @tabcols1
exec sp_executesql @sql
 

set @sql = ''
set @sql += 'select top 0 * into '+@temptabname1+' from ' + @from2 + ';' + char(13)
set @sql += 'select column_id, name, user_type_id from tempdb.sys.columns where object_id=object_id(''tempdb.dbo.'+@temptabname1+''') order by column_id'


insert @tabcols2
exec sp_executesql @sql
	
set @mask_column='mask'
while exists(select * from @tabcols1 where name = @mask_column) or exists(select * from @tabcols2 where name = @mask_column)
  set @mask_column += '_'

if @operation not in ('equal', 'e', 'not equal', 'n', 'left','l', 'right','r' )
begin
  raiserror ('allowed operations only: equal, e, not equal, n, left, l, right, r',16,1);
end
else
begin
	if @operation in ('equal', 'e')
		set @op_expr = 'sum(distinct '+@mask_column+') = 3'
	if @operation in ('not equal', 'n')
		set @op_expr = 'sum(distinct '+@mask_column+') <> 3'
	if @operation in ('left', 'l')
		set @op_expr = 'sum(distinct '+@mask_column+') = 1'
	if @operation in ('right', 'r')
		set @op_expr = 'sum(distinct '+@mask_column+') = 2'
end
 
select 
@colsintern1=
substring(
(select ',' + case when tid=34 then 'cast([' + name + '] as varbinary(max)) ' when tid=35 then 'cast([' + name + '] as varchar(max)) ' when tid in(99,241) then 'cast([' + name + '] as nvarchar(max)) ' else '['+name+']' end + ' as ['+ name+']'
from @tabcols1
where name not in (select name from @exclude_cols_tab)
order by cid for xml path('')
),2,50000) 

select 
@colsintern2=
substring(
(select ',' + case when tid=34 then 'cast([' + name + '] as varbinary(max)) ' when tid=35 then 'cast([' + name + '] as varchar(max)) ' when tid in(99,241) then 'cast([' + name + '] as nvarchar(max)) ' else '['+name+']' end + ' as ['+ name+']'
from @tabcols2
where name not in (select name from @exclude_cols_tab)
order by cid for xml path('')
),2,50000) 


select 
@colsextern=
substring(
(select ',[' + name + ']' 
from @tabcols1
where name not in (select name from @exclude_cols_tab)
order by cid for xml path('')
),2,50000) 

--Output In Table?
declare @out_tab sysname 
declare @insert_clause      nvarchar(max)
declare @insert_clause_into nvarchar(max)
declare @order_by nvarchar(4000)

select  @out_tab = substring(value,1,len(value)-4) from @options_tab where value like '% OUT'

if @out_tab is null
  select  @out_tab = substring(value,1,len(value)-7) from @options_tab where value like '% OUTPUT'


if object_id(@out_tab) is null
  set @insert_clause_into = ' into ' + @out_tab 
else
  set @insert_clause = 'insert ' + @out_tab+'('+@colsextern+')'

set @insert_clause     =isnull(@insert_clause,'')
set @insert_clause_into=isnull(@insert_clause_into,'')


select @order_by = value from @options_tab where value like 'order by%' or value like 'order  by%'
select @order_by = isnull(@order_by,'order by 2')+','+@mask_column


if @options like '%use_tempdb%'
begin 

set @sql = '
select 1 '+@mask_column+','+@colsintern1+' 
into '+@temptabname2+'
from '+@from1+';

insert '+@temptabname2+'  
select 2 '+@mask_column+', 
'+@colsintern2+'
from '+@from2+';

'+@insert_clause+' 
select sum(distinct '+@mask_column+') '+@mask_column+',
'+@colsextern+'
'+@insert_clause_into+'
from '+@temptabname2+' group by 
'+@colsextern+'
having ' + @op_expr + '
'+@order_by 

end
else
begin	

set @sql = '
'+@insert_clause+'
select sum(distinct '+@mask_column+') '+@mask_column+','+@colsextern+'
'+@insert_clause_into+'
from
(
select 1 '+@mask_column+',
'+@colsintern1+'
from '+@from1+'
union all
select 2 '+@mask_column+', 
'+@colsintern2+'
from '+@from2+'
)TBTRCXKOZGFSE ' + 
'group by '+@colsextern+'
having ' + @op_expr + '
'+@order_by 
end

if @options like '%noexec%'
 select @sql
else
 exec sp_executesql @sql
 
END
1 июл 16, 15:56    [19360351]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить