Am looking to do a sql query which uses 2 databases. How can I use 2 connection strings for this query as it is 2 different databases?
var commandText = @"insert into database1 select * from database2";
using (SqlConnection myConn = new SqlConnection(ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand(commandText, myConn))
{
myConn.Open();
myCommand.ExecuteNonQuery();
}
If your both data bases in the same server instance then you don't need two connections. You are virtually connecting to the server instance. So there is no problem.
var commandText = @"insert into [dbname1].dbo.table1 select * from
[dbname2].dbo.table1";
I think best way is to create a stored procedure and pass dbnames (from your configurations) to the sp as a parameters. Then, build a dynamic query to perform the insert. That way you have a better control over different database names.
If they are in two different instances or separate servers, then use sp_addlinkedserver to link them and use sp_addlinkedsrvlogin to login. Also make sure to drop them after performing the action.
As a general advice, this is not recommended as its error prone. Bit more details on comments...
You can't. However, if you have control over the databases, you can set them up for a cross-server select using a single connection string.
I think you can't. You should execute the select statement on the first database and then the insert into the second database.
Create a login that has access to both databases and just use a single connection string. Your query will be like:
string sql = "INSERT INTO [Database1].[dbo].[Table] SELECT * FROM [Database2].[dbo].[Table]
[Database1].dbo.[Table] (twice), surely... but I should emphasize for the OP - this is not good practice and most DBAs will shoot you if they catch you doing this...