copying data between two databases

im trying to copy data from a table on one server onto another server
the scenerio is
Source
SQL1 ( server ) TestData ( database) MyTable ( table )
Destionation
SQL2 ( server ) TestData ( database) MyTable ( table )
However there is a twist, i dont want any of the records duplicatedHi,
If the table already exists:
INSERT INTO SQL1.TestData..MyTable
(
columnlist(..)
)
SELECT
columnlist(...)
>From SQL1.TestData..MyTable S1
INNER JOIN SQL2.TestData..MyTable S2
ON S1.Column = S2.Column
The column list has to be redefined by you based on your needs.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Peter
Assume you have already created linked server between two
INSERT INTO SQL1.TestData.dbo.MyTable SELECT *
FROM SQL2.TestData.dbo.MyTable
WHERE NOT EXISTS (SELECT * FROM SQL2.TestData.dbo.MyTable S2
WHERE S2.PK=SQL1.TestData.dbo.MyTable.PK)
Note: PK is a Primary Key
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:93555303-F5B9-4786-8ADB-2C608CF5BE0C@.microsoft.com...
> im trying to copy data from a table on one server onto another server
> the scenerio is
> Source
> SQL1 ( server ) TestData ( database) MyTable ( table )
> Destionation
> SQL2 ( server ) TestData ( database) MyTable ( table )
> However there is a twist, i dont want any of the records duplicated|||There are two approaches you an do:
1) Copy the data over to a work table and then do an INSERT SELECT... WHERE
NOT EXISTS. Drop the work table when you're done.
2) Leave the data on the source server and do the INSERT SELECT... WHERE NOT
EXISTS.
In both cases, you'll need to set up a linked server. However, you could
also bcp out the data from the source and bcp it back into the target for
approach #1. In that case, you would not need a linked server. Linked
servers are slow, so I'd avoid approach #2.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:93555303-F5B9-4786-8ADB-2C608CF5BE0C@.microsoft.com...
im trying to copy data from a table on one server onto another server
the scenerio is
Source
SQL1 ( server ) TestData ( database) MyTable ( table )
Destionation
SQL2 ( server ) TestData ( database) MyTable ( table )
However there is a twist, i dont want any of the records duplicated|||Sorry should be an outer join with a NULL Check or Exists or "not in".
-Jens.|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eFYCOfyUGHA.5808@.TK2MSFTNGP12.phx.gbl...
> There are two approaches you an do:
> 1) Copy the data over to a work table and then do an INSERT SELECT...
> WHERE
> NOT EXISTS. Drop the work table when you're done.
> 2) Leave the data on the source server and do the INSERT SELECT... WHERE
> NOT
> EXISTS.
> In both cases, you'll need to set up a linked server. However, you could
> also bcp out the data from the source and bcp it back into the target for
> approach #1. In that case, you would not need a linked server. Linked
> servers are slow, so I'd avoid approach #2.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:93555303-F5B9-4786-8ADB-2C608CF5BE0C@.microsoft.com...
> im trying to copy data from a table on one server onto another server
> the scenerio is
> Source
> SQL1 ( server ) TestData ( database) MyTable ( table )
> Destionation
> SQL2 ( server ) TestData ( database) MyTable ( table )
> However there is a twist, i dont want any of the records duplicated
>
One point to make about the above, use a Select Distinct, to make sure that
you only select one instance of a row.
Colin.