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
Solved

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

Posted on 2007-11-27
6
193 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 143

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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

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 …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

861 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