Update Statement, Then Insert What Wasn't Available To Be Updated.

Jun 29, 2006

Using MS SQL 2000

I have a stored procedure that processes an XML file generated from an

Audit program. The XML looks somewhat like this:


<ComputerScan>
<scanheader>
<ScanDate>somedate&time</ScanDate>
<UniqueID>MAC address</UniqueID>
</scanheader>
<computer>
<ComputerName>RyanPC</ComputerName>
</computer>
<scans>
<scan ID = "1.0" Section= "Basic Overview">
<scanattributes>
<scanattribute ID="1.0.0.0" ParentID=""
Name="NetworkDomian">MSHOMe</scanattribute>
scanattribute ID = "1.0.0.0.0" ParentID="1.0.0.0", etc etc....


This is the Update portion of the sproc....


CREATE PROCEDURE csTest.StoredProcedure1 (@doc ntext)


AS
DECLARE @iTree int
DECLARE @assetid int
DECLARE @scanid int
DECLARE @MAC nvarchar(50)
CREATE TABLE #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))


/* SET NOCOUNT ON */


EXEC sp_xml_preparedocument @iTree OUTPUT, @doc


INSERT INTO #temp
SELECT * FROM openxml(@iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@ID',
ParentID nvarchar(50) './@ParentID',
Name nvarchar(50) './@Name',
scanattribute nvarchar(50) '.'
)


SET @MAC = (select UniqueID from openxml(@iTree, 'ComputerScan',
1)with(UniqueID nvarchar(30) 'scanheader/UniqueID'))


IF EXISTS(select MAC from tblAsset where MAC = @MAC)
BEGIN
UPDATE tblAsset set DatelastScanned = (select ScanDate from
openxml(@iTree, 'ComputerScan', 1)with(ScanDate smalldatetime
'scanheader/ScanDate')),
LastModified = getdate() where MAC =
@MAC


UPDATE tblScan set ScanDate = (select ScanDate from
openxml(@iTree,
'ComputerScan', 1)with(ScanDate smalldatetime 'scanheader/ScanDate')),
LastModified = getdate() where MAC =
@MAC


UPDATE tblScanDetail set GUIID = #temp.ID, GUIParentID =
#temp.ParentID, AttributeValue = #temp.scanattribute, LastModified =
getdate()
FROM tblScanDetail INNER JOIN #temp
ON (tblScanDetail.GUIID = #temp.ID AND
tblScanDetail.GUIParentID =
#temp.ParentID AND tblScanDetail.AttributeValue = #temp.scanattribute)
WHERE MAC = @MAC


!!!!!!!!!!!!!!!!!! THIS IS WHERE IT SCREWS UP, THIS NEXT INSERT
STATEMENT IS SUPPOSE TO HANDLE attributes THAT WERE NOT IN THE PREVIOUS

SCAN SO CAN NOT BE UDPATED BECAUSE THEY DON'T EXIST
YET!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID,
ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @MAC, b.ID, b.ParentID,
tblScanAttribute.ScanAttributeID,
@scanid, b.scanattribute, DateCreated = getdate(), LastModified =
getdate()
FROM tblScanDetail LEFT OUTER JOIN #temp a ON
(tblScanDetail.GUIID =
a.ID AND tblScanDetail.GUIParentID = a.ParentID AND
tblScanDetail.AttributeValue = a.scanattribute), tblScanAttribute JOIN
#temp b ON tblScanAttribute.Name = b.Name
WHERE (tblScanDetail.GUIID IS NULL AND
tblScanDetail.GUIParentID IS
NULL AND tblScanDetail.AttributeValue IS NULL)
END
ELSE
BEGIN


Here are a few table defintions to maybe help out a little too...


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
GO


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =

1)
drop table [dbo].[tblAsset]
GO


CREATE TABLE [dbo].[tblAsset] (
[AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,

[AssetName] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
,
[AssetTypeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DatelastScanned] [smalldatetime] NULL ,
[NextScanDate] [smalldatetime] NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
-----------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblScan]
GO


CREATE TABLE [dbo].[tblScan] (
[ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[AssetID] [int] NULL ,
[ScanDate] [smalldatetime] NULL ,
[AssetName] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NOT NULL
) ON [PRIMARY]
GO
----------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblScanDetail]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblScanDetail]
GO


CREATE TABLE [dbo].[tblScanDetail] (
[ScanDetailID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT
NULL ,
[ScanID] [int] NULL ,
[ScanAttributeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[GUIID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
,
[GUIParentID] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[AttributeValue] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NOT NULL
) ON [PRIMARY]
GO


------------------------------------------------------------
My problem is that Insert statement that follows the update into
tblScanDetail, for some reason it just seems to insert everything twice

if the update is performed. Not sure what I did wrong but any help
would be appreciated. Thanks in advance.

View 9 Replies


ADVERTISEMENT

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

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

One Statement To Do Insert And Update

Jan 23, 2013

In one statement (or whatever is most efficient if not possible), how do I update the rows in table A (based on a where clause), and insert into table B, the data from those same number of rows.

For example,

Table A: columns: id , lastName, department, branch
Table B: columns: id, errorMessage, lastName

UPDATE table A by settting branch = 'ETC' where department IS NULL
INSERT INTO table b the rows found in the above UPDATE statement

Note: 1.To find the rows UPDATED in the first statement, it is not sufficient to search by branch='ETC', as that will encompass more rows.
2. I do not want to use triggers as these statements will be executed once in the entire process

View 11 Replies View Related

Using CASE With INSERT,UPDATE Statement

Mar 6, 2008

Is it possible to use CASE statement with INSERT /UPDATE statement?this is what i am trying to do
i have a table like this
Field1 Field2 Field3  FieldType1 FieldType2 FieldType3
1-when there is no data for a given combination of  Field2 and  Field3,i need to do "insert".i.e the very first time and only time
2-Second time,when there is already a row created for a given combination of Field2 and  Field3,i would do the update onwards from there on.
At a time value can be present for only one of the FieldType1,FieldType2,FieldType3) for insert or update
I am passing a parameter to the stored procedure,which needs to be evaluated and wud determine,which field out of (FieldType1,FieldType2,FieldType3)has a value for insert or update .this is what i am trying to do in a stored procedure
CREATE PROCEDURE dbo.StoredProcedure
 ( @intField1 int, @intField2 int, @intField3 int,  @intFieldValue int , @evalFieldName varchar(4)  )So i am trying something like
CaseWHEN @evalFieldName ="Fld1" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,@intFieldValue,cast(null as int) fld2 ,cast(null as int) fld3)
CaseWHEN @evalFieldName ="Fld2" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,@intFieldValue,cast(null as int) fld3)
CaseWHEN @evalFieldName ="Fld3" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,cast(null as int) fld2,@intFieldValue)
END
similar trend needs to be followed for UPDATE as well..obiviousely its not working,gives me synatax error at case,when,then everywher.so can someone suggest me the alternative way?..i am trying to avoid writing  stored procedure to insert/update for each individual fields..thanks a lot
 

View 8 Replies View Related

Have Insert Statement, Need Equivalent Update.

Jun 26, 2006

Using ms sql 2000I have 2 tables.I have a table which has information regarding a computer scan. Eachrecord in this table has a column called MAC which is the unique ID foreach Scan. The table in question holds the various scan results ofevery scan from different computers. I have an insert statement thatworks however I am having troulbe getting and update statement out ofit, not sure if I'm using the correct method to insert and thats why orif I'm just missing something. Anyway the scan results is stored as anXML document(@iTree) so I have a temp table that holds the releventinfo from that. Here is my Insert statement for the temporary table.INSERT INTO #tempSELECT * FROM openxml(@iTree,'ComputerScan/scans/scan/scanattributes/scanattribute', 1)WITH(ID nvarchar(50) './@ID',ParentID nvarchar(50) './@ParentID',Name nvarchar(50) './@Name',scanattribute nvarchar(50) '.')Now here is the insert statement for the table I am having troublewith.INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,ScanID, AttributeValue, DateCreated, LastModified)SELECT @MAC, #temp.ID, #temp.ParentID,tblScanAttribute.ScanAttributeID, tblScan.ScanID,#temp.scanattribute, DateCreated = getdate(),LastModified =getdate()FROM tblScan, tblScanAttribute JOIN #temp ONtblScanAttribute.Name =#temp.NameIf there is a way to do this without the temporary table that would begreat, but I haven't figured a way around it yet, if anyone has anyideas that would be great, thanks.

View 3 Replies View Related

How To Update If Exists Else Insert In One SQL Statement

Jul 20, 2005

In MS Access I can do in one SQL statement a update if exists else ainsert.Assuming my source staging table is called - SOURCE and my targettable is called - DEST and both of them have the same structure asfollowsKeycolumns==========MaterialCustomerYearNonKeyColumns=============SalesIn Access I can do a update if the record exists else do a insert inone update SQL statement as follows:UPDATE DEST SET DEST.SALES = SOURCE.SALESfrom DEST RIGHT OUTER JOIN SOURCEON (DEST.MATERIAL = SOURCE.MATERIAL ANDDEST.CUSTOMER = SOURCE.CUSTOMER ANDDEST.YEAR = SOURCE.YEAR)This query will add a record in SOURCE into DEST if that record doesnot exist in DEST else it does a update. This query however does notwork on SQL 2000Am I missing something please share your views how I can do this inSQL 2000.ThanksKaren

View 6 Replies View Related

Single Statement For Insert Or Update

May 8, 2008

In VB6 using MDAC 2.8 I could do a single select statement that would act as either an Insert or an update. Is there a way to do this in ADO.net?
My old VB6 code
Dim dbData As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim strParm As String
Dim strCusNo As String
'
strParm = "Provider=SQLOLEDB; Data Source=SQL2000; Initial Catalog=DATA_01; User ID=UserName; Password=password"
dbData.Open strParm
'
strParm = "Select CusNo from CusFil Where CusNo = '" & strCusNo & "'"
rs1.Open strParm, dbData, adOpenStatic, adLockOptimistic, adCmdText
If rs1.BOF And rs1.EOF Then
rs1.AddNew
Else

End If
With rs1
!CusNo = strCusNo
.Update
End With
rs1.Close
'
Set rs1 = Nothing
dbData.Close
Set dbData = Nothing

Is there an ADO.Net equivalent?

thanks,

View 3 Replies View Related

SQL UPDATE/INSERT Statement Speed

Apr 21, 2008



Hello,

Server hardware: Intel core 2 duo, 2Gb ram, SATA 1500Gb, SQL Server 2005

I have big table (~ from 50000 to 500000 rows) :



Code Snippet
CREATE TABLE [dbo].[CommonPointValues](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[pointID] [bigint] NOT NULL,
[pointValue] [numeric](10, 2) NOT NULL,
[qualityID] [int] NOT NULL,
[dateFrom] [datetime] NULL,
[dateTo] [datetime] NULL,
CONSTRAINT [PK_PointValues] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]




UPDATE statement:



Code SnippetUPDATE dbo.CommonPointValues SET dateTo = @last_update WHERE ID = @lastValID




2000 rows update takes about 1 sec.

INSERT statement:




Code Snippet
INSERT INTO dbo.CommonPointValues (pointID, pointValue, qualityID, dateFrom, dateTo )
VALUES (@point_id, @value_new, @quality_new, @last_update, @last_update )






Speed with INSERT is similar like with UPDATE.

I need about 10000 updates per 1 second and 10000 inserts per 1 second or together 5000 inserts and 5000 updates per 1 sec.

Should I update hardware or try some improvements to table structure.

View 9 Replies View Related

How To Correctly Update A Table Which Values Can Be Either Inserted/updated/deleted On Update?

Feb 16, 2006

Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza

View 8 Replies View Related

INSERT, UPDATE, And DELETE Statement Checkbox Inactive

Mar 26, 2008

Hi  AllgI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advancehttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPGhttp://img139.a.com/img.php?image=28285_2_122_937lo.JPG   

View 1 Replies View Related

My Update Statement Isn't Working But Select And Insert Are. What's Wrong?

Aug 11, 2007



here is my code:


Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())

cn.Open()

Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()

adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products
set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" &
textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)

cn.Close()

Response.Redirect("database.aspx")

it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?

View 5 Replies View Related

Problem With Data Refreshing After Insert Or Update Statement

Mar 18, 2008

Dear All,

Im using VS2008, visual basic and SQL Server express.

General questions please: Is it necessary, to issue some sort of command, to 'commit' data to a SQL express database after a INSERT or UPDATE sql command?

I'm getting strange behavior where the data is not refreshed unless i exit my app and re-enter. In other words, i can run a sql command , the data is apparantly saved (because i get no errors) then if i refresh a data set or do a sql select query the data that i expect to return is not there.

Im fairly new to SQL express (and SQL server generally) so i dont know if its my coding or i need to switch some 'feature'
on/off or not.

I hope thats clear

Also, could someone point me to documentation that explains each parameter in the connection string

Many Thanks

Chris Anderson


My code is:


ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:myfile.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

Connection = New SqlConnection

Connection.ConnectionString = ConnectionString

Connection.Open()
'''''''''''''''''the above code is done at the start of my application


'''''''this code below called during application many times


dim sql as string = "my sql string here"

Dim cmd As SqlCommand = Nothing

cmd = New SqlCommand(sql, Connection)






Try

cmd.ExecuteNonQuery()

Catch err As SqlException

MessageBox.Show(err.Message.ToString())

MessageBox.Show(sql, "SQL ERROR: ", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try



cmd.Dispose()

View 5 Replies View Related

Select Statement (Advvance Button Insert, Update, Deltete

May 6, 2007

Hello All
I have had asked the same question in another post, i didnt get answer to it i might have had asked it wrongfully
 
Soo the question is:   When creating a SQLDataSource in the wizard  you get to the pont where you select the option . It says that by using this datasource you can select to update delete and insert. So my question is if i am creating a select statement to reterieve the data from the Table, then what does it do it do if my intention is to only reterie the data. Or what is the other way that it could be helpful to me ??
 
thanks all I hope it make sence, if not I wrill write another post to bring step by step info into it.
 

View 1 Replies View Related

GridView Based On SQLServerDataSource Using A Select Union Statement, Impacts On Update And Insert?

Jan 3, 2006

I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):

SELECT    FirstName,    LastNameFROM    MasterUNION ALLSELECT    FirstName,    LastNameFROM    CustomORDER BY    LastName,    FirstName
I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom).  Any ideas if or how this can be done?  Specifically, I want the Custom table to be editable, but not the Master table.  Any examples or ideas would be very much appreciated!
Thanks,
Randy

View 5 Replies View Related

Connection To SQLEXPRESS Was Working And Then... It Wasn't

Apr 23, 2008



I see a few have had this problem but their answers didn't help.

My connection string:


"Data Source=.SQLEXPRESS; Integrated Security=True;AttachDbFilename=|DataDirectory|TestApp.mdf;Initial Catalog=TestApp"

and the error a am getting is:

Cannot open database "TestApp" requested by the login. The login failed.
Login failed for user 'ComputerName\TheHaggis'.

Like i said it work a couple of times and then i get this error.

what am i do wrong

cheers

the Haggis

View 5 Replies View Related

Get Total Updated Rows Effected By A Statement

May 6, 2008

This was a usual day today in office and i was working on a requirement in which i was needed to fetch the total number of rows effected by an update query, so I asked my best code mate "Google" and to my surprised there was not enough correct answers at least the one i was looking for.There were suggestions that  you can use a select statement for the updated rows and make it like a select (count) which works fine, but just looking into the SQL server books online, it shows that there is even a better way to do it.After the update statement in my  stored procedure i used "@@ROWCOUNT" with a select statement and it works like a charm.so the little find for my first ever post on asp.net is that there is a better way to find the total updated rows by a query Example: DB: Northwind , Table Employeesupdate employees set extension='1234'select @@ROWCOUNT  This will return 9 (default rows in this table) as the rows effectedHope this helps 

View 2 Replies View Related

T-SQL (SS2K8) :: INSERT Only When Value Has Been Updated?

Oct 14, 2014

I have database that holds column depicting various events and the last time that the event occurred.I query the database and I get a result like this:

Event Update Time
Event A 2014-10-14 00:35:00.000
Event B 2014-10-14 01:30:00.000
Event C 2014-10-14 00:35:00.000

Event L 2100-01-01 00:00:00.000..I would like to create another database to hold the various timestamps for each event. Some of these events happen every 2 minutes, others hourly, daily or monthly.I can query the source every minute. I do not want duplicate entries in the destination when the 'Update Time' has not been updated. Is it better to query the destination for the latest 'Update Time' value and only INSERT the new record when the time has been updated or just put a UNIQUE constraint on the Event and UpdateTime columns?

View 9 Replies View Related

Auto Update Value When Record Gets Updated

Feb 21, 2006

I have a table that contains a field LastUpdated. This field should contains the date of the last update of the record.

Is there a way to auto-update this field value to the getdate() value each time the record is "touched" by an UPDATE sql command?

Thanks in advance,
Joannes

View 1 Replies View Related

How Can I Catch Updated Row In A Update Trigger.

Dec 17, 2007



Hi,

I am creating update trigger(After) on a table.I want to catch an updated row and insert the values of this row into
a new table.How can i catch this row while creating a trigger after update.

View 7 Replies View Related

How Can I Get Which Columns Were Updated In Trigger On Update

Nov 22, 2006

Hi,

I'm using sql-2005.

I want to update several columns in different tables whenever an update is happend on specific table. I have a trigger for that which update all the relevant places when it fires.

I want to do this update only if certains columns were changed, otherwise - do anything, to reduce performance.

How Can I know which columns were updated inside the trigger? The tables has many columns and only if 2 of them were changed then I need to update other tables.

Many thanks,

Nira.

View 2 Replies View Related

Updated Records From A Table Insert Into Another

Jul 26, 2007

Hi:

I need to create a trigger to save every record which has been updated in a table (e.g. "Contacts") into another (e.g. "Shadow_contacts). So basically something like a versioning on database level.
I have no experience with it and any precise hints/solutions would be very appreciated

selmahan

View 1 Replies View Related

Insert/Updated SP From Multiple Tables

Nov 23, 2004

How do I insert unrelated statistical data from three tables into another
table that already exist with data using an insert or update stored procedure?
OR...
How do I write an insert/Update stored procedure that has multiple select
and a where something = something statements?

This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this....

CREATE PROCEDURE AddDrawStats
AS
INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers)

SELECT
WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing),

TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ),

RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID )

FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID

WHERE D.DrawingID = DS.CurrentDrawing
GO

View 6 Replies View Related

UPDATE Stored Proc -- Updated Over All Of My Records

Jan 31, 2005

I wrote a stored proc to be implemented in a datagrid. I went and used it on a record to update some information and it updated THE WHOLE DATABASE WITH THAT ONE RECORD..

IF anyone could shead some light on what I'm doing wrong that would be great.

Here is the syntax for my stored proc.



CREATE PROC updateResults2
@id int, @address1 nvarchar (50), @address2 nvarchar (50), @CITY nvarchar (33), @ST nvarchar (10), @ZIP_OUT nvarchar (5), @ZIP4_OUT nvarchar (4), @home_phone nvarchar (22), @NEWPhone nvarchar (20)
AS
UPDATE Results
SET address1 = @address1,
address2 = @address2,
CITY = @CITY,
ST = @ST,
ZIP_OUT = @ZIP_OUT,
ZIP4_OUT = @ZIP4_OUT,
home_phone = @home_phone,
NEWPhone = @NEWPhone


GO



As said previously it ran but it updated the WHOLE DATABASE with the same change (WHICH I DIDNT WANT IT TO DO)!!

Thanks in advance.
RB

View 3 Replies View Related

Trigger (Instead Of Update) Doesn't Allow My Table Been Updated.

Mar 8, 2008

I have a Trigger Instead of Update.
When I Update my table, the trigger runs correctly but my table never is updated.


View 5 Replies View Related

Update A Dataset From An Updated MSSQL Table

May 23, 2007

Here is my problem: I've created a table in MSSQL named MyReferences and then a typed dataset to connect to it.



Then I realized that I needed to add a column to MyReferences table and then I did it.



The problem is that I cannot find a way to add the new column to the typed dataset. I am unable to view it in the available columns.



Can anyone help me?



I'm using Visual Studio 2005 sp1 and C#.



Best regards



Alessandro

View 1 Replies View Related

Update Values From Inserted Or Updated In Triggers

Mar 6, 2008

Hi all. I have this problem:

on insert in a new table a have to change one column before insert.

I wrote this trigger:

create trigger SUBSCR_ID_TRANSFER ON dbo.SalesOrderExtensionBase AFTER INSERTAS BEGIN SET NOCOUNT ON;DECLARE @OpportunityID uniqueidentifier;DECLARE @subscrId uniqueidentifier;declare @salesorderid uniqueidentifier;set @salesorderid = (select SalesorderID from inserted)SET @OpportunityID = (SELECT OpportunityId FROM SalesOrderBase where SalesOrderID=@salesorderid)SET @subscrId = (SELECT New_old_subscridId from OpportunityExtensionbase where OpportunityID=@OpportunityID)Update inserted set New_old_subscridId = @subscrIdENDbut SQL Rise the error "The inserted values can not be modified"

how to slove this issue.
Thanks.

View 5 Replies View Related

How To Determine Which Record Was Updated Using A Update Trigger?

May 8, 2008



Im using a trigger to check updates on particular table and execute a email. it works but it doesnt show the right record
im looking into one table called SiteInfo.
here is my code
Im using sql 2005, can someone look at my code or the select statement.


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE TRIGGER TTSUpdate

ON SiteInfo

FOR UPDATE

AS

declare @SiteID varchar(10)

declare @Body2 varchar(2000)

declare @Sitename varchar(50)

declare @TTSCreate varchar(30)

declare @TTSCreator varchar(50)

declare @Subject2 varchar (100)



SELECT @SiteID = SiteID,@Sitename = AccountName,@TTSCreator = TTSOwner,@TTSCreate = TTSCreatedDate

from SiteInfo



SET @Body2 = 'New TTS site created: ' + @Sitename + ' With TTS Site ID:' + @SiteID + ' TTS was created on: ' + @TTSCreate + ' By:' + @TTSCreator

SET @subject2 = 'New TTS site created: ' + @Sitename

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'TTSAdmin',

@recipients = 'email address here',

@subject = @subject2,

@body = @body2

GO

View 3 Replies View Related

Create A Trigger To Update A Row That's Been Inserted Or Updated

Jun 4, 2007

Hi



Apologies if this is a silly question



I have a basic table "Customer" which has

Id

Address1

Address2

Address3

Town

County

Postcode

SearchData



After I insert or Update a row in this table I need to update the SearchData column

with



UPPER(ADDRESS1) + UPPER(ADDRESS2) + UPPER(TOWN) + UPPER(POSTCODE)



only for that Id



I'm sure this is only a basic update but all the examples I can find are for inserting into other tables not the row itself.



Regards



David

View 4 Replies View Related

Web Data Administrator - If I Wasn't Bald I'd Be Tearing My Hair Out!

Dec 22, 2004

Hello. I see a lot of threads re this but I havent seem an answer. Please help.
I have Win xp pro SP2 with msde, IIS 5.1 and Web Data Administrator on two separate machines.

With one machine everything works great. On the other, When I hit the Web Data Administrator Login button (windows integrated or SQL) it just shows the hour glass and doesnt do anything.

On the one that works, I can change the server to one on our lan with sqlserver and it will link in fine. However with the other, the same hourglass shows.

IIS and msde are both running and seem to work fine. I can browse to localhost virtual directories (I have dotnetnuke running using msde on localhost) and access the database from dotnetnuke and visual studios.

I have made sure my localhost is a "Trusted site" und ie browser and I have enable windows integrated authentication for the default web site and the webadmin virtual directory.

Any help would be greatly appreciated.

View 2 Replies View Related

T-SQL (SS2K8) :: Return Primary Key From (updated) Record Of Merge Statement

Apr 16, 2014

I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).

I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.

CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL

[code]....

View 4 Replies View Related

Does The UPDATE Trigger Fire When A Record Is Updated Or Only When It Is Deleted?

Jun 15, 2004

I've gotten conflicting info about this in the past so I thought I'd try to get clarification.

When a record is deleted, I'm sure it fires the delete trigger. Does it also fire the update trigger?

Thanks

View 3 Replies View Related

Timestamp - Automatically Update Date And Time When Rows Updated?

Apr 7, 2014

I know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?

View 5 Replies View Related







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