It’s not often that I post something here, but I find myself returning to my blog to find tricky SQL statements. This time it’s about comparing data between companies.

Let’s say you have a large number of companies and you want to compare data in a specific table to see differences. This script will give you all columns except the timestamp column and display all data in the No. Series table by company.

 
declare @table nvarchar(30) = 'No_ Series'
declare @sql nvarchar(MAX), @CompanyName varchar(30)
declare @columns nvarchar(max)=''
declare @col nvarchar(100) =''
declare @num_rows int =0
declare @dat_type nvarchar(30)
set @CompanyName = (select top(1) Name from Company)
declare cols cursor for
select column_name, data_type
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ''+@CompanyName+'$'+@table+''
open cols
fetch next from cols into @col, @dat_type
while(@@FETCH_STATUS = 0)
begin
set @num_rows +=1
if @dat_type = 'timestamp'
set @columns += 'null'
else 
set @columns += '['+@col+']'
fetch next from cols into @col, @dat_type
if @@FETCH_STATUS=0
set @columns += ','
end
close cols
deallocate cols
SELECT @sql = 'SELECT TOP(0) space(30) AS CompanyName, * INTO ##TempData FROM ['+ @CompanyName +'$'+@table+']'
exec (@sql)
DECLARE CompanyCursor CURSOR FORWARD_ONLY FOR
SELECT REPLACE([Name],'.','_')
FROM [Company]
OPEN CompanyCursor
FETCH NEXT FROM CompanyCursor INTO @CompanyName

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = '
INSERT INTO ##TempData SELECT ''' + @CompanyName + ''', ' + @columns + ' from ['+@CompanyName+'$'+@table+']' 
exec (@sql) 
FETCH NEXT FROM CompanyCursor INTO @CompanyName
END
CLOSE CompanyCursor
DEALLOCATE CompanyCursor
ALTER TABLE ##TempData DROP COLUMN timestamp
select * from ##TempData
DROP TABLE ##TempData