CODEWITHSUNDEEP
c#sql
Ryan
Ryan

Reputation: 5546

execute sql query using 2 databases

My query looks like:

INSERT INTO [NewModel.Persistence.PersistencyContext].[dbo].[NewPerson] (
    [Name], 
    [Location], 
    [LastUpdate]
    )
SELECT 
    MIN([name]), 
    MIN([location]), 
    MIN([time])
FROM [OldModel.Persistence.PersistencyContext].[dbo].[ExPerson]
GROUP BY name

How do I define the connection string and what is the c# code to execute this query?

Upvotes: 0

Views: 1818

Answers (2)

Ayrab
Ayrab

Reputation: 336

There are two things going on here.

First if your databases are on the same instance of sql server (essentially two different versions of the same database running under the sql instance) then you won't need to have a linked server.

However, if they are running on different servers (and possibly different machines) then you'll need to link them as stated by Oded and David.

I would create a stored procedure and call it from the code as needed. 

CREATE PROC usp_AddMyRecords

AS
BEGIN  
INSERT INTO [NewModel.Persistence.PersistencyContext].[dbo].[NewPerson] (  
    [Name],   
    [Location],   
    [LastUpdate]  
    )  
SELECT   
    MIN([name]),   
    MIN([location]),   
    MIN([time])  
FROM [OldModel.Persistence.PersistencyContext].[dbo].[ExPerson]  
GROUP BY name  

END

C# Code To Call the Procedure: 

SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings
                                     [YOUR_CONNECTION_STRING_NAME].ConnectionString);  
SqlCommand cmd = new SqlCommand();  
cmd.CommandText = "usp_AddMyRecords";  
cmd.CommandType = CommandType.StoredProcedure;  
cmd.Connection = dbConnection;  
conn.Open();
cmd.ExecuteNonQuery();

Upvotes: 2

David Hoerster
David Hoerster

Reputation: 28701

If your database is SQL Server, you'll need to create a linked server connection between NewModel and OldModel's servers. You'll need to run the sp_addlinkedserver stored procedure (MSDN documentation here).

As Oded's comment states, your connection string to NewModel will be just a normal connection string (server=NewModelServer;Initial Catalog=NewModel;UID=;PWD=;) and just connect with a user (SQL Server authentication or Integrated Security) that can access both servers.

Hope this helps!

Upvotes: 0

Related Questions