0

Yesterday I learned how to use ADO.NET to take a DataTable and bulk insert all records into a SQL server database. That works great for one part of my project. Now what I need to do is take the records from the DataTable and insert only the records that do not already exist in the SQL server. Im using VB2010 and the code looks something like:

'get the data from our Teradata server and fill up a DataTable
Dim dtCheck As New DataTable("TableCheck")
dtCheck = GetDataTableFromTeradataServer

'connect to our SQL server
Dim connSqlSvr As New System.Data.SqlClient.SqlConnection
connSqlSvr.ConnectionString = "Data Source=DestSqlServer;Initial Catalog=DestDb;Connect Timeout=15"
connSqlSvr.Open() 

'how do I take records from the DataTable and insert only those records that do not
'already exist in the SQL server?

'close and dispose the SQL server database connection
connSqlSvr.Close()
connSqlSvr.Dispose()

The SQL server destination table [DestDb].[dbo].[Events] has fields

[CityCode],[CarNum],[VIN],[Fleet],[CarMileage],[EventItm],[EventDate]

and the DataTable in memory has fields

City,CarNo,VIN,Fleet,Mileage,EventDesc,EventDate

For my purposes a unique record is made up of [CityCode],[CarNum],[VIN],[EventItm], and [EventDate]

Typically i will run this routine once a day and will get back about 200 records from the source. Been looking for a couple hours but just not sure how to approach this.

sinDizzy
  • 1,300
  • 7
  • 28
  • 60
  • 1
    There is a great solution for your problem [here][1] [1]: http://stackoverflow.com/questions/16460397/sql-insert-into-table-only-if-record-doesnt-exist – Vahid Farahmandian Nov 06 '14 at 23:33
  • Thanks. I ended up using the INSERT INTO SELECT FROM WHERE HAVING approach as detailed here http://stackoverflow.com/questions/913841/mysql-conditional-insert. I cycled through each record as noted here http://stackoverflow.com/questions/26940195/sql-server-parameterized-query-with-a-where-clause-with-nulls – sinDizzy Nov 20 '14 at 19:11

1 Answers1

0

You could try loading data in a staging table in the target server. Then do the unique insert from there.

Ronald
  • 3
  • 2