MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMS SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old September 5th, 2004, 11:34 PM
umesht umesht is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2004
Posts: 8 umesht User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question How do i replicate a table in the same database

i want to replicate the data from one table into another table in the same database,all the transactions in table "a" should be replicated in table "b" and vice versa.
Note:this should be done without using triggers

Reply With Quote
  #3  
Old September 6th, 2004, 04:07 AM
umesht umesht is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2004
Posts: 8 umesht User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Unhappy thanks

hi,
Thanks for u r reply, can u just elaborate

Reply With Quote
  #4  
Old September 6th, 2004, 05:11 PM
Username=NULL Username=NULL is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Apr 2004
Location: TX
Posts: 249 Username=NULL User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5
Send a message via Yahoo to Username=NULL
well, could you elaborate a bit more??? You want the exact same data in 2 diff tables?? There are numerous ways to do this, if we know how, when, and why this is needed maybe we can select the best way for you.

Reply With Quote
  #5  
Old September 6th, 2004, 09:10 PM
umesht umesht is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2004
Posts: 8 umesht User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
detailed requirement

Ok,
We r doing the stratification of a module in a project , for which we have to change the design of a table which is used by other modules so we thought of creating another table for this module ,but we want to synchronize the data betwen both the tables,one solution is to write triggers on both the tables.
Is there any others solution.
Pls help me

Reply With Quote
  #6  
Old September 6th, 2004, 10:04 PM
Username=NULL Username=NULL is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Apr 2004
Location: TX
Posts: 249 Username=NULL User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5
Send a message via Yahoo to Username=NULL
so it sounds like this is a temporary ordeal?

Not sure when, how, and why the table is populated, but why not just add code to update the other table as well? To start out, just create another table and dump what's in the orig table into the new one, then add code to simply INSERT any new data into the new table as well? Just use a flag variable to detect records that were the most recently inserted. If it's a temporary thing that's one thing I would consider.

Reply With Quote
  #7  
Old September 6th, 2004, 10:43 PM
umesht umesht is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2004
Posts: 8 umesht User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Thumbs down Thanks, but......

Thanks
But i dont think this is he solution. May be the detail explaination i gave was not so clear , i'll try to explain it in detail.
Suppose there is a table Guest which is used by 5 modules,
now we r working on the first module where we find that some of the columns r redundant or we need to add some more columns to the table.Now because we r working only on the first module we do not want to make changes to original table because that may effect other modules so we make another say guestnew.
So the new table will be updated by the new module only and the original table will be used by all the other modules.
Whenever one table is updated the other table also has to be updated .

Reply With Quote
  #8  
Old September 7th, 2004, 03:19 AM
pabloj's Avatar
pabloj pabloj is online now
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 7,816 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 3 Weeks 1 Day 40 m 58 sec
Reputation Power: 278
I would check if it's possible to:
1. add another table holding the new columns
2. build a view joining the two tables
3. use that view in your module instead of the original tables (SQLServer supports updateable views with some restrictions)

Reply With Quote
  #9  
Old September 7th, 2004, 03:53 AM
umesht umesht is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2004
Posts: 8 umesht User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Unhappy Cant do that

That solution would work if we could change all the modules at the same time ,but the problem is that it has been decided to take up one module at a time i.e the code in other modules will be left untouched.
Now to use a view its name would have to be the same as the original table which is not possible ,renaming the original table also is not possible because it is linked to many other tables.
so if u have any oher solution pls revert back immediately

Pls this is urgent

Reply With Quote
  #10  
Old September 7th, 2004, 04:32 AM
pabloj's Avatar
pabloj pabloj is online now
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 7,816 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 3 Weeks 1 Day 40 m 58 sec
Reputation Power: 278
Why can't do that?
You wrote:
Quote:
Suppose there is a table Guest which is used by 5 modules,
now we r working on the first module where we find that some of the columns r redundant or we need to add some more columns to the table.Now because we r working only on the first module we do not want to make changes to original table

I'm not suggesting to modify the original one
Quote:
because that may effect other modules so we make another say guestnew.

I'm suggesting to make a new table, holding only the new attributes (columns) and the appropriate keys, then build a view that joins the original table and the new one and use that view in your module
Quote:
So the new table will be updated by the new module only

that's why I'm suggesting an updateable view
Quote:
and the original table will be used by all the other modules.

that's what my suggestion hopes to achieve
Quote:
Whenever one table is updated the other table also has to be updated .

that's why I'm suggesting to use a view, you'll see all the modifications made by the other modules seamlessly integrated with the ones from the new module.
Hope I clarified my suggestion (again not all views are updateable, check it first).

Reply With Quote
  #11  
Old September 9th, 2004, 02:45 AM
umesht umesht is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2004
Posts: 8 umesht User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Smile Thanks

Thanks a lot for ur help.I think the solution u gave will solve my problem.

Thanks again

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMS SQL Development > How do i replicate a table in the same database


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by