Copying Between Databases Without Linked Servers?

Then my requirements are too narrow then.

What is the "tightest" way to create a linked server temporarily and
then drop it at the end of the script?

I am open to not using "select into" if that is my problem. I really
need to do an insert and then update from one DB table to another
(same name, same column schema), so whatever will get me there without
having to leave the SSMS session in 'pure tsql' is cool.

Thanks.



On Wed, 13 Jan 2010 12:48:13 -0500, "Scott Morris"
wrote:

>"SnapDive" wrote in message
>
>>I have a SQL Server 2005 machine called "Dest" and a 2008 machine
>> called "Source" and a 2008 machine called "Console". Each has the same
>> SQL account with appropriate permissions and rights on all 3 to do
>> anything.
>>
>> Without using a linked server, how can I sit at "Console" and execute
>> a "select into" fromthe "Source" database to the "Dest" database?
>
>You can't - assuming that the equivalent use of openquery is not allowed
>either. There are many ways to transfer data, but your requirement to use
>"select into" is the limiting factor here.
>
 >> Stay informed about: Copying Between Databases Without Linked Servers? 

Not sure why you need to add and drop it rpeatedly, but look up
sp_addlinkedserver and sp_dropserver in BOL

SnapDive wrote:
> Then my requirements are too narrow then.
>
> What is the "tightest" way to create a linked server temporarily and
> then drop it at the end of the script?
>
> I am open to not using "select into" if that is my problem. I really
> need to do an insert and then update from one DB table to another
> (same name, same column schema), so whatever will get me there without
> having to leave the SSMS session in 'pure tsql' is cool.
>
> Thanks.
>
>
>
> On Wed, 13 Jan 2010 12:48:13 -0500, "Scott Morris"
> wrote:
>
>> "SnapDive" wrote in message
>>
>>> I have a SQL Server 2005 machine called "Dest" and a 2008 machine
>>> called "Source" and a 2008 machine called "Console". Each has the
>>> same SQL account with appropriate permissions and rights on all 3
>>> to do anything.
>>>
>>> Without using a linked server, how can I sit at "Console" and
>>> execute a "select into" fromthe "Source" database to the "Dest"
>>> database?
>>
>> You can't - assuming that the equivalent use of openquery is not
>> allowed either. There are many ways to transfer data, but your
>> requirement to use "select into" is the limiting factor here.

--
HTH,
Bob Barrows
 >> Stay informed about: Copying Between Databases Without Linked Servers? 

"SnapDive" wrote in message

> Then my requirements are too narrow then.
>
> What is the "tightest" way to create a linked server temporarily and
> then drop it at the end of the script?
>
> I am open to not using "select into" if that is my problem. I really
> need to do an insert and then update from one DB table to another
> (same name, same column schema), so whatever will get me there without
> having to leave the SSMS session in 'pure tsql' is cool.

"Tightest"? "Without having to leave the SSMS session in 'pure tsql'"? I
don't really understand what you mean with these terms and phrases. The
biggest problem is that you are defining "how" you want to do something, and
not "what" you want to accomplish.

First, let's review something that you might be missing (or perhaps contains
a piece of information that isn't obvious to me).

You said (rephrasing a bit):
> I have a SQL Server 2005 machine called "Dest" and a 2008 machine
> called "Source" and a 2008 machine called "Console".
> How can I sit at "Console" and move data from "Source" to "Dest"?

The fact that you are physically at "Console" is mostly irrelevent from a
SSMS perspective. You can connect to any SQL Server from any physical point
in your network. The more important point is the server instance to which
you are connected (from within SSMS or any other application).

Based on your terms and usage, I get the impression that you are looking for
a rather simple set of steps to merge data - perhaps because there is some
unfamiliarity with sql server or tsql. Your comments also imply that this is
a temporary thing - while "script" implies that it will be done multiple
times. These facets of the issue will affect the possible solutions - you
may want to mull these over some more.

In any event, the SQL Server documentation is the first place to start.
There are tools provided by SQL Server for doing these things. Creating a
linked server is one option (even temporarily), but it may not be the best
option. The amount of data to be merged, the speed of the network, the
complexity of the rules involved in merging data all have an impact on the
selection of a solution. MS provides SSIS (sql server integration services)
to handle things of this type. There are also many ways to export data from
and import data to a database/table. SSIS can do this, as can bcp, bulk
insert, insert/openrowset. You should review the following (just paste it
into your browser url) BOL documentation to get an idea of their
capabilities.
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/50d19cab-1830-4f70-8df1-2450ce887805.htm
At the bottom of this page is a link to "Scenarios for bulk importing and
exporting data", which will lead you to the topic of "Copying data between
servers" (among others). All very good sources of information. Reading
that may help clarify / quantify your situation.

In BOL, some additional useful index topics:
openquery
openrowset
replication
importing data
exporting data

Perhaps now is a good time to stress the importance of a good disaster
recovery plan. If you lose or alter your data (and it is pretty easy to
do - trust me!), do you have a proper (and tested) backup strategy that will
allow you to recover? This is important when you are attempting to
move/merge data between databases and accidentally update every row with an
incorrectly written update statement (or delete statement).
 >> Stay informed about: Copying Between Databases Without Linked Servers? 

I suggest you check out the OPENROWSET() and OPENDATASOURCE() functions.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"SnapDive" wrote in message

> Then my requirements are too narrow then.
>
> What is the "tightest" way to create a linked server temporarily and
> then drop it at the end of the script?
>
> I am open to not using "select into" if that is my problem. I really
> need to do an insert and then update from one DB table to another
> (same name, same column schema), so whatever will get me there without
> having to leave the SSMS session in 'pure tsql' is cool.
>
> Thanks.
>
>
>
> On Wed, 13 Jan 2010 12:48:13 -0500, "Scott Morris"
> wrote:
>
>>"SnapDive" wrote in message
>>
>>>I have a SQL Server 2005 machine called "Dest" and a 2008 machine
>>> called "Source" and a 2008 machine called "Console". Each has the same
>>> SQL account with appropriate permissions and rights on all 3 to do
>>> anything.
>>>
>>> Without using a linked server, how can I sit at "Console" and execute
>>> a "select into" fromthe "Source" database to the "Dest" database?
>>
>>You can't - assuming that the equivalent use of openquery is not allowed
>>either. There are many ways to transfer data, but your requirement to use
>>"select into" is the limiting factor here.
>>
>
 >> Stay informed about: Copying Between Databases Without Linked Servers? 

Thanks everyone for their input, I am set with what I need.


On Wed, 13 Jan 2010 11:52:59 -0500, SnapDive
wrote:

>I have a SQL Server 2005 machine called "Dest" and a 2008 machine
>called "Source" and a 2008 machine called "Console". Each has the same
>SQL account with appropriate permissions and rights on all 3 to do
>anything.
>
>Without using a linked server, how can I sit at "Console" and execute
>a "select into" fromthe "Source" database to the "Dest" database?
>
>
>Thanks.
>
 >> Stay informed about: Copying Between Databases Without Linked Servers?