Inserting from temp table to linked server

Question

Inserting from temp table to linked server

daniness

SSCrazy

Points: 2890
More actions
October 22, 2018 at 11:18 am

#380102

Hi All,
I've been having a painful time with this scenario: building an SSIS package, but to verify results, I've been asked to pull from production copy data sources, which are on another server. After much difficulty I was able to tweak the package to pull from production copy sources and to load the records into the Int server table, and it runs successfully, but I'm not seeing any records in the Int server's table. So I thought maybe a workaround would be to try to import the query's result records that I had in the package, into an .xlsx or a .txt file and then import via the SQL Server Import and Export Wizard, but one of the columns keeps erroring out, even after I tried changing the format multiple times. So now, I'm attempting to import the results of my query into a linked server, but not having much luck...my query populates a temp table, from which I'd like to load the records into the linked server that already exists. Is this even possible to do:
INSERT INTO ##FSEDMEnrollData
SELECT DISTINCT ire.SourceID, ire.SourceCoveragePeriodID...
SELECT * INTO [ServerName].[DatabaseName].[dbo].[StgFSEMonthlyRevenue]
The above server has already been set up as a linked server on the ProdCopy server, per the db admin. Any advice on how to do this would be greatly appreciated. Thanks in advance!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

As you are using SSIS, you should not need to use linked servers to do this.

The direct pull of data from the remote server to the local server, using SSIS, is the way to go – despite the fact that you had difficulties making it work. Are you able to provide any additional info which may throw some light on why this did not work?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 2

Hmmmmm.... pulling data from production and putting it on another server... sounds like a security issue just waiting to happen to me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

daniness SSCrazy Points: 2890 More actions · Answer 3

Thanks guys for your replies. @JeffModen, it's actually not a production server, but rather a copy of the data from a production server. So this seemed to have done the trick....I haven't worked too much with linked servers, so didn't know it's as simple as this:

INSERT INTO [ServerName].[Database].[dbo].[StgFSEMonthlyRevenue]

SELECT * FROM ##FSEDMEnrollData

Thanks all for your input!