Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-108

Best Practices for MS SQL Server IMPORT/EXPORT

    Details

    • Type: Explanation
    • Status: Obsolete
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: EXAloader
    • Labels:
      None
    • Solution:
      Hide

      Note: This solution is no longer maintained. For the latest information, please refer to our documentation:

      https://community.exasol.com/t5/connect-with-exasol/best-practices-for-import-export-with-sql-server/ta-p/215

      -------------------------------------------------------------------------------------------------------------------------------------------------

      Background

       
      The following examples show how one can import data into Exasol from MS SQL Server and export data from Exasol into MS SQL Server.

      Explanation

      Prerequisites:

      You need to install the "Microsoft JDBC driver for SQL Server" driver first: See See also SOL-178 for that.

      At the time of writing, the driver can be downloaded from the microsoft download portal

      From version 6.1 onwards, the system privileges for IMPORT/EXPORT exist and need to be assigned to a user (or one of its roles), e.g.:

      grant import,export to public;
      

      Basic syntax:

      Using Microsoft's JDBC driver for SQL Server, one could import/export data using the following commands:

      IMPORT INTO table1 
      FROM JDBC DRIVER='MSSQL' 
      AT 'jdbc:sqlserver://dbserver;databaseName=testdb' 
      USER 'user1' IDENTIFIED BY 'user1pw' TABLE table2;
      
      EXPORT table1 INTO JDBC DRIVER='MSSQL' 
      AT 'jdbc:sqlserver://dbserver;databaseName=testdb' 
      USER 'user1' IDENTIFIED BY 'user1pw' TABLE table2;
      

      A connection could also be created and used:

      CREATE CONNECTION conn_mssql 
      TO 'jdbc:sqlserver://dbserver;databaseName=testdb' 
      USER 'user1' IDENTIFIED BY 'user1pw';
      
      IMPORT INTO table1 FROM JDBC DRIVER='MSSQL' 
      AT conn_mssql TABLE table2;
      EXPORT table1 
      INTO JDBC DRIVER='MSSQL' AT conn_mssql TABLE table2;
      

      Alternate Driver:

      For performance reasons, it may be preferable to use the jTDS JDBC driver for SQL Server.

      Beware: If you do single loads with more than 4,294,967,295 rows, you should use the MSSQL native JDBC driver due to a bug in JTDS (note that the number 4,294,967,295 is the maximum value for a 32-bit unsigned integer). If you used JTDS for such big loads, only 4,294,967,295 rows would be imported, and the rest would be ignored without seeing an error.

      Using the JTDS driver, one could import/export data using the following commands:

      IMPORT INTO table1 FROM JDBC DRIVER='JTDSMSSQL' 
      AT 'jdbc:jtds:sqlserver://dbserver;databaseName=testdb' 
      USER 'user1' IDENTIFIED BY 'user1pw' TABLE table2;
      
      EXPORT table1 INTO JDBC DRIVER='JTDSMSSQL'
      AT 'jdbc:jtds:sqlserver://dbserver;databaseName=testdb' 
      USER 'user1' IDENTIFIED BY 'user1pw' TABLE table2;
      

      Again, a connection could be created and used:

      CREATE CONNECTION conn_jtdsmssql 
      TO 'jdbc:jtds:sqlserver://dbserver;databaseName=testdb' 
      USER 'user1' IDENTIFIED BY 'user1pw';
      
      IMPORT INTO table1 FROM JDBC DRIVER='JTDSMSSQL' 
      AT conn_jtdsmssql TABLE table2;
      
      EXPORT table1 INTO JDBC DRIVER='JTDSMSSQL' 
      AT conn_jtdsmssql TABLE table2;
      

      Please note, that using of the newly created connection requires either a system privilege USE ANY CONNECTION or the connection has to be explicitly granted to the user. Connections are automatically granted to the creator, including the ADMIN OPTION.

      Additional References

       

      Show
      Note: This solution is no longer maintained. For the latest information, please refer to our documentation: https://community.exasol.com/t5/connect-with-exasol/best-practices-for-import-export-with-sql-server/ta-p/215 ------------------------------------------------------------------------------------------------------------------------------------------------- Background   The following examples show how one can import data into Exasol from MS SQL Server and export data from Exasol into MS SQL Server. Explanation Prerequisites: You need to install the "Microsoft JDBC driver for SQL Server" driver first: See See also SOL-178 for that. At the time of writing, the driver can be downloaded from the microsoft download portal From version 6.1 onwards, the system privileges for IMPORT/EXPORT exist and need to be assigned to a user (or one of its roles), e.g.: grant import,export to public ; Basic syntax: Using Microsoft's JDBC driver for SQL Server , one could import/export data using the following commands: IMPORT INTO table1 FROM JDBC DRIVER= 'MSSQL' AT 'jdbc:sqlserver://dbserver;databaseName=testdb' USER 'user1' IDENTIFIED BY 'user1pw' TABLE table2; EXPORT table1 INTO JDBC DRIVER= 'MSSQL' AT 'jdbc:sqlserver://dbserver;databaseName=testdb' USER 'user1' IDENTIFIED BY 'user1pw' TABLE table2; A connection could also be created and used: CREATE CONNECTION conn_mssql TO 'jdbc:sqlserver://dbserver;databaseName=testdb' USER 'user1' IDENTIFIED BY 'user1pw' ; IMPORT INTO table1 FROM JDBC DRIVER= 'MSSQL' AT conn_mssql TABLE table2; EXPORT table1 INTO JDBC DRIVER= 'MSSQL' AT conn_mssql TABLE table2; Alternate Driver: For performance reasons, it may be preferable to use the jTDS JDBC driver for SQL Server . Beware : If you do single loads with more than 4,294,967,295 rows, you should use the MSSQL native JDBC driver due to a bug in JTDS (note that the number 4,294,967,295 is the maximum value for a 32-bit unsigned integer). If you used JTDS for such big loads, only 4,294,967,295 rows would be imported, and the rest would be ignored without seeing an error. Using the JTDS driver, one could import/export data using the following commands: IMPORT INTO table1 FROM JDBC DRIVER= 'JTDSMSSQL' AT 'jdbc:jtds:sqlserver://dbserver;databaseName=testdb' USER 'user1' IDENTIFIED BY 'user1pw' TABLE table2; EXPORT table1 INTO JDBC DRIVER= 'JTDSMSSQL' AT 'jdbc:jtds:sqlserver://dbserver;databaseName=testdb' USER 'user1' IDENTIFIED BY 'user1pw' TABLE table2; Again, a connection could be created and used: CREATE CONNECTION conn_jtdsmssql TO 'jdbc:jtds:sqlserver://dbserver;databaseName=testdb' USER 'user1' IDENTIFIED BY 'user1pw' ; IMPORT INTO table1 FROM JDBC DRIVER= 'JTDSMSSQL' AT conn_jtdsmssql TABLE table2; EXPORT table1 INTO JDBC DRIVER= 'JTDSMSSQL' AT conn_jtdsmssql TABLE table2; Please note, that using of the newly created connection requires either a system privilege USE ANY CONNECTION or the connection has to be explicitly granted to the user. Connections are automatically granted to the creator, including the ADMIN OPTION. Additional References https://docs.exasol.com/loading_data/connect_databases/sql_server.htm SOL-178  
    • Category 1:
      ETL / Data Integration
    • Category 2:
      Clients, Interfaces & Drivers - JDBC

      Attachments

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: