Solved

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

Posted on 2007-11-27
6
191 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Stored procedure 4 32
Incremental load example 2 52
create insert script based on records in a table 4 15
Anyway to make these 2 SQL statements into one? 13 25
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now