[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2007-11-27
6
Medium Priority
?
198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 143

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 143

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 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