How to do column mapping for Bulk Insert.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to do column mapping for Bulk Insert.

Author

Topic

gunasilanm
Starting Member

8 Posts

Posted - 2011-08-18 : 04:22:39

Hi Gurus!!!

I've did a BULK INSERT query using java and the code is working fine. I managed to import data from csv file to sql server. I have a issue here where i need to mapp the column with value in csv file.
For an example :

DB (Emp_Table)
NAME, ID, Type, Contact, Address

CSV File
TESTEMP|10177|0102253698|White House,New York

If you see above i have missed 'Type' which i will update the field later.
Java Code


public static void main(String[] args) {
       Connection conn;
       Statement stmt;
       ResultSet rs;
       String strPath ="C:/Java/load.txt";
       String strRowTerminator = "\\n"; 
       String sql = null;
       
       try
        {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:jtds:sqlserver://192.168.50.6:1433/pcms_tm_test","sa","password");
            System.out.println("connected");
            sql = "BULK INSERT Cust_Info_Test_Loader FROM '" + strPath + "' WITH (FIELDTERMINATOR = '|',ROWTERMINATOR = '" + strRowTerminator + "')";
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
            stmt.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        
    }

How do i do the column mapping in bulk insert so it only insert in Name,ID,Contact and Address. Please help. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 04:25:48

you need to use format file i guess

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gunasilanm
Starting Member

8 Posts

Posted - 2011-08-18 : 23:52:51

HI Visakh,
Thanks for the link. It helps a lot. But i have an issue here. How do I set the default value using format file. For an example(please refer the scenario above), If my Employee_id starts with 1 then type = "Permanent" else ="Contract". I need to set the TYPE field with default value. Please help. Thanks. :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-19 : 00:24:28

Bulk insert into a staging table, update columns according to your business needs and then transfer the rows from the staging table to the production table.

N 56°04'39.26"
E 12°55'05.63"

gunasilanm
Starting Member

8 Posts

Posted - 2011-08-19 : 03:26:58

Hi SwePeso,
Yeah that's my workaround but I'm trying to do it from the insert statement it self. I'm trying to minimize the access to staging table due to performance issue. If i manage to set default value in BULK INSERT then no need to update the staging table again.

gunasilanm
Starting Member

8 Posts

Posted - 2011-08-19 : 03:37:19

My format file.


  <?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="50" /> 
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="20" /> 
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="20" /> 
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="200" /> 
  </RECORD>
- <ROW>
  <COLUMN SOURCE="1" NAME="Outlet_ID" xsi:type="SQLNVARCHAR" /> 
  <COLUMN SOURCE="2" NAME="Account_No" xsi:type="SQLNVARCHAR" /> 
  <COLUMN SOURCE="3" NAME="Login_ID" xsi:type="SQLNVARCHAR" /> 
  <COLUMN SOURCE="4" NAME="Customer_Name" xsi:type="SQLNVARCHAR" /> 
  </ROW>
  </BCPFORMAT>

Java Code


 public static void main(String[] args) {
       Connection conn;
       Statement stmt;
       ResultSet rs;
       String strPath ="C:/Java/load.txt";
       String format = "C:/Java/major.fmt";
       String format1 = "C:/Java/myTestSkipCol2.Xml";
       String strRowTerminator = "\\n"; 
       String sql = null;
       
           try
        {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:jtds:sqlserver://192.168.50.6:1433/pcms_tm_test","sa","password");
            System.out.println("connected");
            sql = "BULK INSERT Cust_Info_Test_Loader FROM '" + strPath + "' WITH (FIELDTERMINATOR = '|',FORMATFILE = '" + format1 + "',ROWTERMINATOR = '" + strRowTerminator + "')";
            //sql = "BULK INSERT Cust_Info_Test_Loaders FROM '" + strPath + "' WITH (FORMATFILE = '" + format1 + "',FIELDTERMINATOR = '|') ";
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
            stmt.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        
    }

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources