Import / Export / Copy Issues MSSQL IDENTITY_INSERT
Using MS SQL Server Management Studio 2008.
I have a db (say ip 10.16.17.10 and called db1 ) and the second one (for example ip 10.16.17.25 called db2 ).
I am trying to copy one table (and its contents) from db1 to db2 .
I have a database on both (but empty in db2).
The problem is how I copy / export / import, no matter what options I set in MS SQL Server Management Studio 2008 , when I click "table" -> "Design" (in db2 ), it ALWAYS says " Speech Identification : NO "even if db1 table is included.
From db1 I go to 'Tasks' → 'export' → 'source / db' and 'destination / db' → 'Edit Mapping' → 'Enable identity Insert' and click on it.
But there is no joy. ALWAYS exported without it.
I am trying a similar thing from IMPORT on db2 . A similar thing if I use COPY.
I have read MANY STACKOVERFLOW articles about this, they all suggest to set IDENTITY_INSERT to ON, but when I work below:
SET IDENTITY_INSERT [dbo].[mytable] ON
The table does not exist yet or has already been copied WITHOUT setting up authentication, so see the error:
does not have the identity property. Cannot perform SET operation.
I tried to set it as a property (by database properties) for db2 , but when I copy / import / export it never works.
Thanks for any help here, as many StackOverflow articles so far all seem to have an easier time than I did.
I plan to do this for 50 or so more tables in this database, so I hope to find a way that does not involve running scripts for each table.
thank
source to share
The process of using the Export Data Wizard to copy data from one table to another does NOT replicate all aspects of the schema (such as identity and auto-increment). If you want to replicate the schema, the script put your table in the create statement, change the name to db2 and create it. You will then be able to run the Export / Import wizard with the insert ID option enabled and insert into your new table, which replicates the schema of the old table.
source to share
According to the error message I think your table does not have a column IDENTITY
. Make sure you [dbo].[mytable]
have it IDENTITY column
before proceeding SET IDENTITY_INSERT
.
SET IDENTITY_INSERT [dbo].[mytable] ON
DEMO1 (Attempt to set ID ON when there is NO ID column )
--Error
'Table 'T' does not have the identity property. Cannot perform SET operation.: SET IDENTITY_INSERT T ON'
DEMO2 (Attempt to set ID ON if is ID column )
--No Errors
source to share
Follow the next steps:
From db1 I go to "Tasks" → 'export' → 'source / db' and 'destination / db' → 'Edit Mapping' → 'Enable identity Insert' and Edit SQL → You can view the structure of queries in the table.
In a request, for example. ID int NOT NULL, do next step ID int NOT NULL IDENTITY (1,1) Then continue.
I bet it will work.
source to share
Completed sorting using MS SQL Management Studio.
Thanks to @kevin for help with importing data and exporting data. Schemas are NOT migrated, however they are the best means of migrating data after the schema is complete.
Found the best way for MAT to import / export db table schemas using below (Saved SQL creates scripts for the file):
Tasks-> Generate Scripts-> All Tables to File-> With ID
Ran 200kb SQL file on db2 for schema.
Then did the Import data from db1 to db2 .
Done, all Identity_Inserts are saved.
thanks for the help
source to share