I'm importing tables from Access to SQL Server. The column ID in Access is auto-increment, but in SQL Server I must enter every time the ID. It's important to say that there're records in the imported tables and I want to alter the ID be IDENTITY.
I tried to change the ID to IDENTITY, but it's says that I must drop and recreate the tables. Is it possible to alter ID to IDENTITY after importing from Access?
1条答案
按热度按时间8zzbczxx1#
Ok, in SQL server, you need to set (in most cases) the column that was the PK column as the primery key, and THEN also set the column to be auto increment.
So, open the sql server table in the SSMS designer, and set the ID column as PK.
you also THEN want to set the "identity" settings to auto increment.
so, like this:
In this example, I do BOTH!
I select the ID column, set as PK, and THEN also setup the "autonumbering" system (it works like how auto number in Access does).
Note that AFTER you do the above, you need to re-link the table(s) from Access.
Edit: There is some major confusing in regards to this being NOT possible vs that of SSMS having to drop + re-create the table.
SSMS has a setting, and will do all the work for you.
That setting is this and by default one will get a message to this effect:
Tools->Options->Table and Database Designers
so, if you un-check that box (Prevent saving changes that require table re-creating?
Then you are 100% free from the table designer to turn on, or set, or un-set the identify column.
Obviously, the above screen cap gif shows me doing this task without a problem. Such operations does keep existing data. So, behind the scenes, yes, SQL server is dropping the table, re-creating the table, and moving the data for you. It is a operation with "some" risk, but I done this for years without a issue.
So, if one un-checks that box above, then one is rather free to add the identity column to the database in question.