SQL Server dataset read from database A and write to database B

29 Jan 2007 4:24 PM

RWC

Hello,

What I want I fairly simple (I guess), but I can't work it out.

In one application I export data from a SQL Server database to an XML
file. I have used the DataSet.WriteXML method to do this. My XML file
looks like this (simplified):

<?xml version="1.0" standalone="yes"?>
<brokers>
<broker>
    <broker_number>1</broker_number>
    <broker_name>Peter</broker_name>
</broker>
<broker>
    <broker_number>2</broker_number>
    <broker_name>Eric</broker_name>
</broker>
</brokers>

As you can guess this is just an export from a table with two fields:
broker_nummer and broker_name.

Now I want to fill the table "broker" in another SQL Server database.
I can not read from the XML file directly, but I have to read from a
string (which is just the content of the XML file). In the new
database the field names are slightly different: broker_number is
called BrokerId and broker_name is called Name

I have produced the following:

    private bool ImportXML(string docIn, ref string ErrorMsg)
    {

      const string selectquery = "SELECT * FROM broker WHERE BrokerID
= @BrokerID";
      const string insertquery = "INSERT INTO broker (Name) VALUES
(@Name)";
      const string updatequery = "UPDATE broker SET BrokerID =
@BrokerID, Name= @Name WHERE BrokerID = @BrokerID";
      const string deletequery = "DELETE FROM broker WHERE BrokerID =
@BrokerID";

      try
      {
        //create a custom adapter
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

        //fill dataset with XML-data
        DataSet ds = new DataSet();
        //we need a string reader to read from string
        StringReader sr = new StringReader(docIn);

        // Create the SelectCommand
        SqlCommand query = new SqlCommand(selectquery, m_Connection);
        query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
        sqlDataAdapter.SelectCommand = query;

        // Create the DeleteCommand
        query = new SqlCommand(deletequery, m_Connection);
        query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
        sqlDataAdapter.DeleteCommand = query;

        // Create the InsertCommand.
        query = new SqlCommand(insertquery, m_Connection);
        query.Parameters.Add("@Name", SqlDbType.NVarChar, 50,
"broker_name");
        sqlDataAdapter.InsertCommand = query;

        // Create the UpdateCommand.
        query = new SqlCommand(updatequery, m_Connection);
        query.Parameters.Add("@pBrokerID", SqlDbType.Int);
        query.Parameters["@pBrokerID"].SourceVersion =
DataRowVersion.Current;
        query.Parameters["@pBrokerID"].SourceColumn = "broker_number";

        query.Parameters.Add("@pNaam", SqlDbType.VarChar);
        query.Parameters["@pNaam"].SourceVersion =
DataRowVersion.Current;
        query.Parameters["@pNaam"].SourceColumn = "broker_naam";

        sqlDataAdapter.UpdateCommand = query;

        ds.ReadXml(sr);

        sqlDataAdapter.Update(ds, "Broker");

        return true;
      }
      catch (Exception e)
      {
        ErrorMsg = e.Message;
        return false;
      }
    }

I have got a few questions:

1. Is this the right approach?
2. Are the queries correct?
3. I am inserting data in an empty table. Why I keep getting this
message after inseting 1 record?

"Violation of UNIQUE KEY constraint 'IX_Broker_1'. Cannot insert
duplicate key in object 'dbo.broker'.\r\nThe statement has been
terminated."

Can you help me out. Thanks.

29 Jan 2007 6:27 PM

Otis Mukinfus

Show quote

On 29 Jan 2007 08:24:35 -0800, "RWC" <boe***@hotmail.com> wrote:

>Hello,
>
>What I want I fairly simple (I guess), but I can't work it out.
>
>In one application I export data from a SQL Server database to an XML
>file. I have used the DataSet.WriteXML method to do this. My XML file
>looks like this (simplified):
>
><?xml version="1.0" standalone="yes"?>
><brokers>
> <broker>
>    <broker_number>1</broker_number>
>    <broker_name>Peter</broker_name>
> </broker>
> <broker>
>    <broker_number>2</broker_number>
>    <broker_name>Eric</broker_name>
> </broker>
></brokers>
>
>As you can guess this is just an export from a table with two fields:
>broker_nummer and broker_name.
>
>Now I want to fill the table "broker" in another SQL Server database.
>I can not read from the XML file directly, but I have to read from a
>string (which is just the content of the XML file). In the new
>database the field names are slightly different: broker_number is
>called BrokerId and broker_name is called Name
>
>I have produced the following:
>
>    private bool ImportXML(string docIn, ref string ErrorMsg)
>    {
>
>      const string selectquery = "SELECT * FROM broker WHERE BrokerID
>= @BrokerID";
>      const string insertquery = "INSERT INTO broker (Name) VALUES
>(@Name)";
>      const string updatequery = "UPDATE broker SET BrokerID =
>@BrokerID, Name= @Name WHERE BrokerID = @BrokerID";
>      const string deletequery = "DELETE FROM broker WHERE BrokerID =
>@BrokerID";
>
>
>      try
>      {
>        //create a custom adapter
>        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
>
>        //fill dataset with XML-data
>        DataSet ds = new DataSet();
>        //we need a string reader to read from string
>        StringReader sr = new StringReader(docIn);
>
>        // Create the SelectCommand
>        SqlCommand query = new SqlCommand(selectquery, m_Connection);
>        query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
>        sqlDataAdapter.SelectCommand = query;
>
>        // Create the DeleteCommand
>        query = new SqlCommand(deletequery, m_Connection);
>        query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
>        sqlDataAdapter.DeleteCommand = query;
>
>        // Create the InsertCommand.
>        query = new SqlCommand(insertquery, m_Connection);
>        query.Parameters.Add("@Name", SqlDbType.NVarChar, 50,
>"broker_name");
>        sqlDataAdapter.InsertCommand = query;
>
>        // Create the UpdateCommand.
>        query = new SqlCommand(updatequery, m_Connection);
>        query.Parameters.Add("@pBrokerID", SqlDbType.Int);
>        query.Parameters["@pBrokerID"].SourceVersion =
>DataRowVersion.Current;
>        query.Parameters["@pBrokerID"].SourceColumn = "broker_number";
>
>        query.Parameters.Add("@pNaam", SqlDbType.VarChar);
>        query.Parameters["@pNaam"].SourceVersion =
>DataRowVersion.Current;
>        query.Parameters["@pNaam"].SourceColumn = "broker_naam";
>
>        sqlDataAdapter.UpdateCommand = query;
>
>        ds.ReadXml(sr);
>
>        sqlDataAdapter.Update(ds, "Broker");
>
>        return true;
>      }
>      catch (Exception e)
>      {
>        ErrorMsg = e.Message;
>        return false;
>      }
>    }
>
>I have got a few questions:
>
>1. Is this the right approach?
>2. Are the queries correct?
>3. I am inserting data in an empty table. Why I keep getting this
>message after inseting 1 record?
>
>"Violation of UNIQUE KEY constraint 'IX_Broker_1'. Cannot insert
>duplicate key in object 'dbo.broker'.\r\nThe statement has been
>terminated."
>
>Can you help me out. Thanks.

I think you are trying to insert the primary key from the original table into
the primary key of the new table, which may be a an autoincrement column. If
that's the case you have two options.

1. Don't insert the primary key of the first table into the second.

or

2. Turn off the autoincrement of the second table before doing the inserts and
turn it back on when you are done.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

29 Jan 2007 7:31 PM

William (Bill) Vaughn

If the overall purpose is to move data from one SQL Server to another you
should use SqlBulkCopy, DTS, SSIS or somesuch. You can also link the two
servers and do a direct INSERT from one to the other.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Show quote

"RWC" <boe***@hotmail.com> wrote in message
news:1170087875.688209.302600@k78g2000cwa.googlegroups.com...
> Hello,
>
> What I want I fairly simple (I guess), but I can't work it out.
>
> In one application I export data from a SQL Server database to an XML
> file. I have used the DataSet.WriteXML method to do this. My XML file
> looks like this (simplified):
>
> <?xml version="1.0" standalone="yes"?>
> <brokers>
> <broker>
>    <broker_number>1</broker_number>
>    <broker_name>Peter</broker_name>
> </broker>
> <broker>
>    <broker_number>2</broker_number>
>    <broker_name>Eric</broker_name>
> </broker>
> </brokers>
>
> As you can guess this is just an export from a table with two fields:
> broker_nummer and broker_name.
>
> Now I want to fill the table "broker" in another SQL Server database.
> I can not read from the XML file directly, but I have to read from a
> string (which is just the content of the XML file). In the new
> database the field names are slightly different: broker_number is
> called BrokerId and broker_name is called Name
>
> I have produced the following:
>
>    private bool ImportXML(string docIn, ref string ErrorMsg)
>    {
>
>      const string selectquery = "SELECT * FROM broker WHERE BrokerID
> = @BrokerID";
>      const string insertquery = "INSERT INTO broker (Name) VALUES
> (@Name)";
>      const string updatequery = "UPDATE broker SET BrokerID =
> @BrokerID, Name= @Name WHERE BrokerID = @BrokerID";
>      const string deletequery = "DELETE FROM broker WHERE BrokerID =
> @BrokerID";
>
>
>      try
>      {
>        //create a custom adapter
>        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
>
>        //fill dataset with XML-data
>        DataSet ds = new DataSet();
>        //we need a string reader to read from string
>        StringReader sr = new StringReader(docIn);
>
>        // Create the SelectCommand
>        SqlCommand query = new SqlCommand(selectquery, m_Connection);
>        query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
>        sqlDataAdapter.SelectCommand = query;
>
>        // Create the DeleteCommand
>        query = new SqlCommand(deletequery, m_Connection);
>        query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
>        sqlDataAdapter.DeleteCommand = query;
>
>        // Create the InsertCommand.
>        query = new SqlCommand(insertquery, m_Connection);
>        query.Parameters.Add("@Name", SqlDbType.NVarChar, 50,
> "broker_name");
>        sqlDataAdapter.InsertCommand = query;
>
>        // Create the UpdateCommand.
>        query = new SqlCommand(updatequery, m_Connection);
>        query.Parameters.Add("@pBrokerID", SqlDbType.Int);
>        query.Parameters["@pBrokerID"].SourceVersion =
> DataRowVersion.Current;
>        query.Parameters["@pBrokerID"].SourceColumn = "broker_number";
>
>        query.Parameters.Add("@pNaam", SqlDbType.VarChar);
>        query.Parameters["@pNaam"].SourceVersion =
> DataRowVersion.Current;
>        query.Parameters["@pNaam"].SourceColumn = "broker_naam";
>
>        sqlDataAdapter.UpdateCommand = query;
>
>        ds.ReadXml(sr);
>
>        sqlDataAdapter.Update(ds, "Broker");
>
>        return true;
>      }
>      catch (Exception e)
>      {
>        ErrorMsg = e.Message;
>        return false;
>      }
>    }
>
> I have got a few questions:
>
> 1. Is this the right approach?
> 2. Are the queries correct?
> 3. I am inserting data in an empty table. Why I keep getting this
> message after inseting 1 record?
>
> "Violation of UNIQUE KEY constraint 'IX_Broker_1'. Cannot insert
> duplicate key in object 'dbo.broker'.\r\nThe statement has been
> terminated."
>
> Can you help me out. Thanks.
>