Copying data between tables

[gary1975]

Hi

I have 2 databases with similar tables e.g.

[Forestry].[dbo].[ORIGIN_POINTS] (db1)
FID
Actual_Tonnes_Road_2000
Actual_Tonnes_Sea_2000

[SDE_Forestry].[dbo].[SEFE_ORIGINS] (db2)
ID
telNum
Actual_Tonnes_Road_2000
Actual_Tonnes_Sea_2000

The problem that I have is that I want to get Actual_Tonnes_Road_2000 & Actual_Tonnes_Sea_2000 data from db1 and put it into db2. The 2 tables are related by the fields FID and telNum. Unfortunately FID is unique whereas telNum has multiple entries. I think I need to use something like this:

INSERT INTO [SDE_Forestry].[dbo].[SEFE_ORIGINS]([Actual_Tonnes_Road_2000], [Actual_Tonnes_Sea_2000])
SELECT [Actual_Tonnes_Road_2000], [Actual_Tonnes_Sea_2000] FROM [Forestry].[dbo].[ORIGIN_POINTS]

But I need to fit a where into the statement to match up FID and telNum. Any ideas if this can be done in sql without doing seperate code?

Hope this makes sense.

Cheers

Gary

 

[hmckillop]

When you say you want to match up the data, are you planning on doing UPDATEs as opposed to INSERTs.
Which of the rows goes in when multiple matches occur.
Do you only want to insert rows which dont match, and then update all matches?

Sorry need a few of these answered before going further.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[gary1975]

Hi

Thanks for the reponse.

I basically want to update rows from db1 to db2 that are related e.g.

db1:
FID Actual_Tonnes_Road_2000 Actual_Tonnes_Sea_2000
31 8000 6000
32 7890 9809
33 7777 6666
34 2222 1141


db2
FID telNum Actual_Tonnes_Road_2000 Actual_Tonnes_Sea_2000
1 31 NULL NULL
2 31 NULL NULL
3 32 NULL NULL
4 33 NULL NULL

By updating the matching rows I should get:

db2
FID telNum Actual_Tonnes_Road_2000 Actual_Tonnes_Sea_2000
1 31 8000 6000
2 31 8000 6000
3 32 7890 9809
4 33 7777 6666

Hope this helps

Thanks

Gary

 

[gary1975]

I managed to work it out I used this template:

UPDATE #t1 SET #t1.c2 = #t2.c2, #t1.c3 = #t2.c3,
#t1.c4 = #t2.c4
FROM #t2
WHERE #t1.c1 = #t2.c1


Thanks

Gary