Copying tables from DB1 to DB2 by using query which is kinda complicated...

• Ken Tot

  Old Hand

  Points: 375

  More actions

  May 19, 2015 at 2:52 am

  #317512

  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.