-
Newbie: SQL 2008 Bulk Import with Idendity
I have a 2008 SQL database (PCINFO) and a table (SCRIPTS). The table was created with the following:
USE PCINFO
CREATE TABLE SCRIPTS
(
ID INT NOT NULL PRIMARY KEY IDENTITY,
RDate datetime NOT NULL,
UserID VARCHAR(25) NOT NULL,
Computer varchar(20) NOT NULL,
Domain varchar(30) NOT NULL,
FreeSpace varchar(100) NOT NULL,
RAM varchar(20) NOT NULL,
);
GO
I have ALOT of files I want to import into the database. I am trying to use BULK import. My data files do not contain the FIELD SCRIPTS.ID, which is a autogenerating KEY filed in the table. A SAMPLE of my data file is:
7/8/2008, User:besadmin, Computer:TDCSRV1, Domain:TDC, C:1748MB free (10.07%)D:70874MB free (51.02%), RAM:3071MB
7/8/2008, User:besadmin, Computer:TDCSRV1, Domain:TDC, C:1747MB free (10.06%)D:70875MB free (51.02%), RAM:3071MB
7/8/2008, User:besadmin, Computer:TDCSRV1, Domain:TDC, C:1745MB free (10.06%)D:70875MB free (51.02%), RAM:3071MB
7/10/2008, User:besadmin, Computer:TDCSRV1, Domain:TDC, C:1697MB free (9.78%)D:70545MB free (50.78%), RAM:3071MB
7/17/2008, User:besadmin, Computer:TDCSRV1, Domain:TDC, C:1554MB free (8.96%)D:69276MB free (49.87%), RAM:3071MB
I have created a FORMAT file to use with the BULK import, here it is:
10.0
6
1 SQLDATETIME 0 8 "" 2 RDate ""
2 SQLCHAR 2 25 "" 3 UserID SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 2 20 "" 4 Computer SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 30 "" 5 Domain SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 100 "" 6 FreeSpace SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 20 "" 7 RAM SQL_Latin1_General_CP1_CI_AS
And finally my BULK import command:
USE PCINFO
BULK
INSERT SCRIPTS
FROM 'c:\temp\besadmin.txt'
WITH(FORMATFILE='C:\temp\formatScripts.fmt');
GO
ISSUE: When i run this command I get and error:
Msg 4866, Level 16, State 7, Line 2
The bulk load failed. The column is too long in the data file for row 1, column 3. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
It seems to be an issue with the first COLUMN in the data file, a date field. Any assisance would be greatly apprieciated.
-
Re: Newbie: SQL 2008 Bulk Import with Idendity
Thank you so much for sharing this information