Insert data from one SQL Database to a second one using C#

Question

I dont know how to do this query in c#.

There are two databases and each one has a table required for this query. I need to take the data from one database table and update the other database table with the corresponding payrollID.

I have two tables in seperate databases, Employee which is in techData database and strStaff in QLS database. In the employee table I have StaffID but need to pull the PayrollID from strStaff.

Insert payrollID into Employee where staffID from strStaff = staffID from Employee

However I need to get the staffID and PayrollID from strStaff before I can do the insert query.

This is what I have got so far but it wont work.

cn.ConnectionString = ConfigurationManager.ConnectionStrings["PayrollPlusConnectionString"].ConnectionString;
cmd.Connection = cn;
cmd.CommandText = "Select StaffId, PayrollID From [strStaff] Where (StaffID = @StaffID)";
cmd.Parameters.AddWithValue("@StaffID", staffID);

//Open the connection to the database
cn.Open();
// Execute the sql.
dr = cmd.ExecuteReader();
// Read all of the rows generated by the command (in this case only one row).
For each (dr.Read()) {
    cmd.CommandText = "Insert into Employee, where StaffID = @StaffID";
}
// Close your connection to the DB.
dr.Close();
cn.Close();

`For each`? o.O You mean `while`? And should add your `@StaffID` value inside your while statement and execute it with `ExecuteNonQuery`. Also you should consider to clear your parameter every iteration with `Parameters.Clear()` method. By the way, it is not clear which column value you want to insert. You should get this value like `reader[0], reader[1] etc..` and you should insert _that_ value. — Soner Gönül, Jun 01 '15 at 14:23
Do two things in a loop: `while(dr.Read()) { // get parameter values // execute INSERT command }` Let us know which part you're stuck on - plenty of examples out there of both. — D Stanley, Jun 01 '15 at 14:26
If it's a while can you update your code. I was going to update it but I wasn't sure if it was the correct thing to do — Liam, Jun 01 '15 at 14:27
You said you have separate databases, I only see you working with one database in your example, the one you access via `PayrollPlusConnectionString`. — Scott Chamberlain, Jun 01 '15 at 14:27
The Title is misleading: where is the connection to the second Database mentioned in the Title? Please clarify. Thanks and regards, — Alexander Bell, Jun 01 '15 at 14:28
You probably want to use UPDATE + SELECT, please see: http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server — Maciej Los, Jun 01 '15 at 14:29
@Redheadinferno You should ideally use the Select Into statement... http://www.w3schools.com/sql/sql_select_into.asp — Paul Zahra, Jun 01 '15 at 14:31
@Alex Bell The two databases is where the data is coming from so the insert query is going into one database but needing data from the other database table to insert the data into the corresponding row. — Redheadinferno, Jun 01 '15 at 14:32
Is this an INSERT or a UPDATE. This code is very, very confusing. — Liam, Jun 01 '15 at 14:33
So, you have to open another connection to that SECOND Database and properly format the INSERT query for that second Database. The code you have posted is very confusing: please add the required correction (btw, are you sure you are talking about two DATABASES and not of two TABLES?). Thanks and regards, — Alexander Bell, Jun 01 '15 at 14:36
The code is wrong and confusing but just put it up to show I had tried. — Redheadinferno, Jun 01 '15 at 14:38
Then try to correct it as stated above and add more clarity: is it indeed two Databases or just two Tables within the same Database? Thanks and regards, — Alexander Bell, Jun 01 '15 at 14:41
I have added some clarity to your question, otherwise it was a bit misleading. The essential part is that you want to create a C# procedure to accomplish your task (not using SQL Server Management Studio, SSIS, bulk insert, etc). Best regards, — Alexander Bell, Jun 01 '15 at 18:19
Your approach is possible, but very laborious. You could do much more elegant, faster and more stable by creating just one simple SQL statement which copies data from one database to another. And of course, you can then call this statement in C#. But you don't need loops and DataTables etc. — SQL Police, Jun 30 '15 at 16:40

Maciej Los · Answer 1 · 2015-06-01T14:41:15.313

2

Assuminig, you want to add data to existing table, you have to use UPDATE + SELECT statement (as i mentioned in a comment to the question). It might look like:

UPDATE emp SET payrollID = sta.peyrollID
FROM Emplyoee AS emp INNER JOIN strStaff AS sta ON emp.staffID = sta.staffID

edited Jun 01 '15 at 14:41

answered Jun 01 '15 at 14:36

Maciej Los

8,468
1
20
35

How can I do two connection strings for the one query? As I would be trying to set payrollID in one database but need to pull the data from another database. – Redheadinferno Jun 01 '15 at 15:02
@Redheadinferno Lets get something clear... when you say two databases... is that two databases in one SQL Server instance? or is that two databases in different SQL Server instances? – Paul Zahra Jun 01 '15 at 15:23
Two databases on two different SQL server instances – Redheadinferno Jun 01 '15 at 15:41
@Redheadinferno, create linked server and then you'll be able to use above query. For more details, please see: https://msdn.microsoft.com/en-us/library/ff772782.aspx?f=255&MSPPError=-2147217396 – Maciej Los Jun 01 '15 at 17:59

Alexander Bell · Answer 2 · 2015-06-01T18:45:48.673

I have added some clarity to your question: the essential part is that you want to create a C# procedure to accomplish your task (not using SQL Server Management Studio, SSIS, bulk insert, etc). Pertinent to this, there will be 2 different connection objects, and 2 different SQL statements to execute on those connections.

The first task would be retrieving data from the first DB (for certainty let's call it source DB/Table) using SELECT SQL statement, and storing it in some temporary data structure, either per row (as in your code), or the entire table using .NET DataTable object, which will give substantial performance boost. For this purpose, you should use the first connection object to source DB/Table (btw, you can close that connection as soon as you get the data).

The second task would be inserting the data into second DB (target DB/Table), though from your business logic it's a bit unclear how to handle possible data conflicts if records with identical ID already exist in the target DB/Table (some clarity needed). To complete this operation you should use the second connection object and second SQL query.

The sample code snippet to perform the first task, which allows retrieving entire data into .NET/C# DataTable object in a single pass is shown below:

private static DataTable SqlReadDB(string ConnString, string SQL)
{
    DataTable _dt;
    try
    {
        using (SqlConnection _connSql = new SqlConnection(ConnString))
        {
            using (SqlCommand _commandl = new SqlCommand(SQL, _connSql))
            {
                _commandSql.CommandType = CommandType.Text;
                _connSql.Open();
                using (SqlCeDataReader _dataReaderSql = _commandSql.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    _dt = new DataTable();
                    _dt.Load(_dataReaderSqlCe);
                    _dataReaderSql.Close();
                }
            }
            _connSqlCe.Close();
            return _dt;
        }
    }
    catch { return null; }
}

The second part (adding data to target DB/Table) you should code based on the clarified business logic (i.e. data conflicts resolution: do you want to update existing record or skip, etc). Just iterate through the data rows in DataTable object and perform either INSERT or UPDATE SQL operations.

Hope this may help. Kind regards,

Insert data from one SQL Database to a second one using C#

2 Answers2