SQL Insert/Update Query
I am trying to move away from using cursors in SQL and I was wondering what is the most efficient way to accomplish this?
I have a PO Line table called PODETAIL where the PK is PONUMBER,POLINE. I have this two in two different instances of SQL. I want to insert/update the records from instance A to instance B. If I were to use a cursor I would have a select statement looking for existing records in instance B. If it did not exist, I do an insert statement, if it did exist I would do an update statement. How do you accomplish the same thing without cursors?
I have a PO Line table called PODETAIL where the PK is PONUMBER,POLINE. I have this two in two different instances of SQL. I want to insert/update the records from instance A to instance B. If I were to use a cursor I would have a select statement looking for existing records in instance B. If it did not exist, I do an insert statement, if it did exist I would do an update statement. How do you accomplish the same thing without cursors?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott Pletcher
The overhead of checking all the key values then doing INSERTs and UPDATEs (in SQL 2005 you can't do MERGE) would probably be higher than just sending the whole table ... UNLESS you have very few INSERTs and/or UPDATEs as a percent of the table.
can you give us more background to your requirement...
e.g. same/different servers?
why the two instances?
regular/one-off?
is there data on the tables which you don't intend to "maintain" across both databases?
have you considered using replication to maintain the data...?
will the updates be required in both directions?
what is the nature of the systems OLTP or Data warehouse, ...
e.g. same/different servers?
why the two instances?
regular/one-off?
is there data on the tables which you don't intend to "maintain" across both databases?
have you considered using replication to maintain the data...?
will the updates be required in both directions?
what is the nature of the systems OLTP or Data warehouse, ...
ASKER
I have been unsuccessful in convincing the owners to get rid of the old instance of SQL. Personally I think it is because there is an old Access legacy application wrapped around it and they do not want to spend the time and money to upgrade it.
I only have 4 tables I care about and the total record count of all 4 tables is less than 10,000. The truncate table idea is a great one. Each night the tables in the receiving instance need to get completely overwritten by what is in the sending instance. The updates will always be just one way and that is to the receiving instance. The receiving instance is a SQL 2005 instance and the data is coming from a SQL 2000 instance. The link server already exists and I have a query that joins the two instances and the performance is doggedly slow. So I want to move these 4 tables into the SQL 2005 instance and change my query to stay within the SQL 2005 instance and hence, boost performance.
I only have 4 tables I care about and the total record count of all 4 tables is less than 10,000. The truncate table idea is a great one. Each night the tables in the receiving instance need to get completely overwritten by what is in the sending instance. The updates will always be just one way and that is to the receiving instance. The receiving instance is a SQL 2005 instance and the data is coming from a SQL 2000 instance. The link server already exists and I have a query that joins the two instances and the performance is doggedly slow. So I want to move these 4 tables into the SQL 2005 instance and change my query to stay within the SQL 2005 instance and hence, boost performance.
Yeah, any join between two instances is gonna be very slow. 10K rows should be nothing for SQL to insert, even across instances.
sounds like replication would be a solution then...
consider transactional replication.
consider transactional replication.
I wouldn't get involved with the complexities of replication for this small a number of rows.
ASKER
The initial suggestion to just truncate the and copy over the records is the way to go with such a small set of records. Thanks for everyone's suggestions.