SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 identity specification		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

m.yazdian
Starting Member

3 Posts
Posted - 08/04/2012 :  04:55:46  Show Profile  Reply with Quote
When we import or export a table from sqlserver database to another table in sqlserver database, the "identity specification" switch to NO from YES.
therefore we try to switch back from NO to YES but a message appears in sqlserver, indicating that:
"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-create or enabled the option Prevent saving changes that require the table to be re-create."

So what should I do to have identity specification to remain YES or I be able to switch it from NO to YES.

Thank you.

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/04/2012 :  05:20:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
HOW are you importing the data?

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

m.yazdian
Starting Member

3 Posts
Posted - 08/04/2012 :  06:59:08  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

HOW are you importing the data?

N 56°04'39.26"
E 12°55'05.63"


Database / All task / Import data / select database source / select database destination / select copy data from one or more tables or view
Go to Top of Page

chadmat
The Chadinator

USA
1957 Posts
Posted - 08/04/2012 :  13:37:40  Show Profile  Visit chadmat's Homepage  Reply with Quote
Create the table first with Identity, and then import into that table with Identity Insert ON.

-Chad
Go to Top of Page

m.yazdian
Starting Member

3 Posts
Posted - 08/05/2012 :  00:38:07  Show Profile  Reply with Quote
quote:
Originally posted by chadmat

Create the table first with Identity, and then import into that table with Identity Insert ON.

-Chad


Hi chadmat,
Thank you very much for your answer.
I should select checkbox (Identity Insert ON) before import table from the source database.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/05/2012 :  04:43:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can ONLY do that if the target table already has an identity column.
The checkbox then uses the column value in the file as identity value, instead if the autogenerated value from the table.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000