#1
  1. No Profile Picture
    got Rice?
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2004
    Posts
    532
    Rep Power
    15

    BCP Bulk Insert into specific columns


    I am trying to do a BULK insert using BCP and a XML format file.

    I have a file with 32 columns, and a table with 34 columns that i want to bulk insert

    i have read here:

    http://msdn.microsoft.com/en-us/library/ms179250.aspx

    to try and do the bulk insert. but it doesn't seem to insert the data into the correct columns.

    here is the SQL to create the table.

    SQL Code:
     
    CREATE TABLE [dbo].[prep_SomeTable](
    	[ID] [INT] IDENTITY(1,1) NOT NULL,
    	[Last_Update] [smalldatetime] NULL,
    	[ClientID] [VARCHAR](10) NOT NULL DEFAULT (''),
    	[pollSystem] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[vin] [VARCHAR](17) NOT NULL DEFAULT(''),
    	[YEAR] [VARCHAR](4) NOT NULL DEFAULT(''),
    	[make] [VARCHAR](50) NOT NULL DEFAULT(''),
    	[model] [VARCHAR](50) NOT NULL DEFAULT(''),
    	[TRIM] [VARCHAR](50) NOT NULL DEFAULT(''),
    	[mileage] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[factColor] [VARCHAR](100) NOT NULL DEFAULT(''),
    	[intFactColor] [VARCHAR](100) NOT NULL DEFAULT(''),
    	[doors] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[newused] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[stockno] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[cpo] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[certType] [VARCHAR](50) NOT NULL DEFAULT(''),
    	[certid] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[otherprice] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[webprice] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[invprice] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[msrp] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[mktClass] [VARCHAR](50) NOT NULL DEFAULT(''),
    	[options1] [VARCHAR](8000) NOT NULL DEFAULT(''),
    	[instock] [VARCHAR](50) NOT NULL DEFAULT(''),
    	[manfModelNum] [VARCHAR](50) NOT NULL DEFAULT(''),
    	[trans] [VARCHAR](100) NOT NULL DEFAULT(''),
    	[enginedesc] [VARCHAR](100) NOT NULL DEFAULT(''),
    	[drivetrain] [VARCHAR](100) NOT NULL DEFAULT(''),
    	[overviewtext] [VARCHAR](8000) NOT NULL DEFAULT(''),
    	[options2] [VARCHAR](8000) NOT NULL DEFAULT(''),
    	[chromestyleid] [VARCHAR](10) NOT NULL DEFAULT(''),
    	[photourls] [VARCHAR](8000) NOT NULL DEFAULT(''),
    	[photosupdated] [VARCHAR](10) NOT NULL DEFAULT(''),
    	CONSTRAINT [PK_prepSomeTable] PRIMARY KEY CLUSTERED 
    		(
    			[ID] ASC
    		)
    ) ON [PRIMARY]


    the format file:

    Code:
    <?xml version="1.0"?>
    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <RECORD>
        <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="16" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="17" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="18" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="19" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="20" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="21" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="22" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="23" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="24" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="25" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="26" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="27" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="28" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="29" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="30" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="31" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
        <FIELD ID="32" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      </RECORD>
      <ROW>
        <COLUMN SOURCE="1" NAME="ClientID" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="2" NAME="pollSystem" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="3" NAME="vin" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="4" NAME="year" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="5" NAME="make" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="6" NAME="model" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="7" NAME="trim" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="8" NAME="mileage" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="9" NAME="factColor" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="10" NAME="intFactColor" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="11" NAME="doors" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="12" NAME="newused" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="13" NAME="stockno" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="14" NAME="cpo" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="15" NAME="certType" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="16" NAME="certid" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="17" NAME="otherprice" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="18" NAME="invprice" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="19" NAME="webprice" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="20" NAME="msrp" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="21" NAME="mktClass" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="22" NAME="options1" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="23" NAME="instock" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="24" NAME="manfModelNum" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="25" NAME="trans" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="26" NAME="enginedesc" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="27" NAME="drivetrain" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="28" NAME="overviewtext" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="29" NAME="options2" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="30" NAME="chromestyleid" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="31" NAME="photourls" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="32" NAME="photosupdated" xsi:type="SQLVARYCHAR"/>
      </ROW>
    </BCPFORMAT>
    and the SQL:

    SQL Code:
     
    BULK INSERT prep_SomeTable
    FROM '\\dev\d_drive\ftproot\7415_519.dat' 
    WITH ( FIRSTROW = 2, FORMATFILE = '\\dev\d_drive\format_files\test.fmt' )


    error message:

    Msg 295, Level 16, State 3, Line 1
    Conversion failed when converting character string to smalldatetime data type.
    I am not sure why it is trying to insert into the Last_Update Column, which is the only smalldatetime field in the table. but in the format file, i do not have any data going into that column.

    it looks like everything is set correctly in the format file

    in the SQL Server Library Link, section "Using an XML Format File" 2nd example, it looks to be the same thing as I am?

    not sure what I am doing wrong?
  2. #2
  3. No Profile Picture
    got Rice?
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2004
    Posts
    532
    Rep Power
    15
    blah, found my own answer

    the name does not correspond with the table names, but just if you wanted to use it in a select statement (http://msdn.microsoft.com/en-us/library/ms189327.aspx), as to do this:

    SQL Code:
     
    INSERT INTO prep_someTable
      (ClientID,pollSystem,vin,YEAR,make,model,TRIM,mileage,factColor,intFactColor,doors,newused,stockno,c  po,certType,
    	certid,otherprice,invprice,webprice,msrp,mktClass,options1,instock,manfModelNum,trans,enginedesc,dri  vetrain,
    	overviewtext,options2,chromestyleid,photourls,photosupdated)
        SELECT ISNULL(ClientID,''),ISNULL(pollSystem,''),ISNULL(vin,''),ISNULL(YEAR,''),ISNULL(make,''),ISNULL(mode  l,''),ISNULL(TRIM,''),ISNULL(mileage,''),ISNULL(factColor,''),ISNULL(intFactColor,''),ISNULL(doors,'  '),ISNULL(newused,''),ISNULL(stockno,''),ISNULL(cpo,''),ISNULL(certType,''),
    		ISNULL(certid,''),ISNULL(otherprice,''),ISNULL(invprice,''),ISNULL(webprice,''),ISNULL(msrp,''),ISNU  LL(mktClass,''),ISNULL(options1,''),ISNULL(instock,''),ISNULL(manfModelNum,''),ISNULL(trans,''),ISNU  LL(enginedesc,''),ISNULL(drivetrain,''),
    		ISNULL(overviewtext,''),ISNULL(options2,''),ISNULL(chromestyleid,''),ISNULL(photourls,''),ISNULL(pho  tosupdated,'')
          FROM  OPENROWSET(BULK  '\\dev\d_drive\ftproot\7415_519.dat',
          FORMATFILE='\\dev\d_drive\format_files\test.fmt', FIRSTROW = 2
           )  AS t1;


    just in case this could help someone else.

    edit: the "as t1" identifier needs to exist in the statement or it will throw an error. of course, t1 can be whatever

    A correlation name must be specified for the bulk rowset in the from clause.

    Comments on this post

    • pabloj agrees : Thanks for posting the solution
    Last edited by jaeSun; May 21st, 2008 at 02:39 PM.

IMN logo majestic logo threadwatch logo seochat tools logo