x

How i can copy records from one server to other server.There is no linked server between them.

Hello,

I have a requirement to write a script to copy records from one server to other server .There is no linked server between them.It should be done by using temp table.Can anyone help me in this?

server:sql1\pr,database:db1.dbo.table1
server:sql1\pr,database:db1.dbo.table2
to 
server:sql2\dv,database:db1.dbo.table1
server:sql2\dv,database:db1.dbo.table2

I should use a temp table to copy data from server:sql1\pr,database:db1.dbo.table1

and should check like the records doesn't already exist using a left join.
more ▼

asked Nov 08 '10 at 08:51 AM in Default

swethaashwini gravatar image

swethaashwini
223 16 18 20

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

If you want to do it in T-SQL, you really could do with using a linked server. Otherwise you can investigate using OPENROWSET to use a connection string.

So, something like

SELECT * INTO #TempTable 
  FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_connection=yes;', 
                             'SELECT * FROM [TestDatabase].[dbo].[tblTest]')
However, if you can do it using other tools, then you will find that SSIS is probably a much better bet.
more ▼

answered Nov 08 '10 at 09:12 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

I should write a script to do it.

can you be more clear for the above query..I am new to sqlserver.
Nov 08 '10 at 09:17 AM swethaashwini

@Matt Whitfield SSIS is a much better bet indeed. Another option could be to have a look at the [article by YHR][1] first published by SSC in the summer of 2009. The script generating inserts still has some problems (it does not handle some data types, for example evil float, properly), but it is worthy of considering if there are no other options. And I get to pimp my article too by posting this comment :)

[1]: http://www.sqlservercentral.com/articles/T-SQL/66987/
Nov 08 '10 at 09:21 AM Oleg

That covers all the best methods.

There are at least a half dozen other options, but I think all the others are clearly inferior to SSIS, creating the needed linked server, or using openrowset/opendatasource (in that order of preference).
Nov 08 '10 at 09:25 AM TimothyAWiseman

SELECT * INTO #TempTable FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_connection=yes;', 'SELECT * FROM [TestDatabase].[dbo].[tblTest]')

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

IT GAVE THE ERROR...WHEN I EXECUTED THE ABOVE STATEMENT...PLEASE HELP ME OUT
Nov 08 '10 at 09:43 AM swethaashwini

@swethaashwini There are 2 problems. First, your server does not allow running so-called Ad Hoc Distributed Queries. This should be addressed like this:

sp_configure 'Show Advanced Options', 1;
reconfigure with override;
go

sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure with override;
go
Once this is done, you will be able to use openrowset queries, but you do need to change the second parameter to the actual connection string. What Matt gave you in his answer is just an example, you have to modify it according to your needs.
Nov 08 '10 at 09:48 AM Oleg
(comments are locked)
10|1200 characters needed characters left

If you need to copy a data more then once, try to use a 3rd party tool, such as [SQL Data Examiner][1] or SQL Data Compare.

[1]: http://www.sqlaccessories.com/SQL_Data_Examiner/
more ▼

answered Nov 17 '10 at 11:55 AM

SQLDev gravatar image

SQLDev
11

(comments are locked)
10|1200 characters needed characters left
My Dear Freind you can use Import Export utility in SQL server to Copy record from one server to another..
more ▼

answered Nov 10 '10 at 12:34 PM

basit 1 gravatar image

basit 1
449 49 61 81

Yes, If you are nt familiar with the SSIS then its better you use the Import and Export wizard which is quite simple (assuming you dont need to do any data transformations )
Nov 18 '10 at 02:08 AM aRookieBIdev
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x341

asked: Nov 08 '10 at 08:51 AM

Seen: 2236 times

Last Updated: Nov 08 '10 at 12:30 PM