Solved

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

Posted on 2007-11-27
6
195 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 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
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!

 

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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

728 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