CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Steam
Steam

Reputation: 9856

Select into from one sql server into another?

I want to select data from one table (T1, in DB1) in one server (Data.Old.S1) into data in another table (T2, in DB2) in another server (Data.Latest.S2). How can I do this ?

Please note the way the servers are named. The query should take care of that too. That is, SQL server should not be confused about fully qualified table names. For example - this could confuse SQL server - Data.Old.S1.DB1.dbo.T1.

I also want "mapping" . Eg Col1 of T1 should go to Col18 of T2 etc.

Upvotes: 13

Views: 40011

Answers (7)

LoMaPh
LoMaPh

Reputation: 1690

Based on the accepted answer.
Make sure the source server (server1) is linked to the destination server (server2):

SELECT * 
INTO Server2.DB2.dbo.T2
FROM OPENQUERY (server1
                , ' SELECT col_one, col_two, ...
                    FROM DB1.dbo.T1 
                    WHERE ...
                    ...
                );

Upvotes: 1

Steven Mark Ford
Steven Mark Ford

Reputation: 3432

Use Sql Server Management Studio's Import feature.

  1. right click on database in the object explorer and select import
  2. select your source database
  3. select your target database
  4. choose the option to 'specify custom query' and just select your data from T1, in DB1
  5. choose your destination table in the destination database i.e. T2
  6. execute the import

Upvotes: 10

markaaronky
markaaronky

Reputation: 1305

I think you're overcomplicating it by insisting on SQL. In SSMS, right-click the server you want to export from, select "Tasks", "Export", and let the wizard walk you through the steps of selecting your target server and table, which includes mapping all of the columns exactly as you're trying to do with your SQL situation. All the functionality you seem to be looking for is already there.

There's no need for linked servers, SSIS, or anything else to accomplish this. It's already built into SQL Server Management Studio.

Upvotes: 0

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

If it is just one time linked server in appropriate. But if it neede to move data frequently ,replication is better and easier.

Upvotes: -1

Charles Bretana
Charles Bretana

Reputation: 146449

create a linked server. then use an openquery sql statement.

Upvotes: 9

M.Ali
M.Ali

Reputation: 69504

select * into [newtable] from [linked_server].[databasename].dbo.[tablename]

Upvotes: 6

Andrew
Andrew

Reputation: 8703

You could look into this:

Clicky!

Or you could use SSIS, which would probably be much simpler.

Upvotes: -2

Related Questions