prenos podataka izmedju baza

declare @s varchar(max),
@ss varchar(max),
@k varchar(max),
@kk varchar(max),
@t char (80),
@nesto char (80),
@tsql varchar(max),
@db_from char (80),
@db_to char (80)

set @db_from = 'SD'
set @db_to = 'demo'

declare xPoz cursor local fast_forward for
select name from sysobjects where name like 'tHE%' and xtype = 'U'
open xPoz
fetch from xPoz into @nesto
while @@fetch_status = 0
begin
set @s = ''
set @ss = ''
set @k = ''
set @kk = ''
set @t = rtrim(@nesto)

SELECT
@k = @k + 'g.' + column_name + '=t.' + column_name + ' and ',
@kk = @kk + 't.' + column_name + ' is null and '
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND
KU.table_name=@t
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION

if len(@k) >4
begin
set @k = LEFT(@k, len(@k)-4)
end

if len(@kk) >4
begin
set @kk = LEFT(@kk, len(@kk)-4)
end

if exists (
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND
KU.table_name=@t
)
begin
--select @s = @s + ',' + name, @ss = @ss + ',g.'+ name
--from syscolumns
--where id in (select id from sysobjects where name = @t) and name <> 'anQid'
--order by colorder

select @s = @s + ',' + name, @ss = @ss + ',g.'+ name
from syscolumns
where id in (select id from sysobjects where name = @t)
and COLUMNPROPERTY(object_id(@t), name, 'IsIdentity')=0
and COLUMNPROPERTY(object_id(@t), name, 'IsComputed')=0
order by colorder

if exists(select * from demo..sysobjects where name = rtrim(@t))
begin
set @tsql = 'alter table ' + rtrim(@db_to) + '.dbo.' +rtrim(@t) + ' nocheck constraint all' + char (13) +
'insert into ' + rtrim(@db_to) + '.dbo.' + rtrim(@t) + ' (' + right(@s, len(@s)-1) + ')'+ char(13) +
'select ' + right(@ss, len(@ss)-1) + char(13) +
'from '+ rtrim(@db_from) +'.dbo.' + rtrim(@t) + ' g' + char(13) +
'left join '+ rtrim(@db_to) +'.dbo.' + rtrim(@t) + ' t on ' + @k + char(13) +
'where ' + @kk + char(13) + char(13)

print(@tsql)
end
end

fetch from xPoz into @nesto
end
close xPoz
deallocate xPoz