How to insert all records into a table only one time

Question

Hello, I want to get all records from a table1 & insert them into table2. Table1 & table2 are in different databases, table1 and table2 are same structure.

Normally, I will get all records from table1, and for each record (foreach), I will insert it into table2 by using "INSERT ...". I want to know a effect way to insert all records into table only one time without foreach.

I use C#, .NET 2.0 & WinForm.

Thanks.

Answer 1

If both databases are hosted on the same Microsoft SQL Server, you could do a cross-database select to copy the data across without needing much code at all ...

insert into database1.dbo.table1
select *
from database2.dbo.table2

permissions are the tricky bit ...

Answer 2

In SQL Code you can do a SELECT in the INSERT

INSERT INTO Table2 
(Id, Name, IsActive) 
SELECT Id, Name, IsActive 
FROM Table2

Or if you are doing in Code then check out the SqlBulkCopy class

            sqlConn.Open();
            using (var bulkCopy = new SqlBulkCopy(sqlConn))
            {
                bulkCopy.DestinationTableName = "Table2";
                bulkCopy.WriteToServer(dataTable);
            }
            sqlConn.Close();

Answer 3

Can the two databases talk to eachother? In that case, forget about C#, and create a query that looks something like this:

use db2

SET IDENTITY_INSERT Table2 ON

INSERT INTO Table2
    SELECT * FROM db1.dbo.Table1

SET IDENTITY_INSERT Table2 OFF