title: Bulk copy from SQL Server to PostgreSQL
body: <p>I need to copy a large SAP table (28 columns with mostly nvarchar, 100 mio records) from a SQL Server into a PostgreSQL database as fast as possible every day (delta update is not possible) using Python.</p>
  
  <p>Before copying, I have created an empty table with the same structure (equivalent data types) like the source table in the PostgreSQL database.</p>
  
  <p>Right now I am using the following implementation based on the read_sql_query method from pandas for reading and the copy_from
  method for writing and it takes ~1 min for copying 1 mio records.</p>
  
  <blockquote>
    <p>Does anyone know a faster implementation or have ideas for optimization?. I already switched the driver to ODBC and dropped all constraints in the target table like primary keys.</p>
  </blockquote>
  
  <p>PS: the ETL tool Pentaho Dataintegration is two times faster and is a benchmark for me.</p>
  
  <p>SQL create statement for creating the empty target table in the PostgreSQL database:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS q_nlei (
    mandt varchar(3),
    einri varchar(4),
    falnr int,
    leist varchar(10),
    lnrls varchar(10),
    lfdbew varchar(5),
    anfoe varchar(8),
    anman varchar(1),
    erboe varchar(8),
    erman varchar(1),
    haust varchar(2),
    preis decimal(13,2),
    abrkz varchar(1),
    imeng varchar(4),
    ibgdt date,
    iendt date,
    ibzt time,
    iezt time,
    tarif varchar(2),
    tarls varchar(10),
    tarsp varchar(2),
    erdat date,
    updat date,
    storn varchar(1),
    remrk varchar(40),
    anpoe varchar(8),
    refky varchar(20),
    thergroup smallint);
  </code></pre>
  
  <p>Python script for copying data:</p>
  
  <pre><code>import pandas as pd
  import io
  from sqlalchemy import create_engine
  
  src_engine = create_engine(f"mssql+pyodbc://{usr}:{pw}@{host}:1433/{db}?driver=ODBC+Driver+17+for+SQL+Server", fast_executemany=True)
  snk_engine = create_engine(f"postgresql+psycopg2://{usr}:{pw}@{host}/{db}")
  
  sql_query = "SELECT * FROM Q_NLEI"
  
  for chunk in pd.read_sql_query(sql_query, src_engine, chunksize = 500000):
    chunk.columns = map(str.lower, chunk.columns)
  
    # Prepare data
    output = io.StringIO()
    chunk.to_csv(output, sep='\t', header=False, encoding="utf8", index=False)
    output.seek(0)
    # Insert data
    connection = snk_engine.raw_connection()
    cursor = connection.cursor()
    cursor.copy_from(output, snk_tbl, sep='\t', null='')
    connection.commit()
    cursor.close()
  </code></pre>