dynamically copy the table from one db to another db

• ravisamigo

  SSCertifiable

  Points: 7440

  More actions

  November 27, 2014 at 7:51 pm

  #301453

  HI,

  The requirement is as below.

  Need to scheduled to copy table data from one database to another database daily @7:00 AM by creating the table with date and time stamp.

  Example :

  Test1DB ---> Table1

  Test2DB -----> Table1_281120140700 in Day1 and Table2_291120140700 in Day2.....and so.

  SELECT * INTO Test2db.dbo.new_table_name FROM Test1db.dbo.old_table_name in this format ??

  Please advise me script and scheduled to run daily @ 7:00AM

  Thanks and Regards,

  Ravichandra.

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  November 28, 2014 at 6:59 am

  #1761431

  The easiest way to do that is with Dynamic SQL.

  Basically create a varchar variable which holds the SQL statement and build the statement up manually then call execute on the SQL

  DECLARE @sql as varchar(4000)

  SET @sql = 'SELECT * INTO MyTable_' + <whatever you need to concatenate here> + '' FROM MyTable)

  EXECUTE sp_executesql @sql