Microsoft SQL Server forum - BCP fmt and Create Table

Author

BCP fmt and Create Table

danths@gmail.com

2005-05-25, 1:23 pm

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

Simon Hayes

2005-05-25, 1:23 pm

<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

Sponsored Links