C# for SQL Data Transfer

  • Thread starter Thread starter Stu
  • Start date Start date
S

Stu

This is really more of an SQL question but I'm doing the client-side
coding in C# so I figured it would make sense to post it here.

I'd like to export some portion of data from an SQL database to a
file, then import that data to a different database which has the same
structure. I've googled high and low and come up with a bunch of
different options, all of which seem to be more complicated than I was
hoping this would be.

The most common solution seems to be exporting to XML (easy) and then
using either an updategram or diffgram to import the data (never used
either and they are a little confusing). I need to deal with the
possibility that some rows that are in the exported file already exist
in the database I'm importing into, in which case I would ignore those
rows rather than doing an update. Also I don't fully understand how
to execute a diffgram. The DataSet object has the ability to read/
write a diffgram to/from a file, which seems close to what I want.
However it appears that in order to apply the diffgram to a database
table, I'd have to load the entire table into the DataSet and then do
DataSet.ReadXml(fileName, XmlReadMode.DiffGram) which essentially runs
a Merge. Obviously I don't want to have to load the entire table just
to figure out which IDs not to insert, so is there a better way to do
this that I'm missing? Is it possible to load just the primary keys
of a table into the DataSet and then do ReadXml for the diffgram
against those?

Also if there is a better way other than what I'm trying to do, please
let me know. Thanks in advance, my head is spinning at this point!
 
Stu said:
This is really more of an SQL question but I'm doing the client-side
coding in C# so I figured it would make sense to post it here.

I'd like to export some portion of data from an SQL database to a
file, then import that data to a different database which has the same
structure. I've googled high and low and come up with a bunch of
different options, all of which seem to be more complicated than I was
hoping this would be.
Click to expand...
bcp or SqlBulkCopy.
 
your scenario isn't very clear, so I will share a scenario: you have data in
a microsoft sql server stored in a table. You want to transfer this data to
another sql server. For this scenario - here are your options:

1) doing it manually - you export the data from sqlsvr1 to a text file, or
an Excel file, or an Xml file. Then import that data using the import wizard
on sqlsvr2.

2) you could create linked servers and transfer the data directly from query
analyzer.

3) create a .net app and use sqlAdapters to connect to each sql server.
Pull data from one server and transfer this data to the other sql server
using CreateDataReader

DataTableReader reader = dataset1.Tables["tbl1"].CreateDataReader();
dataset1.Tables["tbl2"].Load(reader, LoadOption.Upsert);
sqlDataAdapter1.Update(dataset1, "tbl2");

Here "tbl1" and "tbl2" are data tables connected to respective sql servers
and contained in a dataset object (dataset1). dataset1.Tables["tbl1"]
contains data pulled from a table in one sql server. dataset1.Tables["tbl2"]
is an empty data table which the structure of the table was pulled from sql
server2. You read the data from tbl1 to tbl2 using CreateDataReader on tbl1
and then Load on tbl2. And for the actual transfer to take place - you have
to run the update your sqlDataAdapter against tbl2.

Rich
 
Back
Top