August 6, 2004 at 6:47 am
I have decided to convert some very lenghthy stored procedures to a .net app to give us more flexibility and a web based user interface for a Nightly billing process. The steps I need to take are
1 - Query our production database for a resultset
2 - I am SELECTING INTO a new Table, However I want to SELECT INTO a new TABLE in a different database on a different server.
This Query works great :
<!-- Query Executed from destination Server (SERVER2) -->
SELECT * INTO Buffer_tb
FROM SERVER1.MYDB.dbo.Buffer_tb
However, here is the actual query that works great when it is executed on the production server and the new table is in the original database. I know I can make this a two step process by executing my query, then moving the new table but I would like to know if I can just SELECT INTO the new table on the destination server
SELECT Interchange_tb.InterchangeKEY,
Interchange_tb.NbrBytes,
Interchange_tb.ControlNumber,
Interchange_tb.Agency,
Track_tb.TransactionSetID,
ExtDataXref_tb.ByteCount,
Partner_tb.EDICode,
Partner_tb.PartnerKEY,
Interchange_tb.TimeCreated,
Lookup_tb.Text1,
Lookup_tb.Text2
Into Buffer_tb
From Interchange_tb
LEFT JOIN Track_tb ON Track_tb.InterchangeKey = Interchange_tb.InterchangeKEY
RIGHT JOIN Document_tb ON Document_tb.DocumentKEY = Track_tb.DocumentKEY
RIGHT JOIN ExtDataXref_tb ON ExtDataXref_tb.GentranKEY = Document_tb.DocumentKEY
RIGHT JOIN Partner_tb ON Partner_tb.PartnerKEY = Interchange_tb.PartnerKEY
RIGHT JOIN Lookup_tb ON Lookup_tb.PartnerKEY = Interchange_tb.PartnerKEY
WHERE (Interchange_tb.TimeCreated BETWEEN 1078929199 AND 1078980201)
AND (ExtDataXref_tb.GentranType = 1)
AND (Lookup_tb.Item = 'BILLING')
Thank you
August 6, 2004 at 3:53 pm
Hi Chris -
Is your issue that performance is slow or that the actual query doesn't work at all?
Might take a look at the SQL Books Online page below. It shows how the data will be handled from one server to another. I've seen instances where the entire dataset is pulled to the local server before a where clause is evaluated and data is discarded.
Optimizing Distributed Queries
Queries involving the following are never delegated to a provider and are always evaluated locally:
bit
uniqueidentifier
Michelle
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply