Posted by Simon Hayes on 05/25/05 21:10

<danths@gmail.com> wrote in message
news:1117041460.483784.193120@g47g2000cwa.googlegroups.com...
> Hello,
> I am being sent a fmt file and a data file without headers which I
> would like to import into my sql 2000 server. However the data files
> have couple of 100's of columns, how could I avoid manually creating a
> table. Can I create a table using a fmt file thru bcp/bulk insert?
>
> Thanks
> sd
>

I don't believe there's an automatic way to create a table using only a
format file, but why not just ask the person sending the data to include a
CREATE TABLE script?

If that's not possible (which would be strange), one solution would be to
write a script to parse format files and produce the corresponding CREATE
TABLE statement - if you'll need to do this often, and if the data will
always be in different formats, it could be worth it.

Alternatively, for a one-off task, it might be easier to use DTS via the
Import and Export Data Wizard. That will give you the option to create the
destination table, although depending on what's in the format file, you
might have to spend a lot of time rearranging column order and so on.

But getting the table script is probably the best option, otherwise there's
no way to know what the exact data types are, what the key columns are,
which columns are NULL/NOT NULL, if there are any CHECK constraints etc.

Simon

[Back to original message]