locked
How to copy table to another database in same server in SQL Azure RRS feed

  • Question

  • Hi,

    I have databases host in SQL Azure. I want to copy tables from one database to another database in same server. I use script as follow,

    select  *
    into    [OTPPROD].[dbo].[Result]
    from    [OTPTEST].[dbo].[Result]

    But it gives error,

    Reference to database and/or server name in 'OTPPROD.dbo.Result' is not supported in this version of SQL Server.

    What is the problem? Thanks.


    York

    Thursday, October 11, 2012 10:59 PM

Answers

  • from one database to another database in same server.

    Hello York,

    Cross database operation = three-part qualifier as you used are not supported in SQL Azure. And your databases are not on the same server, they are somewhere in the cloud.

    If you have an on-premise SQL Server you could add both SQL Azure databases as linked server and copy the tables with a local executed T-SQL statement using a four-part qualifier.


    Olaf Helper

    Blog Xing

    • Proposed as answer by Carlos SardoMicrosoft employee Saturday, October 13, 2012 9:11 PM
    • Unproposed as answer by york Z Saturday, October 13, 2012 9:12 PM
    • Marked as answer by york Z Tuesday, October 16, 2012 2:55 AM
    Friday, October 12, 2012 5:00 AM
  • Hi York,

    In SQL Azure, cross-database query and command "select into" are both not supported.

    According to your scenario and Olaf mentioned, you can do the migration if you have on-premise sql server. On-premise sql server is the same as your sql azure server but not in the cloud, it means that if you have two backups of your sql azure databases in the local, you can migrate the table in local first using the t-sql as usual, and then sync up them to the Azure database use SQL Azure data sync.

    Copy table directly from different database in SQL Azure is not supported I'm afraid. But if copy table in one database, we can use another way but not "select into".


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by york Z Tuesday, October 16, 2012 2:55 AM
    Monday, October 15, 2012 6:27 AM

All replies

  • from one database to another database in same server.

    Hello York,

    Cross database operation = three-part qualifier as you used are not supported in SQL Azure. And your databases are not on the same server, they are somewhere in the cloud.

    If you have an on-premise SQL Server you could add both SQL Azure databases as linked server and copy the tables with a local executed T-SQL statement using a four-part qualifier.


    Olaf Helper

    Blog Xing

    • Proposed as answer by Carlos SardoMicrosoft employee Saturday, October 13, 2012 9:11 PM
    • Unproposed as answer by york Z Saturday, October 13, 2012 9:12 PM
    • Marked as answer by york Z Tuesday, October 16, 2012 2:55 AM
    Friday, October 12, 2012 5:00 AM
  • Can you give more detail on how to copy the tables with a local executed T-SQL statement using a four-part qualifier?


    York

    Saturday, October 13, 2012 9:13 PM
  • Hi York,

    In SQL Azure, cross-database query and command "select into" are both not supported.

    According to your scenario and Olaf mentioned, you can do the migration if you have on-premise sql server. On-premise sql server is the same as your sql azure server but not in the cloud, it means that if you have two backups of your sql azure databases in the local, you can migrate the table in local first using the t-sql as usual, and then sync up them to the Azure database use SQL Azure data sync.

    Copy table directly from different database in SQL Azure is not supported I'm afraid. But if copy table in one database, we can use another way but not "select into".


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by york Z Tuesday, October 16, 2012 2:55 AM
    Monday, October 15, 2012 6:27 AM