Created
November 10, 2011 14:50
-
-
Save growse/1355020 to your computer and use it in GitHub Desktop.
Bulk copy SQL from GNU splitted Postgres CSV output into MSSQL with IDENTITY_INSERT
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Data; | |
using System.Data.SqlClient; | |
using System.IO; | |
namespace ARGHGHGHGHG | |
{ | |
internal class Program | |
{ | |
static int progress = 0; | |
private static int total = 0; | |
private const int batchsize = 1000; | |
private static void Main() | |
{ | |
const string filename = "c:\\stuff\\x{0}.csv"; | |
Console.WriteLine("hi"); | |
using (var dbconn = new SqlConnection("string")) | |
{ | |
dbconn.Open(); | |
var bulk = new SqlBulkCopy(dbconn, SqlBulkCopyOptions.KeepIdentity, null) { DestinationTableName = "perflog", NotifyAfter = batchsize, BatchSize = batchsize }; | |
bulk.SqlRowsCopied += bulk_SqlRowsCopied; | |
for (var x = 0; x <= 9; x++) | |
{ | |
var currfilename = string.Format(filename, x.ToString("00")); | |
Console.WriteLine("Reading Data from {0}", currfilename); | |
var datatable = CsvReader.GetDataTable(currfilename); | |
total = datatable.Rows.Count; | |
Console.WriteLine("Done. {0} rows", total); | |
bulk.WriteToServer(datatable); | |
} | |
} | |
} | |
static void bulk_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e) | |
{ | |
progress += batchsize; | |
Console.WriteLine("Done {0} out of {1}", progress, total); | |
} | |
} | |
class CsvReader | |
{ | |
public static DataTable GetDataTable(string filename) | |
{ | |
var conn = new System.Data.OleDb.OleDbConnection(string.Concat("Provider=Microsoft.Jet.OleDb.4.0; Data Source = ", Path.GetDirectoryName(filename), "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"")); | |
conn.Open(); | |
var strQuery = string.Concat("SELECT * FROM [", Path.GetFileName(filename), "]"); | |
var adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn); | |
var dataset = new DataSet("CSV File"); | |
adapter.Fill(dataset); | |
return dataset.Tables[0]; | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment