Inserting from temp table to linked server

  • daniness

    SSCrazy

    Points: 2890

    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!

  • Phil Parkin

    SSC Guru

    Points: 244578

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeff Moden

    SSC Guru

    Points: 996622

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • daniness

    SSCrazy

    Points: 2890

    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!

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

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