dynamically copy the table from one db to another db

  • 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.

  • 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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply