Need Help Update And Insert

Feb 19, 2004

I need a query to make an insert or and update in the same query

thanks

View 6 Replies


ADVERTISEMENT

SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE

Apr 16, 2015

If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?

EXAMPLE:

CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,

[Code] ....

If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)

INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE

View 9 Replies View Related

Can I Insert/Update Large Text Field To Database Without Bulk Insert?

Nov 14, 2007

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind.  I've tried using the new .write() method in my update statement, but it cuts off the text after a while.  Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

View 6 Replies View Related

T-SQL (SS2K8) :: Insert / Update Triggers When Insert Run Via Script

Oct 23, 2014

I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.

This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.

View 1 Replies View Related

Trigger To Update A Table On Insert Or Update

Feb 15, 2008



Hello

I've to write an trigger for the following action

When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz

all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated

statut_tiers to 1
and date_cloture to the same date as entered

the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture

thank you for your help
I've never done a trigger before

View 14 Replies View Related

Single Complex INSERT Or INSERT Plus UPDATE

Jul 23, 2005

Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.

View 4 Replies View Related

Can I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net

Mar 1, 2007

Can I roll back certain query(insert/update) execution in one page if  query (insert/update) in other page  execution fails in asp.net.( I am using sqlserver 2000 as back end)
 scenario
In a webpage1, I have insert query  into master table and Page2 I have insert query to store data in sub table.
 I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance

View 2 Replies View Related

SQL Insert/Update

Jul 7, 2006

Hi,
Can anyone explain what the difference is and the advantages or disadvantages of using the below statements in my SQL paramaters please. Is there a performance hit if I use the second option?
If myCustomer.Phone2 IsNot Nothing Then
versus
If myCustomer.Phone2.Length > 0 Then
 
Thanks

View 2 Replies View Related

DTS Insert Or Update

Dec 8, 2006

Is there an easy way with DTS to pump data from one table to another so that it will update the row if it exists (the source and destination have the same value for the ID colum) or insert it if it doesn't.
 I know this can be done with stored procedures/sql by doing IF EXISTS UPDATE ELSE INSERT but there are many tables and columns and this will be very tiime consuming.

View 1 Replies View Related

When To Use Sql Update And Sql Insert

Feb 2, 2008

I have a page where the user can update stock records. it has 5 x 5 text boxes. If the user has already entered stock before that stock will show up and they can change it and clicking the button it will update, however if they have just entered new data i would assume they would need to insert it, so how do i go about doing this? do i need to use both insert and update in the same sql string?

View 7 Replies View Related

Insert Then Update...

Jul 15, 2005

greetings

I am developing an application for the marketing dept at my company.
Basically users can build the content of an email to be sent to our
subscriber database.

I am wanting the application to initailly save the content into a database, the update the most recently inserted row.

The save button uses the following SQL command:
        Dim SqlMethod As String =
"INSERT INTO CZC_email (Offer, SendDate, Destinations, Copy, BannerURL)
VALUES ('" & txtCampaignName.Text & "','" &
calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "','" &
DestinationsSelected & "','" & FreeTextBox2.Text & "', '"
& txtBannerPath.Text & "')SELECT @@IDENTITY AS 'CZ_ID'"

And my update button has this SQL command:

        Dim SqlMethod As String =
"UPDATE CZC_email SET SendDate = '" &
calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "', Offer = '"
& txtCampaignName.Text & "',Destinations = '" &
DestinationsSelected & "', BannerURL = '" & txtBannerPath.Text
& "'  WHERE  CZ_ID = @@IDENTITY "

but it doesnt seem to be updating. anyone know what I'm doing wrong?

Cheers

View 7 Replies View Related

Insert Before Update?

Nov 24, 2005

I have a GridView on a page, It contains data from 2 joined tables and a command column to Edit/Update.  I want to update the data in only one of the tables.  If the data exists in the table to be updated there seems to be no problem (obviously).  But I get an error when trying to update a record that does not exist (I should think so).  Is there a way of Inserting the record before the update is fired?  I have tried to do an insert contained in a 'Try' in the GridView1_RowUpdating, but this does not seem to work. as I still get the same error. Object cannot be cast from DBNull to other types. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastException: Object cannot be cast from DBNull to other types.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 
Stack Trace:
[InvalidCastException: Object cannot be cast from DBNull to other types.]   System.DBNull.System.IConvertible.ToInt32(IFormatProvider provider) +54   System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +293   System.Web.UI.WebControls.SqlDataSourceView.AddParameters(DbCommand command, ParameterCollection reference, IDictionary parameters, IDictionary exclusionList, String oldValuesParameterFormatString) +577   System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +400   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78   System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1173   System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +1084   System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +88   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35   System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +117   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +83   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +136   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +172   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4839
 
Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
Try
Dim eventid As Integer = CInt(GridView1.Rows(GridView1.EditIndex).Cells(7).ToString)
Dim userid As Integer = CInt(hfID.Value)
Dim guests As Integer = CInt(GridView1.Rows(GridView1.EditIndex).Cells(4).ToString)
Dim attending As Boolean = CBool(GridView1.Rows(GridView1.EditIndex).Cells(5).ToString)
Dim extra As String = Server.HtmlEncode(GridView1.Rows(GridView1.EditIndex).Cells(6).ToString)
InsertRecord(eventid, userid, guests, attending, extra)
Catch ex As Exception
End Try
End Sub






Function InsertRecord(ByVal eventID As Integer, ByVal userID As Integer, ByVal guests As Integer, ByVal attending As Boolean, ByVal extra As String) As Integer
Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='AFRA'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [AFRAAttendance] ([EventID], [UserID], [Guests], [Attending], [Extra]) VALUES (@EventID, @UserID, @Guests, @Attending, @Extra)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlCommand.Parameters.Add("@EventID", System.Data.SqlDbType.Int).Value = eventID
sqlCommand.Parameters.Add("@UserID", System.Data.SqlDbType.Int).Value = userID
sqlCommand.Parameters.Add("@Guests", System.Data.SqlDbType.Int).Value = guests
sqlCommand.Parameters.Add("@Attending", System.Data.SqlDbType.Bit).Value = attending
sqlCommand.Parameters.Add("@Extra", System.Data.SqlDbType.VarChar).Value = extra
Dim rowsAffected As Integer = 0
sqlConnection.Open()
Try
rowsAffected = sqlCommand.ExecuteNonQuery
Finally
sqlConnection.Close()
End Try
Return rowsAffected
End FunctionThank you

View 1 Replies View Related

DTS To Update Rather Than Insert

Jan 6, 2006

Is it possible to run a DTS package so that it just updates existing rows rather than a full on insert?

I have an HR db as a source and a health and safety db as destination. records of peoples H+S acheivments and assessments are maintained in the H+S db but i need to keep this topped up with the newly recruited staff details and the existing staff change of details. The DTS need to know to insert where its a new record and to update where its just a change of name or work location etc.

if DTS wont do this, what are my options?

TIA

View 2 Replies View Related

There Has To Be A Better Way To INSERT/UPDATE

Aug 22, 2004

I have a form that pulls existing information from a database and allows users to edit it. Every field can be left blank if the user wishes. I am having an issue with determining which SQL statement to run. There has to be an easier way to do this then what I am doing. For each filed I process the data this way:

SQL2="select * from 1985ClassList where MailingListID =" & Session("EID")
set aData = oConn.execute(SQL2)


SQL = "Update 1985ClassList SET "

if aData.Fields("FirstName") > " " and Request.QueryString("FirstName") > " " then
SQL = SQL & "'FirstName' = '" & Request.QueryString("FirstName") & "', "
end if

if aData.Fields("FirstName") = " " and Request.QueryString("FirstName") > " " then
strFSQL = strFSQL & "FirstName, "
strVSQL = strVSQL & Request.QueryString("FirstName") & ", "
end if


SQL = SQL & "where MailingListID =" & Session("EID")
set mData = oConn.execute(SQL)

if strFSQL > " " then
SQL1 = "Insert into 1985ClassList " & strFSQL & "VALUES " & strVSQL
end if
set mData = oConn.execute(SQL1)

It works fine for the UPDATE, but not for the INSERT.

View 9 Replies View Related

Insert/Update

Sep 1, 2004

Hi,
Is it possible to Read data From a Table while Insertion/Update is happening on the table??
In case if this is not possible,Is there anyway to do that??

Thanks,
Karthik

View 2 Replies View Related

Insert/Update

Dec 6, 2004

Is there a way to insert / update in SQL Server 2000 with one command..

I know with Oracle there is a merge command....

View 1 Replies View Related

Insert Or Update

Jun 4, 2008

Scenario :

MS SQL 2005

Tables:
DETAIL --Here are all the detailed transactions
Customer
Item
Amount
...MoreFields

TOTAL --Here are the accumulated values (1 record by Customer-Item
Customer
Item
Accumulated
...MoreFields

What I want to know is the general logic to
-Read all records from table DETAIL
-If relationship Customer-Item exists in TOTAL table then UPDATE the TOTAL.Accumulated field adding the DETAIL.Amount field
-If relationship Customer-Item doesn't exists in TOTAL table then INSERT a new ecord with the values from DETAIL table

Because of my experience in VB and ADO my first try is to use a Cursor and loop record by record

But I know that is not the only and better solution

May You post some hint or some sample querie on how could be done

Thanks

JG

View 2 Replies View Related

DTS For Insert/update

Jul 20, 2005

I'm new to DTS. I read some docs before adventuring into this matter.I still haven't found in all the docs I read if there is some "built-in" DTStask or function or wathever, to do a mixed "insert/update" import from asource, giving a unique field as primary key.I'll try to be more specific. The problem I would like to solve is this:I have a source file (csv) but it could be any source. I must check thisfile for all the records and compare them with the ones in the db (giving aunique field as a way of checking), so that all the records that alreadyexist, are UPDATED, and the others are INSERTED.I guess this is one most common task to accomplish, when you have a localbased application that you regularly update and then you want to export thedata to another "slave" application without using replication. But even ifthis sounds to me like a common task, I found no "buil-in" function for thatin DTS. I read something about "lookups" but it don't know if it's relatedsomehow.. it wasn't very clean.Thanks in advance for suggestions.--:: Massimiliano Mattei:: Project Leader:: E.xtranet V.irtual A.pplication:: www.evagroup.net :: www.commy.biz

View 3 Replies View Related

Insert And Update On DTS

May 22, 2008



Hi Team,

I would like to import a .csv file into my existing table. On the DTS, I would like to check if the record already there then updated, on the other hand, if the record wasn't there then add it. How can I do that using DTS. Please help and thanks.

View 3 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 Replies View Related

Update And/or Insert Statement

Sep 28, 2006

I am having an issue with this SQL Statement i am trying to write. i want to insert the values (Test Facility) from CCID table INTO CCFD table where CCID.id = CCFD.id  this is what i have.UPDATE CCFD.id SET TEST_FACILITY = (SELECT CCID.id.TEST_FACILITYFROM CCID.idWHERE CCID.id = CCFD.id)orINSERT INTO CCFD.id(Test_Facility)SELECT TEST_FACILITYFROM CCID.idWHERE (CCFD.id.INDEX_ID = CCID.id.INDEX_ID) thanks in advanced

View 1 Replies View Related

Insert Or Update Errors

May 1, 2007

Is there a way I can stop a form from inserting or updating a record when there is an error.  I have an sql 2000 DB.  I have noticed that if the db field can handle 50 characters and the form field has no limit on the number of characters no errors are displayed to the user if they try to use more than the 50 characters in the textbox.  The record is not saved.  None of the fields are saved. I do notice the autonumber generated is skipped by the db.  That is, the next autonumber for a successful insert skips the logical next number.   How do I capture this error, or any save error and return the user back to the form?  Yes, I have limited the number of characters a user can type on the textbox now, but I would really like to catch save or insert errors.  I use asp.net 2.0 and VB.  I don't know C#. thanksMilton

View 5 Replies View Related

Insert/Update From ASP.NET And Trigger.

Aug 23, 2007

Hi,
 Is it possible to group all my update/insert (one table) ?
Cause I hope to have all the update/insert in one Trigger execution. I need to sum up some figures here.
I am using ASP.NET 2.0, SQLOLEDB and SQL 2005.
 Please Help and Thank you.

View 8 Replies View Related

SQL Statement, INSERT/UPDATE

Dec 7, 2007

What I am attempting to do here is check to see if a record exists in a table, if so, I will update some fields, if not, I will insert a new record into the table.  This is what I have so far, I was hoping someone could let me know if any of these elements are unnecessary. SELECT [IP] FROM [Table]if (strIP == @IP){ UPDATE [Table] SET [Column = value++]} else{ INSERT INTO [Table] (IP) VALUES (@IP) } I obviously left out parameters and whatnot, I am mainly concerned with the logic here.  My apologies if this is something simple. 

View 9 Replies View Related

Update Or Delete With Insert?

Dec 13, 2007

Hi All,
I have this project I'm working on it's Product Content Management System rewrite. I got to the point of updating the Product By Sku and not sure if I should use UPDATE statement or I should DELETE sections assosiated with the ProductContentID and then re-insert them again? I'm not sure which is more afficient?
I can really do both and it's really not that complicated, the only problem I see with DELETE then INSERT is the ProductContentSectionId in the Sections table is going to grow very fast and I'm a bit concerned about it.
We use SQL Server 2000 and we have about 4 bound tables where we keep the data. The one I'm talking about is the sections table where we keep the actual types of product content like a BoxShot, Description, Key Features and so on...
Thank you in advance!
Tatyana Hughes
 

View 3 Replies View Related

Insert/Update Question

Apr 14, 2008

Hi,
I am building an e-commerce site for a college project and in my database design I am using autonumbers/Identity for primary keys for things like order numbers.
I am using SQL Server 2005 and have the keys as int's and with identitys that start and 1 and increment by one.  I know that when I do an insert the table will automatically put a number in the id field.
My question is if I have to do an insert on multiple tables, lets say and order table and order history table.  How do I get the generated id number into the foriegn key on the other table?  Is there someway to get SQL server to do it for you?
 I have setup the relationships and stuff for the server.  If you need a database diagragm to explain what I mean let me know and ill put one up.
 Thanks

View 1 Replies View Related

Insert / Update 2 Tables

May 12, 2008

Hi,
 
I'm trying to update 2 tables in SQL (say 2 Costumers table). 1 Lists all costumers per location( so 1 costumer can be placed in multiple locations), while the other is by location with contact details.
Is it possible for me to update both pages in 1 web update page?????
 
Thanks in advance.

View 3 Replies View Related

Insert, Update Queries

Feb 11, 2004

Is there any way to use a graphical designer to build your insert & update SQL statements in Enterprise manager? I mean Access has an EASY way to build them, surely SQL does too?

I would just build them in Access and copy the SQL, but then I'm stuck replacing all the "dbo_" with "dbo." and other little nuances.

View 3 Replies View Related

What Is Faster... INSERT Or UPDATE?

Jan 21, 2005

I have a situation where currently, I check to see if a record exists and I do an update if it does, I do an INSERT if it does not. This happens about 200k times each night.

I'm trying to speed the application up that is doing this and I was wondering if it would be a better idea to just delete the records I'm about to recreate, before I start, then do an unconditional insert.

It would eliminate my IF EXISTS check but if an update is faster than an insert, it might even out.

Also, will SQL deal with the big delete and the data being recreated each night without too much bloating or fragmentation?

View 2 Replies View Related

Multiple Row Update/Insert

Jun 29, 2005

Hi,I have a a table that is primarily a linking table that contains values for categories indid int             Indicator ID indtype int         Indicator Type can be either 0 or 1catflagnum int     Category Flag Number   the number of the Category catflagvalue int    Cat Flag Value    The Value for that category this table can then be updated from a web form.The Question I have is that can I do this in one statement or do I have to do it one at a time   i.e    The Data set could look something like          indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 1                             'This value will change         Catflagvalue = 1                           'This value will change            indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 2                            'This value will change         Catflagvalue = 3                           'This value will change          indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 3                             'This value will change         Catflagvalue = 1                           'This value will change            indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 4                            'This value will change         Catflagvalue = 5                           'This value will change A further complication is that in the table an entry may not be in that table for the category so we would possibly have to check if the record exist or do an insertI am stumped on this one?

View 5 Replies View Related

On Insert, Then Update Tigger Help

Jan 5, 2006

when a record is inserted into tNTS, I need the column seq_num updated with the value of the column id (it's the PK)can someone help me with my tigger:CREATE TRIGGER updateNewSeq    ON dbo.tNTS    FOR INSERTASupdate tNTS    (set seq_num =tNTS    )SELECT    NTS_idFROM INSERTEDcheers

View 5 Replies View Related

Otomatic Insert Or Update

Oct 31, 2001

i want to make otomatic insert or update base on specific field value in a column, for example if i inserted a date at column clmdate then otomatically insert a text e.g. 'open' at clmOpen, how can i do that?

View 1 Replies View Related

Insert Trigger With An Update

Apr 25, 2002

I would like to update a record in a table when a record is added. The dilema is I want to update the record I am adding. I tried an insert trigger ( it doesn't fail, it just doesn't update) If I use the same syntax in TSQL after the record has been inserted it handles the update properly. SO I think the syntax is OK. I have the trigger below. The question is - Is there a way to do this?
Thanks

CREATE TRIGGER transaction_ins_tr
ON dbo.transactions
FOR INSERT
AS
DECLARE @pat_id varchar(5),
@location_name varchar(50),
@account_id char(12),
@tran_num int
--get patient id
SELECT @pat_id = patient_id
FROM inserted
select @account_id = account_id from dbo.patient pa
where pa.patient_id = @pat_id
--get location
select @location_name = df_sitename from
development..patient_ dp
where dp.account_id = @account_id
-- update trans location
update dbo.transactions
set df_sitename = @location_name
where tran_num = @tran_num

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved