3

is there a way how I can only migrate/export table names with the PKs but no other relationships like FKs? So there is no dependency between the tables. And then import them to another database?

I would like to perform this between SQL Server 2008 R2 and Azure SQL.

  • do you mean script out tables with definition and PK only but no FK? Or you want to create a .dacpac file? – SqlWorldWide May 2 '17 at 12:12
  • I suppose, the dacpac contains entire schema, correct? I would only like to script out tables and their PKs but no FK or whatever relationships between the tables that might represent some dependency between the tables which would prevent me to copy data into the tables in random order. – JS_Diver May 2 '17 at 12:20
  • I will write up an answer. I can think of 3 ways. – SqlWorldWide May 2 '17 at 12:23
  • I can add up a little more to my particular scenario: I have a SQL Server 2008 R2 which I am trying to replicate to Azure SQL via Azure Data Factory's Copy Activity but since the source db is approx. 400 tables, I have approx. 400 input and 400 output data sets that exceed some quota and therefore I need to split it into more pipelines - meaning - I expect to get some errors on the destination Azure SQL due to tables being dependent on each other through FKs etc. – JS_Diver May 2 '17 at 12:29
1

3 ways you can do this.

  1. Using SQL Azure Migration Wizard which you can download from here. One catch here as this is maintained by community, you might not have the most recent schema to compare. Using this tool you can choose to deselect foreign keys and other items. You are in this page click advanced.enter image description here

Then you can deselect foreign keys.

enter image description here

  1. In my opinion this the best solution for you as this will always have latest schema but I doubt if SQL 2008 R2 can create a solution with new SSDT. General idea is once you create a solution you can delete/modify objects (FK in this case) and rebuild the project. Once you clear up FK you can deploy.

  2. Create an empty database. Script out tables without FK and then run the script against empty database in cloud. In SSMS right click the database, go to task-->generate script...-->set scripting options. Then set foreign key to false.enter image description here

2

You can try any of these methods given in How to copy tables from one database to another in SQL Server by Ahmad Yaseen:

1) Using SELECT INTO Query the SELECT INTO SQL statement. This statement will create the tables in the destination database first, then it will copy the data to these tables. If you manage to copy the database objects such as the indexes and constraints, you need to generate script for it individually, after that you need to apply the scripts to the destination database.

2) Using SQL Server Export / Import wizard

Another method that can be used to copy tables from the source database to the destination one is the SQL Server Export and Import wizard, which is available in SQL Server Management Studio. You have the choice to export from the source database or import from the destination one in order to transfer the data.

3) Using Generate Scripts

SQL Server provides another way to generate script for the SQL Server databases with its objects and data. This script can be used to copy the tables’ schema and data from the source database to the destination one in our case.

4)Using the ApexSQL Diff and ApexSQL Data Diff combination

ApexSQL Diff is a useful SQL tool that can be used to find the differences between the databases from schema side and generate synchronization script to create these tables in the destination database in the correct order.

ApexSQL Data Diff also is another SQL tool that can be used to find the differences between the databases from data side and generate synchronization script to insert the data in the destination database tables, taking into consideration the IDENTITY columns.

  • Does all these options work in Azure? – SqlWorldWide May 3 '17 at 17:19
  • @SqlWorldWide, Not pretty much sure about it..but It should work – l.lijith May 3 '17 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.