| Author |
Topic |
|
hrishi_des
Yak Posting Veteran
76 Posts |
 Posted - 2008-01-28 : 04:44:53
|
| Hi,I want to bcp data file generated by other system into a table maintened by our system. While generating the bcp data file they leave data value for id column empty (because they are not aware of the value in our system).I want to specify a constant value for id column during bcp. Is there any way i can specify a constant value in bulk insert command? Can i specify it in format file?Although i can read the bcp file and do transformation in front end (C#) it is time consuming. I can bcp data and then fire a update statement to update id column, this is also found to be time consuming task.Any help would be appriciated.Many thanks,Rishi When solution is simple, God is answering…. |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-01-28 : 05:03:31
|
| You could bcp all the columns and set the value of the id column as a default value.That shouldn't be slow.You could have the bcp called from a stored procedure and parameterise the value of ID column incase you want to change ID values every run.I can't think of a way to have that done just by bcp though.Theres no such switch that bcp has for your purpose. |
 |
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2008-01-28 : 05:36:22
|
| Hi,What about concurrency? If two transactions are run at same time?Regards,RishiWhen solution is simple, God is answering…. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-01-28 : 06:10:11
|
| you mean 2 transacations with different id values ? |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2008-01-28 : 06:14:24
|
| yea, also some other transaction (other than BCP), may try to insert record in that table with some other default value.When solution is simple, God is answering…. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-01-28 : 06:28:31
|
| you could use TABLOCK hint to avoid that |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2008-01-29 : 03:35:52
|
| Hi,I did have a look at those links, i am afraid that won't help. And secondly specifying TABLOCK (I had though about it), any other inserts in other transaction would have to wait, so it won't help either.Regards,RishiWhen solution is simple, God is answering…. |
|
|
|
kashhar
Starting Member
1 Post |
Posted - 2008-04-14 : 04:00:36
|
| Hi Rishi,I am facing the same problem. Could you let me know if you found a solution for this?Thanks! Kalpa |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-14 : 04:10:10
|
What about using QUERYOUT?What about making a VIEW (with emtpy id col) and export that?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
dev2dev
Starting Member
48 Posts |
Posted - 2008-04-14 : 04:27:27
|
quote:
Originally posted by hrishi_desI want to bcp data file generated by other system into a table maintened by our system. While generating the bcp data file they leave data value for id column empty (because they are not aware of the value in our system).I want to specify a constant value for id column during bcp. Is there any way i can specify a constant value in bulk insert command? Can i specify it in format file?
I suggest to use a staging table, this staging table will have the same structure as your destination table. Create a stored procedure, which accepts a parameter (to hold constant which you going to update dest. tablebcp or bulk insert the file into staging tableinsert into destination table using a INSERT INTO DEST_TABLE SELECT ... SQLHope this helpsbtw: quote:
And secondly specifying TABLOCK (I had though about it), any other inserts in other transaction would have to wait, so it won't help either.
I don't think TABLOCK could trouble you lot.Can you give us more insight on the process (how systems interacts/uses this file and db, how many concurrent users tries to run/load) |
|
|
|
|
Specifying constant value for column in bcp.