Add data from in memory table to SQL table

[jcs1953]

I'm using Visual Web Developer 2005 and MSSQL Server 2005 Express.
I have the following code:

Dim mytable As New DataTable

Dim connectionString1 As String = GetConnectionString("FMC_AccessConnectionString")
Dim providerFactory1 As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim SQL1 = "SELECT Round(CMPatientDataFile.Result_Numeric,2)as RESULT_NUMERIC,CMPatientDataFile.Run_Date FROM CMPatientDataFile WHERE CMPatientDataFile.Pat_ID = '3848.1' AND CMPatientDataFile.Test_Name = 'BUN' ORDER BY CMPatientDataFile.Run_Date"
Dim connection1 As DbConnection = providerFactory1.CreateConnection
connection1.ConnectionString = connectionString1
Dim command1 As DbCommand = providerFactory1.CreateCommand
command1.Connection = connection1
command1.CommandText = SQL1
command1.CommandType = CommandType.Text
connection1.Open()

Dim dr2 As DbDataReader = command1.ExecuteReader()
mytable.Load(dr2)

GridView2.DataSource = mytable
GridView2.DataBind()

connection1.Dispose()
command1.Dispose()

This gives me a datatable in memory called 'mytable'. The table looks like this:
RESULT_NUMERIC RUN_DATE
10.2 2/4/2009
8.7 3/5/2009

I also have a table in an SQL database table called 'Combined_results' with the same column headings (empty rows).
What I need to do is this:
1. Empty the SQL table.
2. Copy the 'mytable' data to the SQL table.
There is no primary key.
I imagine that this is easy to do for the experts but is giving me fits.
Thanks

 

[jcs1953]

Hey guys ... did you forget me? Do threads usually get hijacked by the experts?

 

[jbenson001]

Sorry, but in our hijacking I think we gave you some ideas. If you are new to programming and dbs then you can use .NET. You could create 2 connections. 1 to get the data you need into a datatable. Then connected to the destination database using your second connection. You can loop through the datatable and insert each row into the destination db.

 

[jmeckley]

appologies. we went off on a tangent.

if this is a one time thing. then don't waist time trying to code this up. find the quickest way to dump the data from one db to another.

if this is part of generating a report. then follow jbenson001's advice.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ca8msm]

what is data logic? it's either data, or logic. having the data come from heterogeneous sources doesn't change that.
then again, this opens the conversation we had in a previous thread. What role does the database play?

Data logic is deciding exactly what data should be returned to the application. Regardless of where you get your data from, you need to decide what is relevant and apply that logic to the procedures retrieving the data. By having the two data sources in this case doesn't mean you have to start introducing extra complexity into what the application has to do with the data; you simply return what is relevant. By using either a linked server, or performing an ETL script to gather all of the data together in one places means that you don't start blurring the lines of data retrieval and application logic.

Mark,

Website Design Darlington
Experts, Information, Ideas & Knowledge
ASP.NET Tips & Tricks