CODEWITHSUNDEEP
c#sqlsql-server
nadG
nadG

Reputation: 167

Need to use 2 connection strings for query in c#

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

Upvotes: 0

Views: 2250

Answers (4)

Kaf
Kaf

Reputation: 33809

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...

Upvotes: 2

Mike C.
Mike C.

Reputation: 3114

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]

Upvotes: 0

davioooh
davioooh

Reputation: 24676

I think you can't. You should execute the select statement on the first database and then the insert into the second database.

Upvotes: 0

John Kraft
John Kraft

Reputation: 6840

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.

Upvotes: 1

Related Questions