Combining Similar Databases on network

[DajOne]

I have 3 similar databases on 3 different servers and need combined reports from all the fields . I can link them to a 'merged' database but queries are impossible due ot the lack of lien.

How can I merge these databases into one?

P.S. I have tried
INSERT INTO [merged].[*]
select [one].[*],[two].[*], [three].[*]
FROM [one],[two],[three]

without success...

Please any help will be appreciated

 

[oharab]

I'm not entirely sure what you mean, but if you have 3 databases, set up the same, but with different data in ,then what you need is a union query.
In your reporting package, create linked tables to all 3 dBs.
Then create a new query something like:

SELECT lnk1Table1.Data1,lnk1Table1.Data2,lnk1Table1.Data3
UNION
SELECT lnk2Table1.Data1,lnk2Table1.Data2,lnk2Table1.Data3
UNION
SELECT lnk3Table1.Data1,lnk3Table1.Data2,lnk3Table1.Data3;

where lnk1Table1,lnk2Table1 and lnk3Table1 are your 3 linked tables.

You can create any select statement you like for each join, including subqueries and where clauses. The only thing you have to make sure is that each unioned bit has the same number of fields.
If there are less fields in any of the unioned bits then you must add dummy fields ("" AS Dummy1, "" AS Dummy2, etc) to make everything equal.


HTH

Ben

----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------