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

Question

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

lindsaywang

SSCrazy

Points: 2569
More actions
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

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

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

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

lindsaywang SSCrazy Points: 2569 More actions · Answer 2

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

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

Lowell SSC Guru Points: 323518 More actions · Answer 4

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!

lindsaywang SSCrazy Points: 2569 More actions · Answer 5

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