copying from one database to another - but only new data?
-
Hi, I'm still fairly new to SQL server 2000
can anyone tell me how to copy data from a table in one database to another, but only data that isn't in the second database?
-
Asssuming there is the same unique key in both tables youcould simply do this using a query like:
INSERT INTO table1
SELECT * FROM table2 WHERE ID NOT IN (SELECT ID FROM table1)
-
many thanks for the quick responce, I'll give that a try, although I was told to obtain the results I want I should really use JOIN, something I haven't even tried or even know where to start !!
-
Uh... if you're new, it'll take a bit of study on your part, but you may want to consider setting up "replication"... then, it's nearly "auto-magic" from there. The steps are much to complex to post here. Start by looking up "replication" in Books OnLine... you will need SA privs to do this. If you don't have them, ask your friendly neighborhood DBA to setup replication for you.
If you are a true believer in brute force, you could setup triggers on the tables to do mimic replication on a more realtime basis at the expense of some performance.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If you want to use a join:
INSERT INTO Table2
SELECT tb1ID, tb1Name, tb1Description
FROM Table1 T1
LEFT OUTER JOIN Table2 T2
ON T1.tb1ID = T2.tb2ID
WHERE tb2id IS NULL
-- the keyword OUTER is optional
If your table is really huge, you might want to test to see which method uses the least resources.
Replication is the answer IF you need to move data on an ongoing basis. If there's not a lot of tables affected, you could do this with a stored procedure and a SQL Agent job. Replication is a bit of a beast. It's not pretty.
-
Many thanks people, I'll give all the suggestions a try
-
Might as well give you the not exists option as well
INSERT INTO table1
SELECT * FROM table2 t2 WHERE NOT exists (SELECT 1 FROM table1 t1 where t1.id = t2.id)
-
Generally speaking you want to avoid using 'IN ({subquery})' as it can be very inefficient. Stick to using the JOIN method.
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
-
Many thanks people, it looks like I need to get my head around Joins
-
What if the database tables have too many fields to allow replication to work? I'm in a similar situation but replication won't work on my databases which were designed by a solution provider so I can't just 'fix' the tables.
I'm going to explain the whole situation in another thread but am not sure if I should use the Backup, Replication or Newbie forum... Any suggestions?
-
The Newbie forum gets a lot of traffic..
-
Try that using a DTS Package..
..hema
-
Then, I'm thinking that someone didn't design the database correctly. It's hard for me to believe that anyone would make a permanent table that has over 520 some odd columns. Like HemaSunder suggested, use DTS instead and fire that solution provider.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply