April 14, 2017 at 8:28 pm
OK SQL Gurus, riddle me this. I have two SQL Servers, ServerA and ServerB. ServerB has a linked server to ServerA. I want to INSERT data into a table on ServerA from a table on ServerB. The databases on both machines have a sequence object deployed--the object is named the same and has the same starting, increment, and data type values. The table schemas are exactly the same. How do I write an INSERT statement using OPENQUERY so that when the data is inserted into the table on ServerA, the sequence object value comes from ServerA and NOT from ServerB? Here is what I have tried:
INSERT INTO OPENQUERY([ServerA],'SELECT SeqCol, Col1, Col2 from DatabaseA.dbo.TableA WHERE 1=0') --want the ServerA SeqCol value
(SeqCol, Col1, Col2)
SELECT (NEXT VALUE FOR SeqObj) as SeqCol, Col1, Col2 --do NOT want the ServerB SeqCol value
FROM DatabaseB.dbo.TableB
When I execute this statement, the value that goes into the SeqCol column on ServerA is the NEXT VALUE FOR the Sequence Object from ServerB. That is not what I am looking for. I want to INSERT the Col1, Col2 values from DatabaseB.dbo.TableB into DatabaseA.dbo.TableA, but I want the SeqCol column to be the NEXT VALUE FOR the Sequence Object from ServerA. Any thoughts on how I would write the SQL for this?
April 17, 2017 at 8:20 am
Is there some reason you need to run this on Server B ? Why not just reverse the OPENQUERY and solve the problem from the other side of the house, so to speak?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 17, 2017 at 8:28 am
Right now, my DBA will not allow the reverse connection. If there is no way around it, then I might be able to convince him, but I wanted to see if there was some SQL code you could write under this architecture that would achieve the desired results. But you are right, doing this from the other side of the house would be a piece of cake.....
April 17, 2017 at 12:21 pm
If the DBA wants to argue that having the linked server in one direction is OK and from the other, it's not, then get him to explain what's different about having it on the other server. It's not as if the network path will be anything but the opposite direction. The only thing that could be a problem is if that other server is in a remote location with a rather slow speed network link. However, if push comes to shove, then use OPENQUERY to get back the next value from the sequence object and slap it into either a variable or a temp table and then select that as part of the other query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 17, 2017 at 1:24 pm
For sure you can copy the non-sequence-object data from ServerB into a temporary table on ServerA, then use the sequence object on ServerA to add to the temporary table for insert to TableA. But at that point, you have copied the data twice (once from ServerB to temp table on ServerA, then again from temp table to TableA on ServerA) and when you're talking about millions of records, I was hoping to avoid the double copy.
April 17, 2017 at 2:15 pm
T Tschan - Monday, April 17, 2017 1:24 PMFor sure you can copy the non-sequence-object data from ServerB into a temporary table on ServerA, then use the sequence object on ServerA to add to the temporary table for insert to TableA. But at that point, you have copied the data twice (once from ServerB to temp table on ServerA, then again from temp table to TableA on ServerA) and when you're talking about millions of records, I was hoping to avoid the double copy.
Which should reinforce your argument to the DBA about WHY it needs to go the "other way"....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply