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

Question

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?

You could set up replication, but it depends on how often you want to copy data. Is it a one time thing, or should the two databases be at synch? — Patrick
– Patrick, Commented Sep 5, 2012 at 16:04

KeithS · Accepted Answer · 2012-09-05 18:22:13Z

3

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.

edited Sep 5, 2012 at 18:22

answered Sep 5, 2012 at 16:06

KeithS

71.9k24 gold badges118 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

NoviceMe Over a year ago

How would i write connection string in this case? To connect to both servers?

KeithS Over a year ago

You wouldn't; you would only need to connect to one database. The information required for that database to connect to the remote DB is known by the server, and doesn't have to be known by anyone connecting to it.

KeithS Over a year ago

The thing is, this solution doesn't even require C# or a program; just link the servers, then write the statement to insert-select from one table to the other and run it with SQL Management Studio or SQLCMD.

NoviceMe Over a year ago

Problem is i dont want to do this in sql we are creating a windows utility to accomplish this. So has to be in C# code. Can you please suggest solution for that? So in that case i will just connect to one server?

KeithS Over a year ago

Just use the connection string for the server that is linked to the other one. Links are one-way (though you can create a link on each server that references the other one), so if database A with Table A is linked to database B with table B, connect to database A from your program. Then construct the SQL statement as a string and execute it with a SqlCommand.

|

Muthukumar · Accepted Answer · 2012-09-05 18:11:04Z

1

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();

edited Sep 5, 2012 at 18:11

answered Sep 5, 2012 at 16:03

Muthukumar

9,73918 gold badges67 silver badges87 bronze badges

7 Comments

Olivier Jacot-Descombes Over a year ago

The correct syntax would be either database..table or database.schema.table, where schema is probably dbo.

NoviceMe Over a year ago

@Muthukumar - So i need to create two connection strings at top? Can i connect to two databases at same time? Can you please show me the code?

NoviceMe Over a year ago

@Muthukumar - Both databases are on differnt servers. I tried the above query in Sql first to make sure it works. But it is not working?

Muthukumar Over a year ago

@NoviceMe : If they are on different servers,one solution is like, you need to create a link server in one server pointing to the other.

NoviceMe Over a year ago

@Muthukumar - I can not manipulate databases is there any way to query it from C#? What is the other solution? To open 2 different connections?

|

Maria · Accepted Answer · 2015-04-27 06:27:56Z

        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);
        }

Collectives™ on Stack Overflow

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

3 Answers 3

7 Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related