How to get table tada from another server.

Posted by Johnseelan under Sql Server on 10/1/2012 | Points: 10 | Views : 857 | Status : [Member] | Replies : 3
Hi All,

i have a Server1, server2,

inside server1 i have a DB1,
inside server2 i have a DB2,
in DB1 i have a Table1,
in DB2 i have Table2,
both table have same structure, but Table1 is empty but Table2 have Bulk of Records
i need to fill all the records from Table2 to Table1,
how to DO.
2.suppose Two Database are in Same server means how to transfer data from one table to another.

please help me anyone




Responses

Posted by: Sandeepmhatre on: 10/1/2012 [Member] Starter | Points: 25

Up
0
Down
you can use following query

select * into [server2].database2.dbo.table2 from [server1].database1.dbo.table1

this query will copy table structure and data into other table ie table2

Sandeep M,
Software Developer
Follow me on :
http://sandeepmhatre.blogspot.in

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 10/1/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
John

- You can do this through FOUR part naming convention using Linked Server.
- PULL is the best approach instead of PUSH. So, You can Pull the data from Table2 to Table1...

Validate whether you have valid Linked Server between those server (Server1 and Server2)

- Go to Server1 and do the following...

1. Validate that you can access the Server2 from Server1:
EXEC sp_testlinkedserver N'Server2'

Go
It should return Command(s) completed successfully.

If so, You can do the following approach
Insert Table1

Select * from Server2.DB2.dbo.Table2
Go
If you doesn't have Linked server already configured on that server "Server1". Do the following...

1. Configure Linked Server to Server2
Exec Sp_AddLinkedServer 'Server2'

Go
2. Provide access rights to the Server2
Exec Sp_AddLinkedsrvLogin 'Server2',False,'sa','sa','*****'

Go
3. Validate the Linked server
EXEC sp_testlinkedserver N'Server2'

Go
Now You can use the following script
Insert Table1

Select * from Server2.DB2.dbo.Table2
Note: You can use some other credentials to Server2 instead of 'sa'

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Solijoseph on: 10/1/2012 [Member] Starter | Points: 25

Up
0
Down
Good Reply

Thanks & Regards
Solimon Joseph

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response