Hello, looking for some direction on the best way to run an UPDATE query on a SQL Server DB based on a SELECT query of another DB? My goal is to build an SSIS package to schedule to run this procedure daily.
sSQL = "SELECT libraries.*, genes.name FROM libraries LEFT JOIN genes ON libraries.gene_id = genes.id ORDER BY position;"
I need to use the results of this query to UPDATE another DB table called Matrix.pipeline.
I am guessing I could do something like this:
INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
My issue is that the the SELECT is from a DB on another server (PostgreSQL) via ODBC conn.
The UPDATE is on my SQL2005 server.