How to copy table records from one table to another in different databases

Question

I want to copy data records from one table in the database to another table in a different database.

I am using two different connection strings for source and destination connections.

SQL = "Select * from Employee";
DT = dbCommServer.GetDataTable(SQL);  // DT stores records from one Table.

// Query to insert Records from Source Table to destination Table in different Database.
SQL = "INSERT INTO [EmployeeLocal] ( [EmpID],[EmpName], [Salary]) ";
        SQL += "VALUES ('Select [EmpID],[EmpName], [Salary] from [DT]')";
        dbCommLocal.ExecuteCommand(SQL);

This code doesn't insert any records into the destination table.

Please suggest any suitable way to insert table records stored in DT into another table in a different database.

Best regards.

score 0 · Answer 1 · edited May 23 '17 at 12:07

0

You have the INSERT incorrect, you have specified a VALUES clause which expects row constructors, you want to specify your table:

    SQL = "INSERT INTO [EmployeeLocal] ( [EmpID],[EmpName], [Salary]) ";
    SQL += "Select [EmpID],[EmpName], [Salary] from [DT]";
    dbCommLocal.ExecuteCommand(SQL);

UPDATE:

The issue is the SQL variable has passed the T-SQL statement as a statement rather than passing your table DT up. SQL Server is expecting a table in the database called DT.

Check out: Insert entire DataTable into database at once instead of row by row?

edited May 23 '17 at 12:07

Community

1
1

answered Dec 01 '15 at 11:30

Tony Rogerson

579
2
6

Sorry for the confusion; DT is not a SQL table which is what the INSERT statement is expecting. DT is a DataTable within your .NET app so you need to use .NET to pass the data in - check the update on my answer for a link to another stackoverflow post. – Tony Rogerson Dec 01 '15 at 11:36

score 0 · Answer 2 · answered Dec 01 '15 at 11:34

In your second statement, you insert from table DT, but DT only exists in your c# code, it does not exist in your sql database, which is why it deosn't work.

You will need to go through your c# table dt row by row, and run an insert statement for each row. This below is just pseudo code - you should really use a parameterised query. However, I don't know what dbCommLocal is, and how to use parameters with it.

foreach ( DataRow row in DT ) 
{ 
    // first create the insert statement with parameters:
    SQL="INSERT INTO [EmployeeLocal] ( [EmpID],[EmpName], [Salary]) ";
    SQL += "VALUES (" + Row.Item["EmpID"] + ",'" +
                      + Row.Item["EmpName"] + "'," +
                      + Row.Item["Salary"] + ");"

    // Then run it
    dbCommLocal.ExecuteCommand(SQL);
}

How to copy table records from one table to another in different databases

2 Answers2