CODEWITHSUNDEEP
c#snowflake-cloud-data-platformbulk-load
BimA
BimA

Reputation: 11

Can you bulk upload using SnowflakeDbConnection?

I have this following code, it can insert one record at a time. It take long time to load the data line by line. 

using (SnowflakeDbConnection connection = new SnowflakeDbConnection())
{
 connection.ConnectionString = connstr;

 connection.Open(); 

 int rowsAffected;

 using (SnowflakeDbCommand sqlcomm = 
 SnowflakeDbCommand)connection.CreateCommand())
 {

  sqlcomm.CommandType = CommandType.Text;

 sqlcomm.CommandText = String.Format("INSERT INTO {0}({1},{2}) 
values('9',  'Value99')", DestinationTableName, "ValueID", "FullName");

rowsAffected = sqlcomm.ExecuteNonQuery();
}
}

I am looking for something like this 

using (var bulk = new SqlBulkCopy(this.connection))
{
    bulk.DestinationTableName = "DestinationTableName";
    bulk.WriteToServer(table);
}

Upvotes: 1

Views: 3718

Answers (2)

Hans Henrik Eriksen
Hans Henrik Eriksen

Reputation: 2850

You can look at Snowflake Connector for .NET and the Bind Parameter example in how to insert multiple rows at the same time (but still a small number).

You should look into writing your compressed data to an Azure blob "Snowflake staging area" and import via the COPY INTO table command
[I'm assuming you use Snowflake on Azure - works for AWS S3 and GCP as well].

Upvotes: 1

a_weiss_programmer
a_weiss_programmer

Reputation: 21

You should look at using the COPY INTO <"table"> command. There is some setup that you will need to do in order to get this working.

  1. You will need to create an internal stage within Snowflake using the PUT command.

  2. From here, you will then use the COPY INTO <"table"> command.

Based on this, what I would do is have your bulk data stored within a file (Snowflake supports many different file formats), and then pass that to the COPY INTO command's parameters.

The documentation does a better job of explaining this than I ever could :)

Overview of copying from local FS: https://docs.snowflake.net/manuals/user-guide/data-load-local-file-system.html

Syntax for PUT command: https://docs.snowflake.net/manuals/sql-reference/sql/put.html

Syntax for COPY INTO command: https://docs.snowflake.net/manuals/sql-reference/sql/copy-into-table.html

Upvotes: 1

Related Questions