Post reply

How can I load the data in the local server into one remote server?

  • April 15, 2011 at 3:09 am

    #238605

    Hi everyone,

    Recently I want to load data from one local server to one remote server via T-SQL. and I don't know the structure of data table in local server. So I tried using the way like Select * into remotelinkedserver.dbname.dbo.tblname from localdbname.dbo.tablname, but in vain. Seems that I have to create the table on the remote server firstly, and then insert the data into it, right? But what can I do if I don't know the local table structure? My current solution is that we can get the column name, datatype, is_nullable info forlocal table, and then combain all the info into one varchar to create one same structure table on remote server, and insert the data from local server to remote server at last. Do you have any other solution? I only want to ask some suggestion to get more easy way to implement my requirement. Thanks a lot:)

    Thanks

    Lindsay

  • April 15, 2011 at 3:30 am

    #1312001

    Why not back up and restore the database?

    Alternatively if you have shiny new empty database, why not use the Import data wizard under Tasks on the Target Database?

    SSIS will create the tables and columns and move the data, however you need to be carefull with table locks if the database is operational.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • April 15, 2011 at 4:28 am

    #1312025

    Thanks for your response Jason-299789.

    Because this is only one step in the SP, I want to create this SP and then user can call it easily. I know the SSIS wizard can help us import/export. but it is not for our SP

    Thanks

    Lindsay

  • April 15, 2011 at 4:40 am

    #1312027

    In that case what you need to do is parse the Metadata in either the INFORMATION_SCHEMA.COLUMNS view or build your own script from the sys.Tables and sys.Columns that will construct the Create Table script on the remote server, assuming you have permissions to do it via the Linked Server.

    Then build a dynamic SQL script that that will run an INSERT INTO with a SELECT.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • April 15, 2011 at 6:02 am

    #1312056

    you can use the EXECUTE AT feature of a linked server to create a table, then use an insert with 4 part nameingo convention to put local data in the remote server:

    --adding a simple default SQL server linked server

    EXEC sp_addlinkedserver 'PROD2K8', 'SQL Server'

    GO

    --create a table on the remote

    EXECUTE ( 'CREATE TABLE Sandbox.dbo.SalesTable

    (SalesID int, SalesName varchar(20)) ; ' ) AT PROD2K8;

    --insert data from local to remote

    INSERT INTO PROD2K8.Sandbox.dbo.SalesTable(SalesName)

    SELECT Sname FROM Customers

    remote server:

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 15, 2011 at 7:14 am

    #1312112

    Thanks a lot Jason and Lowell.

    Seems that we have to use Jason's suggestion to get column's info if I don't know the structure of the local table.:)

    Thanks

    Ling

Viewing 6 posts - 1 through 5 (of 5 total)

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