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

**URL:** <https://discourse.julialang.org/t/how-to-do-fast-bulk-copy-from-local-windows-computer-to-sql-server/105503>\
**Category:** New to Julia\
**Created:** [October 28, 2023, 1:03pm UTC](https://discourse.julialang.org/t/how-to-do-fast-bulk-copy-from-local-windows-computer-to-sql-server/105503 "2023-10-28T13:03:42Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jwright11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jwright11/32/32065_2.png) [@jwright11](https://discourse.julialang.org/u/jwright11)\
**Post date:** [October 28, 2023, 1:03pm UTC](https://discourse.julialang.org/t/how-to-do-fast-bulk-copy-from-local-windows-computer-to-sql-server/105503/1 "2023-10-28T13:03:42Z")

</div>

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](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=dotnet-plat-ext-7.0) 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](https://odbc.juliadatabases.org/dev/). I got the [ODBC.load](https://odbc.juliadatabases.org/dev/#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](https://github.com/azurefx/DotNET.jl) package but haven’t quite figured out the syntax yet. Is anyone already doing this?

---

<div class="post-metadata">

**Author:** ![nielsls](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nielsls/32/18482_2.png) [@nielsls](https://discourse.julialang.org/u/nielsls)\
**Post date:** [October 28, 2023, 2:51pm UTC](https://discourse.julialang.org/t/how-to-do-fast-bulk-copy-from-local-windows-computer-to-sql-server/105503/2 "2023-10-28T14:51:16Z")

</div>

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

```julia
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

```

---

<div class="post-metadata">

**Author:** ![jwright11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jwright11/32/32065_2.png) [@jwright11](https://discourse.julialang.org/u/jwright11)\
**Post date:** [October 28, 2023, 4:47pm UTC](https://discourse.julialang.org/t/how-to-do-fast-bulk-copy-from-local-windows-computer-to-sql-server/105503/3 "2023-10-28T16:47:21Z")

</div>

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