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.
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
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.
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.
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
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
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.
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
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
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
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
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?
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
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.
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
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
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?
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.
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.
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
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
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.
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"
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.
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.
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.
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?