How to do fast bulk copy from local Windows computer to SQL Server?

New to Julia
1

I have a table with two string columns (about 30,000 rows) in an Excel file on Windows 10 and I’m trying to do a bulk copy into SQL Server. I have an Excel add-in which uses the C# SqlBulkCopy class to do a bulk copy from Excel into SQL Server and it works well and is fast. However, there is a need to be able to do that same fast bulk copy in Julia.

I am connecting to SQL Server using ODBC.jl. I got the ODBC.load function working but it only works well if the number of rows is very small (it goes row by row). I haven’t let it run long enough to finish loading all 30,000 rows but if it’s like 50 rows is does just fine. Maybe I’m missing something, but is anybody doing fast bulk copy to SQL Server with ODBC.jl or another package in Julia?

I’ve also been thinking maybe I can use the SqlBulkCopy class directly within Julia.
I’ve been trying to use the DotNET.jl package but haven’t quite figured out the syntax yet. Is anyone already doing this?

2

At my company we use the BULK INSERT command. Like this:

using DataFrames
using CSV

function save_to_db(df::DataFrame, dst_table::AbstractString)
    newline = '&' # default newline fucks up...

    # Need a file location where SQL server have access
    tmp_file = raw"\\mynetworkdrive\tmp.csv"
    CSV.write(tmp_file, df; newline)

    result = execute_sql(
        """
            BULK INSERT
                $dst_table
            FROM 
                '$tmp_file'
            WITH (
                FIRSTROW = 2,
                FIELDTERMINATOR = ',',
                ROWTERMINATOR='$newline'
            );
        """,
    )

    Base.Filesystem.rm(tmp_file)

    return result
end
1 Like
3

Yeah, I have seen that but the problem is that my SQL Server does not have access to my local machine.