Link to home
Start Free TrialLog in
Avatar of LIULIHUA
LIULIHUA

asked on

Save a table formate as .fmt file

Hi experts,

Here is the case, I have few tables in the SQL database instance, I am going to load text files to the table using Bulk insert task. The problem is I don't have .fmt file created for each of the table's formate. How can I do that?

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nmcdermaid
nmcdermaid

If the fields in the source file match the fields in the destination table, in number, order and data type, then you don't need a format file:


BULK INSERT Dataload_SC_GroupedRating
FROM 'YourSourceFile.CSV'
WITH
(
FIELDTERMINATOR=','
)
Avatar of LIULIHUA

ASKER

a. the fields in the source file dosen't match all the fields in the destination table
b. the delimiter of fileds is using "~" instead of " or '.
c. some table has more then 30 fileds. Take one of example as following:
d. This is the first time I use DTS to load data, would you please give the details of how to implement Bulk insert task. I appreciate!



CREATE TABLE [ACCOUNT] (
      [ACCOUNT_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ACCOUNT_ID PIV] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [COMPANY_NAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [SUPPRESS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ADDRESS1] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CITY] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [STATE] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ZIP] [nvarchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      ........
If the fields don't match then use aneeshattingal's solution.