CODEWITHSUNDEEP
c#sql-server-2008
NoviceMe
NoviceMe

Reputation: 3256

Copy datarow from one table to another similar table in different database c#

I have tableA in database1 and tableA in database2 both have similar number of columns and names basically same tables. But both have different data in them. I am trying to get a row from database1/tableA and insert it into database2/tableA.

This is how i am thinking of doing it:

SqlConnection conn = new SqlConnection("database1")
SqlCommand cmd = new SqlCommand("Select * from tableA where id = 1");
connection.Open()
SqlDataReader reader = cmd.ExecuteReader();

if(reader !=null )
var data = reader;

connection.Close();

Then i do the same above steps open a new connection and try to insert data variable values to tableA in database2.

Is this right approach? Is there a better way to do this?

Upvotes: 1

Views: 1070

Answers (3)

Maria
Maria

Reputation: 141

        string connection_String = @""; //your connection string
        try
        {
            using (SqlConnection con = new SqlConnection(connection_String))
            {
                string sql = "INSERT INTO table_copy_to " +
                    "(column1, column2, column3 ... columnn) " +
                "SELECT column1, column2, column3 ... column FROM table_copy_from";
                con.Open();
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    int rowEffected = cmd.ExecuteNonQuery();
                    if (rowEffected > 0)
                    {
                        Console.WriteLine("Excuted Successfully ...");
                    }
                    else
                    {
                        Console.WriteLine("Some problem occur");
                    }
                } 
            }
        }

        catch (Exception ex)
        {
            Console.WriteLine("Exception : " + ex);
        }

Upvotes: 0

KeithS
KeithS

Reputation: 71591

I would do this with an inter-DB query. In MSS 2005/2008, you can "link" two servers together as long as they can see each other. Once that's done, you can refer to the linked database's table by specifying the linked server name, database, schema owner and table in dot notation. That would allow an INSERT SELECT:

INSERT INTO TableA --in database1
{
   /*columns*/
}
SELECT /*columns*/ from remoteServer.database2.dbo.TableB
WHERE /*dupe-checking, other conditions*/

If the two databases are on the same server, you don't even have to link; just preface the table on the remote database with the DB name and schema owner (or if it's the default "dbo", use two dots between db name and table name.

Upvotes: 3

Muthukumar
Muthukumar

Reputation: 9609

you can use this query instead

INSERT INTO DATABASE2.dbo.TABLEA T1
SELECT * FROM DATABASE1.dbo.TABLEA T2
WHERE T2.ID = 1

The following c#, code should work, provided both databases are in the same server.

SqlConnection conn = new SqlConnection("Enter Connection String of DB, in which you insert the records.(in ur example it is,DATABASE2)");
            string cmdText=@"INSERT INTO DATABASE2.dbo.TABLEA T2
                             SELECT * FROM DATABASE1.dbo.TABLEA T1
                             WHERE T1.ID = 1";
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            cmd.ExecuteNonQuery();

Upvotes: 1

Related Questions