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?
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();
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!
