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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ,
........
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_
[ACCOUNT_ID PIV] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_
[COMPANY_NAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_
[SUPPRESS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_
[ADDRESS1] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_
[CITY] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_
[STATE] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_
[ZIP] [nvarchar] (7) COLLATE SQL_Latin1_General_CP1_CI_
........
If the fields don't match then use aneeshattingal's solution.
BULK INSERT Dataload_SC_GroupedRating
FROM 'YourSourceFile.CSV'
WITH
(
FIELDTERMINATOR=','
)