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


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?


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, 2010 at 08:51 AM in Default

avatar image

223 18 18 23

(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, 2010 at 09:12 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

I should write a script to do it.

can you be more clear for the above query..I am new to sqlserver.

Nov 08, 2010 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 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 :)

Nov 08, 2010 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, 2010 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.


Nov 08, 2010 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, 2010 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 or SQL Data Compare.

more ▼

answered Nov 17, 2010 at 11:55 AM

avatar image


(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, 2010 at 12:34 PM

avatar image

basit 1
509 57 65 91

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, 2010 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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Nov 08, 2010 at 08:51 AM

Seen: 3389 times

Last Updated: Nov 08, 2010 at 12:30 PM

Copyright 2018 Redgate Software. Privacy Policy