Solved

How to move new records or updated records into a new table in SQL 2005

Posted on 2007-11-27
6
192 Views
Last Modified: 2010-03-19
I have a data driven website in ASP.NET using C# and SQL 2005.
I have 2 different tables that users enter data into from the ASP front end. However when the record is entered by the user, i want the database to copy that record (insert) to a seperate table (actually just a few fields from the record) i would ideally want to do this if they update a record also? Is there a way i could have SQL do this automatically behind the scenes by copying and inserting or logging or something? every time a new record is put into one of the other 2 tables?

eventually this will really be copied to a table in a linked server . any idea if something like that would work or how to do it?
0
Comment
Question by:edi77
  • 3
  • 3
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20360295
yes, using a trigger, this is perfectly possible.
see this:
http://www.thescripts.com/forum/thread513448.html
0
 

Author Comment

by:edi77
ID: 20360510
ok i am a looking at that, and reading up on triggers.
thanks.

can i call this trigger from my asp page and have the users run the update or is it something i do in sql only?
can i have it run on its own (autorun) if in sql only?

any more info you know or sites about this stuff would help so much!
thanks!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20360532
>can i call this trigger
you cannot call a trigger. it is "called" automatically when the "insert"/"update" is happening, as part of the statement.
once the trigger is created, it is active automatically.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:edi77
ID: 20360566
would you mind giving me a little more detail on how this would work?
do you have to make an audit table with a trigger first, see what is updated or inserted and THEN copy over that data to the other table somehow?

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20360621
no, you could update/insert directly to the remote table.
now, the "audit" table is however a possible idea, depending on how frequent you insert/update the actual table, and how "fast" you need the data over to the other server, and how efficient the line between the 2 servers...

the link I gave above has already some code to insert to the remote server.
now, if you give some more details about the table(s), a concrete code could be suggested
0
 

Author Comment

by:edi77
ID: 20360810
oh okay thanks. that makes sense. so everything will be done in SQL.  when i update or insert and send over data to the linked server is it live data pretty much?

here are more details:(using one table as an example)

data is entered into a table called pellets available through my asp site:
a number of fields (DNA, Kit, ParticipantID, LP, Type, Date, DateDrawn, Time, Volume, Sex, etc.)

then a few fields  just (DNA, Kit, ParticipantID,and Date) need to be put in the dnaLabSamples table which is the linked server table.

of course i want this to work for updates and as well. im not using an odbc connection , im using linked servers in SQL . I set up a simple view in the linked server and it seems to run okay.

thanks so much


0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question