Management Studio Import with Identity

Question

Management Studio Import with Identity

Muzzamil Sajid 41

Hello all,

I am not very sure if this question is being posted in the right section. But since i am dealing with Azure SQL Database then i thought it should be fine.

My question is that, when i import a database from another one using the SSMS, it does not import the inbuilt settings of the columns. For example the identity columns. Even if i tick the "Enable identity insert" option. The columns are still imported with no identity setting on them.

Ronen Ariely 15,206 Reputation points

2021-12-16T06:07:31.9+00:00

when i import a database from another one using the SSMS

Hi,

Please clarify exactly what you mean by "import a database from another one" since it seems that you are using a wrong phrase.

you cannot import existing database as a database is an entity in specific instance. You can export data and schema from a database and import the data and the schema as a new database or to existing database. There are multiple ways to do this task.

Note! If you are using Bacpac file to export the data and schema and to import it as new database, then I highly recommend to check my lecture about the topic (there is a recording in Hebrew and a few in English indifferent event). This is the first one google found me now but this is the short version of 45 minutes and there is better version of 90 minutes with the same name: The Internals Of The External SQLPackage Tool-Inside A Bacpac File With Ronen Ariely (https://youtu.be/jakWrfzIZQE)

So, please explain the steps which you are using and what you mean by import the database

Accepted answer

1 additional answer

Your answer

Ronen Ariely 15,206 Reputation points

2021-12-16T06:07:31.9+00:00

when i import a database from another one using the SSMS

Hi,

Please clarify exactly what you mean by "import a database from another one" since it seems that you are using a wrong phrase.

you cannot import existing database as a database is an entity in specific instance. You can export data and schema from a database and import the data and the schema as a new database or to existing database. There are multiple ways to do this task.

Note! If you are using Bacpac file to export the data and schema and to import it as new database, then I highly recommend to check my lecture about the topic (there is a recording in Hebrew and a few in English indifferent event). This is the first one google found me now but this is the short version of 45 minutes and there is better version of 90 minutes with the same name: The Internals Of The External SQLPackage Tool-Inside A Bacpac File With Ronen Ariely (https://youtu.be/jakWrfzIZQE)

So, please explain the steps which you are using and what you mean by import the database

Answer 1

Olaf Helper 47,436

it does not import the inbuilt settings of the columns. For example the identity columns

Then I guess you are using the Import/Export Wizard of SSMS and that's a "nice" tool to copy some data over., but that's all. To migrate a complete database as 1:1 copy it's really the wrong tool.
Better use Microsoft® Data Migration Assistant v5.5

Answer 2

AmeliaGu-MSFT 14,006 Microsoft External Staff

Hi MuzzamilSajid-6173,
In addition, please also try to click EDIT SQL button in the Column Mappings windows, and then add IDENTITY property in the SQL code.
Foe example:

Best Regards,
Amelia

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Muzzamil Sajid 41 Reputation points

2021-12-16T08:57:10.837+00:00

Well this is what I actually do. But it becomes a pain when you have a lot of tables. at the moment my database has almost 50 tables. so going into each query and changing one by one is difficult, not impossible. But what if you have 100's of tables? Can't this default query be changed to have the "Identity" part in it by default?
Tom Phillips 17,771 Reputation points

2021-12-16T17:40:54.96+00:00

As said, the import/export wizard is for moving DATA, not tables. It is not designed to script a table with all its options, indexes, etc.

If you need to copy a table, you should either use SSDT, Data Migration Assistant, to script and create the table first and then use the import/export wizard to move the data into the newly created table.

Share via

Management Studio Import with Identity

1 additional answer

Your answer