Search code examples
sqldatabaseazureazure-sql-databaseazure-sql-server

Easier way of Doing SQL Data Export - Azure

I have two tables Table A and B that is present in a Azure SQL Database. I have the same database clone running in my local, but I want to populate the data that is present in Azure by using SSMS Export Data option. While using that option I specify the source and destination and then choose the option of "Write a query to specify the data to transfer"

enter image description here

And then I add the query "Select * from A where Condition1" and select the destination table here: enter image description here

The issue is if I have 5 tables to export data from, I have to do this whole process 5 times, only difference is the queries and destination tables. Anyone has any idea how can I do this whole thing faster by some other means? I just need to copy data using some select statements with where clauses.

Solution

  • As per the Official Documentation

    When you select  Write a query to specify the data to transfer, you can only copy the results of one query to one destination table.

    So, you have to repeat the entire process for multiple times if you want to export data like that.

    You can use the following ways for importing and exporting data:

    • Use Transact-SQL statements.
    • Use BCP (Bulk Copy Program) from the command prompt.
    • If you want to design a custom data import, you can use SQL Server Integration Services.
    • Use Azure Data factory.
    • Use BACPAC file. Refer this material by accu web hosting to know about it. Rather than querying before exporting the data, instead you can delete the unwanted data in destination database after exporting using delete statement.

    REFERENCE:

    Import & export data from SQL Server & Azure SQL Database - SQL Server | Microsoft Docs