ReCall (for Remi Gregoire and anyone want to help...) Impossible to Insert or Update with identity in table
-
Let's image...
database DBr (Database runTime) and DBa (DBa has same structure as DBr).
We want to transfer data from DBr to DBa using BCP (export) and BULK INSERT (for insert).
The process works fine until table with identity are copied and imported.....
Tr table in DBr and Ta in DBa (Ta and Tr same structure....remeber DBr and DBa are the same).
What happend:
- start BCP copy for Tr ....create the file tr.dat .
- Start BULK INSERT to import data into Ta from tr.dat file....
After BULK insert is finished, I check if everything is OK.....
(is not OK)The values in identity col for Ta is different from Tr, because identity incremented byself every time.
(think Tr with cols: ID (identity) and FRUIT and values are as: 1 and Bananas (only 1 row)
In Ta after BULK INSERT is for istance: 5 and Bananas.
If I delete Ta and make again Bulk insert, values are: 6 and Bananas.
If I delete Ta and make again Bulk insert, values are: 7 and Bananas.
and so on....
I tried to : SET IDENTITY_INSERT ta ON (I tried also OFF just to try)
the result is the same.....
My question is:
I would like to have values in Ta equal to Tr also for identity ID (as if identity didn't exist).
How to solve my problem ???????
-
That thread is not over, we requested you send us the code you were using so we could help and you didn't do it so we're still on hold on that thread.
-
Ah, that's better
Use the 'KEEPIDENTITY' option of BULKINSERT !!!!
* Noel -
There can never have too many details in a question
. -
That's right
* Noel -
Hi guys ...
there is anything to write as code...it is only a BULK insert statement....
BULK INSERT DBa.dbo.Ta FROM 'file.dat' with (arguments)....
and anything else....
Your suggestion about KEEPIDENTITY may be interesting....
I'll try it....
-
I tried using identity as arguments and everithing works fine
Thank
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply