Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP native format data issue on import Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 7:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:03 AM
Points: 69, Visits: 331
I am using bcp in native format to export a products table out
which has an integer primary key (NOTE it is not an IDENTITY) but with gaps in the id.

i.e i have 5878 rows and the product id at row 5878 stands at 6008


however when importing the data back on a different sql server... the product id is being
reset totally sequentially getting rid of the gaps in the id that were present
in the key so the max product id at row 5878 is now 5878...
and all product id's now match all the row numbers

the total rowcount is 5878 in both cases...

I cannot understand it as i am doing a native export and import
which is the defacto way of transferring sql server to sql server

i've checked it out a few times and it is still doing it

sql as below

select max(productsid) from products_target 5878
select max(productsid) from products_source 6008

select count(*) from products_target 5878
select count(*) from products_source 5878

any ideas ?
Post #1411717
Posted Monday, January 28, 2013 9:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:03 AM
Points: 69, Visits: 331
Hi All,

I've checked the target tables again and apologise but they do have identity fields...and that is what is causing the problem..the "bcp in" just generates sequential keys for the id column ignoring the source data for the id. I thought in native format the data being binary the bcp would ram home the data for this column. Any one know of setting the CHECK_IDENT_INSERT on type of flag (if there is one) for bcp ...

rgds

robin
Post #1412484
Posted Monday, January 28, 2013 9:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:02 PM
Points: 12,890, Visits: 31,853
run bcp /? from the command prompt for all the possible flags;
i think the one you want is the -E flag:

-E keep identity values


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1412488
Posted Monday, January 28, 2013 9:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:03 AM
Points: 69, Visits: 331
thanks Lowell..it checks out
Post #1412507
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse