Prev: ZeroMemory
Next: Storing a color value as a single byte
From: Stu on 2 Dec 2009 17:51
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!
From: Jeroen Mostert on 2 Dec 2009 18:00
Stu wrote:
> 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.
>
bcp or SqlBulkCopy.

--
J.
From: Stefan Hoffmann on 3 Dec 2009 14:00
hi Stu,

Stu wrote:
> 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!
Use the SQL Server native XML capabilities to export the data and use a
MERGE script to import it...

http://technet.microsoft.com/en-us/library/ms173812.aspx
http://technet.microsoft.com/en-us/library/ms175915%28SQL.90%29.aspx
http://technet.microsoft.com/en-us/library/bb510625.aspx



mfG
--> stefan <--
From: Rich on 3 Dec 2009 14:34
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


"Stu" wrote:

> 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!
> .
>
 | 
Pages: 1
Prev: ZeroMemory
Next: Storing a color value as a single byte