April 13, 2009 at 11:48 am
Hi Everyone
I have a scenario in Sql Server where we have a same table(A) in 6 different databases.
The table A has a column named "status" which is updated from the application side .
my question is how can we pool the data from all the 6 tables in 6 different databases in to another database if there is a change in the status field.
Thanks in advance.
April 13, 2009 at 11:57 am
I'm not sure oif the requirement.
Do you want to be able to see all the statuses in Database7, if one gets added in Database5 for example, or do you want a value to change in Database7?
USE Database7
Create View MyStatus
As
SELECT * FROM Database1.dbo.TableA UNION --not union all..remove dupes?
SELECT * FROM Database2.dbo.TableA UNION
SELECT * FROM Database3.dbo.TableA --contine to add as needed
with the example above, any new values would get added automatically. what is it specifically you want to see in Database7 as an example? or if there is a change in database3, you need to see it in database6?
ajayreddy_502 (4/13/2009)
Hi EveryoneI have a scenario in Sql Server where we have a same table(A) in 6 different databases.
The table A has a column named "status" which is updated from the application side .
my question is how can we pool the data from all the 6 tables in 6 different databases in to another database if there is a change in the status field.
Thanks in advance.
Lowell
April 13, 2009 at 1:59 pm
I want to see only the rows from table A (from all 6 databases)in which the status field is changed in a new database (table B)
April 13, 2009 at 2:57 pm
if you were to post CREATE TABLE... information, as well as the INSERT INTO...scripts, we could help you much better; even with your followup answer, it is still not clear exactly what you want.
here's an example of my best guess:
SELECT * FROM NewDatabase.dbo.TableA ThisDatabase
LEFT OUTER JOIN (
SELECT * FROM Database1.dbo.TableA UNION ---not union all..remove dupes?
SELECT * FROM Database2.dbo.TableA UNION
SELECT * FROM Database3.dbo.TableA UNION
SELECT * FROM Database4.dbo.TableA UNION
SELECT * FROM Database5.dbo.TableA UNION
SELECT * FROM Database6.dbo.TableA UNION )MYALIAS
ON ThisDatabase.StatusID = MYALIAS.StatusID
WHERE MYALIAS.StatusID IS NULL ---new status added
OR ThisDatabase.Status <> MYALIAS.Status --status descrip changed
Lowell
April 14, 2009 at 5:51 am
You can create a same table in Database7, and a before update trigger on tableA (in every database, if the filed is not updated in all tables at the same time) which insert record going to be updated into Database7.tableA. And execute select query on Database7.tableA
"Don't limit your challenges, challenge your limits"
April 17, 2009 at 8:59 am
It is still a little unclear what you require.
Do you want rows that have a particular status (for example, status = 'changed') or do you want rows that have any change done to the status column (for example, status gets changed from 'preliminary' to 'active').
Also, why do you want these rows inserted into another database? Is it to provide a table that can be queried for reports or auditing? The end requirement could affect the recommended solution. Maybe there is a better solution than moving rows to an additional database.
Just on the surface of it, one solution is:
1) use a trigger to insert the primary key into another table within each of the six databases when the status field gets changed
2) run a job on an acceptable interval (daily, hourly, whatever) that, based on the keys, will copy the rows from the original tables to the table in the seventh database and clear out the key tables
This seems to a reasonable solution, but again it depends on what and why.
Steve
April 17, 2009 at 10:28 am
Hi
Please take some time to put a good requirement/question 🙁
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply