Bulk export/import of the table with nullable and computed fields

I have a program that is doing significant refresh of data in tables with nullable and computed (based on values of other fields of the sdame table) fields at least 2 times a week.

I found that it maybe the most efficient way to do this is to dump unchanged rows to flat files of the native format, to add changed rows to the same files,to truncate tables,to drop indices,to bulk insert from flat files and to restore indices.

Can somebody explain to me what value has to be assigned in the native format of the bulk export to the prefix of nullable field if it is null?

What will happened when you bulk insert without format file to the table with computed fields?

You would use a fmt file to define what should be imported. Here is an old post that should get you started.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/6a02e9de79a21a81/50045cbec46f5a8f

|||

Dear oj,

Thank you for your attempt to help, but it is completely unrelated:

1.Guys are completely confused native data file format with char, I want to do native.

2.They are trying to skip identity field which I don't have.

3.They don't trying to create programmatically native format which I am trying to do.

4.I am talking about computed fields(from other fields of the same table.

5.They arte trying to skip identity field with fmt file which by default will be skipped anyway without fmt.

6.Maybe, this isn't relevant because bcp is the same, but I am using sql2k5 vs them sql2k

|||

I found solution on question 1:

In order to insert null to the nullable field you have to put -1 value to the prefix of that field in the native format datafile.

Regarding question 2 I found that without fmt file if you simply dumped content of the table in native format with computed fields

it will be succesfully bulk inserted to the table.

If you changed value of computed field it got ignored.

I am not sure if this has anything to do with caching of execution plans, but I truncated table and changed name of datafile and result the same.