We help IT Professionals succeed at work.

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?
Comment
Watch Question

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
--1. create a linked server on the receiving server for the sending server
--2. probably most efficient way is just to truncate the receiving table, then send the whole table from the sending server

--from the receiving server

USE databasename

TRUNCATE TABLE dbo.PODETAIL

INSERT INTO dbo.PODETAIL
SELECT *
FROM SendingServer.databasename.dbo.PODETAIL
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.
CERTIFIED EXPERT
Top Expert 2011

Commented:
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, ...
rwheeler23President

Author

Commented:
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.

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Yeah, any join between two instances is gonna be very slow.  10K rows should be nothing for SQL to insert, even across instances.
CERTIFIED EXPERT
Top Expert 2011

Commented:
sounds like replication would be a solution then...

consider transactional replication.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
I wouldn't get involved with the complexities of replication for this small a number of rows.
rwheeler23President

Author

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.