| Author |
Topic |
|
Zul
Starting Member
17 Posts |
 Posted - 2009-05-29 : 00:32:04
|
| i have 2 database1 db master, 2 db clientin db master i have alots of table, and make view in the db master to make smaller. exampelviewf1f2f3f4f5.i db client i make 1 table (table1)that in this table the field have same data with the view on db master.i have all ready migration data view from db master to table in db client (table 1)my question is how can i make script update data in db clien if data in view master has changeregradsZulkifli |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-29 : 14:18:13
|
| do you want this change to be applied immediately or periodically? ie after regular intervals? |
 |
|
|
Zul
Starting Member
17 Posts |
Posted - 2009-06-01 : 23:18:11
|
quote:
Originally posted by visakh16
do you want this change to be applied immediately or periodically? ie after regular intervals?
i want to update this change every midnight.can you tell me the script to update this database?as i know that to update the script every midnight is using jobs in sql, can you too show me articel or the step by step to make a jobs?thanksZul |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-01 : 23:35:05
|
you can create a stored procedure to perform the update and then schedule it using SQL Agent to run at midnight.see here http://msdn.microsoft.com/en-us/library/ms187061(SQL.90).aspx on SQL Agent.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Zul
Starting Member
17 Posts |
Posted - 2009-06-02 : 02:49:04
|
quote:
Originally posted by khtan
you can create a stored procedure to perform the update and then schedule it using SQL Agent to run at midnight.see here http://msdn.microsoft.com/en-us/library/ms187061(SQL.90).aspx on SQL Agent.
KH[spoiler]Time is always against us[/spoiler]
can you show me the query??because i'm newbie in SQL |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-06-02 : 09:31:54
|
| Can it be a total refresh of the client table with the data from the master db or do you retain any changes made by the client on the client db?Edit: Also, post your DDL, for both master and client, and some examples of the data for the master and the expected results on the client.Terry-- Procrastinate now! |
|
|
|
Zul
Starting Member
17 Posts |
Posted - 2009-06-02 : 22:58:03
|
| yes it totaly refresh of the client with the db master. if db master has change then db client change same with db master. but db client can't change selve without db master.SELECT pm_guid, pm_id, pm_raise_date, pm_last_update, closureDate, usr_reference, pm_description, pm_title, tps_name, lc_title, im_is_breached, im_full_name, userAnalis, status, nama, AnalystFROM dbo.VIncidentUNION ALLSELECT pm_guid, pm_id, pm_raise_date, pm_last_update, closureDate, usr_reference, pm_description, pm_title, tps_name, lc_title, ca_is_breached, ca_full_name, userAnalis, status, nama, AnalystFROM dbo.VCallUNION ALLSELECT pm_guid, pm_id, pm_raise_date, pm_last_update, closureDate, usr_reference, pm_description, pm_title, tps_name, lc_title, cm_is_breached, cm_full_name, userAnalis, status, nama, AnalystFROM dbo.VChangeUNION ALLSELECT pm_guid, pm_id, pm_raise_date, pm_last_update, closureDate, usr_reference, pm_description, pm_title, tps_name, lc_title, pr_is_breached, pm_full_name, userAnalis, status, nama, AnalystFROM dbo.VProblemUNION ALLSELECT pm_guid, pm_id, pm_raise_date, pm_last_update, closureDate, usr_reference, pm_description, pm_title, tps_name, lc_title, cm_is_breached, cm_full_name, userAnalis, status, nama, AnalystFROM dbo.VReleasethis is query to make view in DB Masterinsert into master.dbo.process (ticket_guid,ticket_id,raise_date,last_update,closure_date,ticket_type,description,title,raise_user_name,priority,is_breach,maincategory,tipeuser,status)select * from client.dbo.VAllprocessthis is query to migration from db master to db client |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-06-03 : 08:14:33
|
quote:
Originally posted by Zul
yes it totaly refresh of the client with the db master. if db master has change then db client change same with db master. but db client can't change selve without db master.SELECT pm_guid, pm_id, pm_raise_date, pm_last_update, closureDate, usr_reference, pm_description, pm_title, tps_name, lc_title, im_is_breached, im_full_name, userAnalis, status, nama, AnalystFROM dbo.VIncidentUNION ALLSELECT pm_guid, pm_id, pm_raise_date, pm_last_update, closureDate, usr_reference, pm_description, pm_title, tps_name, lc_title, ca_is_breached, ca_full_name, userAnalis, status, nama, AnalystFROM dbo.VCallUNION ALLSELECT pm_guid, pm_id, pm_raise_date, pm_last_update, closureDate, usr_reference, pm_description, pm_title, tps_name, lc_title, cm_is_breached, cm_full_name, userAnalis, status, nama, AnalystFROM dbo.VChangeUNION ALLSELECT pm_guid, pm_id, pm_raise_date, pm_last_update, closureDate, usr_reference, pm_description, pm_title, tps_name, lc_title, pr_is_breached, pm_full_name, userAnalis, status, nama, AnalystFROM dbo.VProblemUNION ALLSELECT pm_guid, pm_id, pm_raise_date, pm_last_update, closureDate, usr_reference, pm_description, pm_title, tps_name, lc_title, cm_is_breached, cm_full_name, userAnalis, status, nama, AnalystFROM dbo.VReleasethis is query to make view in DB Masterinsert into master.dbo.process (ticket_guid,ticket_id,raise_date,last_update,closure_date,ticket_type,description,title,raise_user_name,priority,is_breach,maincategory,tipeuser,status)select * from client.dbo.VAllprocessthis is query to migration from db master to db client
OK. Your insert statement is going into master not client. Which way is it truly going? And why are you putting user tables in the master database? Definitely not a best practices approach!Terry-- Procrastinate now! |
|
|
|
|
|
|
Update from view to other table