Solved

SQL and C#

Posted on 2014-04-07
25
212 Views
Last Modified: 2014-04-08
Hi ,

I have 2 SQL server 2012.

The first SQl server hold  database called "rmsmasterdata".
The second server hold  database called "rmscrm"

I would like to copy couple of column from two tables to the "rmscrm" from rmsmasterdata.

I'm looking for the SQL command to do this copy and schedule it to do it everyday.
0
Comment
Question by:Moti Mashiah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 10
25 Comments
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39984172
As long as they are on the same server, and you have a user account with permission to both, you simply need to use a fully qualified name of the table:
insert into rmscrm.dbo.yourtable ( <column_list> )
    select ( <column_list> ) from rmsmasterdata.dbo.yourothertable

Open in new window

To get it to run every day you can create a SQL Server Agent Job to run the query.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984265
WOW that easy thank you very much Carl.

I have another question regarding table that I added to my database.

Actually as you remember I worked with two tables "sales" and "History" now I added another one table which called Notes and assigned the FK_salesID to make the relationship between the tables history and sale(see attachment) the purpose I joined this table is because I would like to delete the columns "status and status1" from the sales table and work with these columns in the notes.

Now my challenge is how to change the code.... I was trying couple of thing but it is not really working.

I have to change it in two places in the update button and combobox

I was trying something like this in the update button:

//update button updating status, status1, history + send email
        private void button1_Click(object sender, EventArgs e)
        {

            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
            //string Query = "update RMSCRM.dbo.sales set Status=@Status, Status1=@Status1 where ID=@id";
            string Query = "update RMSCRM.dbo.sales set, where ID=@id";
           

            SqlConnection Myconn = new SqlConnection(conn);
            SqlCommand cmd = new SqlCommand(Query, Myconn);

            int SalesID = int.Parse(this.txtid.Text);

            cmd.Parameters.AddWithValue("@id", SalesID);


            //cmd.Parameters.AddWithValue("@Status", this.txtstatus.Text);
            //cmd.Parameters.AddWithValue("@Status1", this.txtStatus1.Text);
          
          
            

            try
            {
                Myconn.Open();
                //execute the query to update the header
                cmd.ExecuteNonQuery();

                //build and execute query to delete current history
                cmd.CommandText = "DELETE FROM History WHERE SalesID=@SalesID";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@SalesID", SalesID);
                cmd.ExecuteNonQuery();

                //build and execute queries to re-add history
                for (int i = 0; i < listBox1.Items.Count; ++i)
                {
                    cmd.CommandText = "INSERT INTO [history],[notes] ( [SalesID], [History], [note], [status] ) VALUES ( @SalesID, @History, @note, @status )";
                    //cmd.CommandText = "INSERT INTO [history] ( [SalesID], [History] ) VALUES ( @SalesID, @History )";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@SalesID", SalesID);
                    cmd.Parameters.AddWithValue("@History", listBox1.Items[i].ToString());
                    cmd.Parameters.AddWithValue("@note", txtnote.Text);
                    cmd.Parameters.AddWithValue("@status", txtStatus.Text);

                    cmd.ExecuteNonQuery();
                }

                MessageBox.Show("Updated Client status see email approval");


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Myconn.Close();
            }

Open in new window

Capture.JPG
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39984381
You can't insert into the two tables in a single statement, so you need to execute a second query to insert into your Notes table.

How you go about it depends on how you intend to manage the notes data (is it editable, or do you just need to add extra rows? are you storing multiple per sales item? etc). Although if you only had one Status/Notes entry per Sales record, then those columns would be better off in the Sales table anyway.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984385
just wanna mention the Note and status columns are pointing to textboxes.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984396
i'm going to store just one item per status/note
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39984403
If by that you mean there is a one-to-one relationship between the Status/Notes and the Sales header, then you should move those two columns to the Sales table.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984417
K i did this change:

//build and execute queries to re-add history
                for (int i = 0; i < listBox1.Items.Count; ++i)
                {
                    
                    cmd.CommandText = "INSERT INTO [history] ( [SalesID], [History] ) VALUES ( @SalesID, @History )";
                    cmd.CommandText = "INSERT INTO [notes] ( [SalesID], [note], [status] ) VALUES ( @SalesID, @note, @status )";
                    cmd.Parameters.Clear();
                 
                    cmd.ExecuteNonQuery();
                }

Open in new window

0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984425
now I got issue with the syntax when I click on update button:

As my understanding I don't need to add anything on the query except this:

private void button1_Click(object sender, EventArgs e)
        {

            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
            //string Query = "update RMSCRM.dbo.sales set Status=@Status, Status1=@Status1 where ID=@id";
            string Query = "update RMSCRM.dbo.sales set where ID=@id";
           

            SqlConnection Myconn = new SqlConnection(conn);
            SqlCommand cmd = new SqlCommand(Query, Myconn);

            int SalesID = int.Parse(this.txtid.Text);

            //cmd.Parameters.AddWithValue("@id", SalesID);
            //cmd.Parameters.AddWithValue("@Status", this.txtstatus.Text);
            //cmd.Parameters.AddWithValue("@Status1", this.txtStatus1.Text);
          
          
            

            try
            {
                Myconn.Open();
                //execute the query to update the header
                cmd.ExecuteNonQuery();

                //build and execute query to delete current history
                cmd.CommandText = "DELETE FROM History WHERE SalesID=@SalesID";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@SalesID", SalesID);
                cmd.ExecuteNonQuery();

                //build and execute queries to re-add history
                for (int i = 0; i < listBox1.Items.Count; ++i)
                {
                    
                    cmd.CommandText = "INSERT INTO [history] ( [SalesID], [History] ) VALUES ( @SalesID, @History )";
                    cmd.CommandText = "INSERT INTO [notes] ( [SalesID], [note], [status] ) VALUES ( @SalesID, @note, @status )";
                    cmd.Parameters.Clear();
                 
                    cmd.ExecuteNonQuery();
                }

Open in new window

0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984428
sorry I meant to cop just this one:

string Query = "update RMSCRM.dbo.sales set where ID=@id";

this line give me an issue with the syntax.
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39984449
I'm not quite sure what you are trying to achieve at the moment. Where does the update to the Sales table come into things?

Based on what you said about there only being one set of Status/Notes for the Sales item, I would move those two columns to the Sales table. The you just need to update the column list and parameters for the Update query on the sales table to include the Notes and Status fields.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984492
K, I have confusion here sorry let me try to explain it better.

What I'm trying to achieve now is to update the new related table notes that contains two columns note and status these columns updates just one time for each item for example I don't need to update these columns they why I updating the history as these columns just update one line.

for now I have added this insert command under "for method":
I attached some screenshot maybe it will help to understand my thought.

cmd.CommandText = "INSERT INTO [history] ( [SalesID], [History] ) VALUES ( @SalesID, @History )";
                    cmd.CommandText = "INSERT INTO [notes] ( [SalesID], [note], [status] ) VALUES ( @SalesID, @note, @status )";

Open in new window

Capture.JPG
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39984566
I get what you are trying to do, i just don't quite understand why you have the notes and status in a separate table. IF you have those two columns in a separate table then it gives you the additional problem that you need to know if you already have a row available that needs updating or, if it's a new sales record, if you need to add an extra row.

Assuming you're keeping them in a separate table, and that the record already exists, you would be looking at something like:
private void button1_Click(object sender, EventArgs e)
        {

            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
            
            // define query to update notes table
            string Query = "update RMSCRM.dbo.Notes set Status=@Status, Notes=@Notes where SaledID=@id";
           
            SqlConnection Myconn = new SqlConnection(conn);
            SqlCommand cmd = new SqlCommand(Query, Myconn);

            int SalesID = int.Parse(this.txtid.Text);

            // add parameters
            cmd.Parameters.AddWithValue("@id", SalesID);
            cmd.Parameters.AddWithValue("@Status", this.txtstatus.Text);
            cmd.Parameters.AddWithValue("@Notes", this.txtnotes.Text);
          
                Myconn.Open();

                // execute the query to update the notes table
                cmd.ExecuteNonQuery();

                ....

                 // rest of code to update history

Open in new window

0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984574
before these columns was belong to the sale table and was updated fine:

now my tables diagram look like (screenshot)
Capture.JPG
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984592
i have decided to separate these columns because we are going to copy and replace databases from the master sales table to the sales table that the application point to.

so my concern is that when the DB guy will do this copy it will delete also these two columns note and status...so that's why I have created another table and put them there.

Let me know if is it other better way to do it.
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39984602
There's no reason why copying between two databases should override the status and notes columns. You can quite easily copy between databases and restrict the column list if you don't want the whole row updating.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984637
I didn't know that, I was sure the copy is going through the private key and update all the row.

Do you have the code how To copy databases with the restrict columns?

Thank u soo much I think that way will be much better for us.
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39984679
Copying between tables you specify the column list like you do with any other query. So say, for example, you wanted to copy only the Customer and Account columns from a sales table in one database to the sales table in another:
UPDATE dbo.Sales
    SET Customer = t1.Customer,
        Account = t1.Account
    FROM rmsmasterdata.sales t1
       INNER JOIN dbo.Sales ON t1.SalesID = dbo.Sales.SalesID

Open in new window

0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984690
Thank you sooo much I will try it tomorrow the first thing in the morning.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984694
Sorry just one more things can I do that between two SQL servers as my databases sits in a separate SQL server.

I just wondering if no is the answer I will copy the database to the same server.
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39984704
You can, but you'll need to set it up as a linked server: http://msdn.microsoft.com/en-us/library/ff772782.aspx
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39984985
Hi Carl ,

I have tested the code you provided me above from my individual SQL server.

it looks like this:
note: pay attention that I changed the saledID to ID because SQL couldn't find salesID-FK

UPDATE dbo.Sales
    SET Lastname = t1.Lastname,
        AccountNumber = t1.AccountNumber
		

    FROM rmsmasterdbtest.dbo.Customer t1
       INNER JOIN dbo.Sales ON t1.ID = dbo.Sales.ID

Open in new window


After I updated I saw just two row in my copied table actually it update the rows was created already and didn't create more rows in order to copy all the table.

I have another challenge in this case actually I have to copy from two table to one table

Example:

I have database with order and Customer tables.
order table columns - Time,Deposit
customer table columns - Lastname,AccountNumber

I have to copy these columns to the sales table columns - time,deposit,lastname,accountnumber
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39985379
What are they key fields on the Customer and Order tables? i.e. the column that links them to each other, and also allows you match them to your Sales table.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39986583
HI Carl ,

Thank you very much for the great help I have moved the issue with the databases to our db guy for now.

I really have hard time to pick the two world together DB and C#.

I wish you will be here in my next question.
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39986600
No problem. Transferring data between servers should fall under the DBA's remit anyway :)
0
 
LVL 1

Author Closing Comment

by:Moti Mashiah
ID: 39986620
I will say it again he is amazing help ever.
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question