May 19, 2015 at 2:52 am
declare @sql1 nvarchar(4000)
declare @sql2 nvarchar(4000)
declare @tblnm varchar(60)
declare @x int
declare @y int
Set @x = 1
Set @y = 0
set @sql1 = N'
select @y = COUNT(*) from
(SELECT table_name FROM information_schema.tables
where table_catalog = ''DONKEY'' and TABLE_NAME like ''TBL%'' or TABLE_NAME like ''PRM%'')cnt'
exec sp_executesql @sql1, N'@y int out', @y out
--select @x x, @y y
while (@x <= @y)
begin
set @sql2 =
'SELECT @tblnm = table_name FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY table_name) AS rownumber,table_name
FROM (SELECT table_name FROM information_schema.tables
where table_catalog = ''DONKEY'' and TABLE_NAME like ''TBL%'' or TABLE_NAME like ''PRM%'') as tbls
) AS foo
WHERE rownumber = @x'
--exec sp_executesql @sql2, N'@tblnm varchar(100) out', @tblnm out
select *
into GORILLA.dbo.(@sql2) --going to gorilla db
from (execute (@sql2)) --from donkey DB
end
The scenario: We would like to automate this one. Running every midnight to make a copy/backup to another server. Here's the thing, we would like to copy the new tables if there's an existing new one. We don't want to purchase a different software btw.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply