Moti Mashiah
asked on
SQL and C#
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
just wanna mention the Note and status columns are pointing to textboxes.
ASKER
i'm going to store just one item per status/note
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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();
}
ASKER
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:
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();
}
ASKER
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.
string Query = "update RMSCRM.dbo.sales set where ID=@id";
this line give me an issue with the syntax.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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 )";
Capture.JPG
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
before these columns was belong to the sale table and was updated fine:
now my tables diagram look like (screenshot)
Capture.JPG
now my tables diagram look like (screenshot)
Capture.JPG
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you sooo much I will try it tomorrow the first thing in the morning.
ASKER
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.
I just wondering if no is the answer I will copy the database to the same server.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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,acco untnumber
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
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,acco
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will say it again he is amazing help ever.
ASKER
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:
Open in new window
Capture.JPG