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.
We are getting data feed from Oracle database in our project. Everyday we will need to track if any rows got inserted/updated/deleted in the source and get that update right into our data warehouse.
Currently we are taking a dump of the required table (as it is) to our staging DB and comparing it with previous day data to track the changes (column by column comparison). This approach is working currently but has performance bottleneck. There is no tracking column (eg. last modified date or time) in source that will give us any idea of what got changed. Also there is no identity key or primary key in the source data.
Is there a way in SQL Server to get that inserted/updated records only instead of comparing column by column to track the changes?
I am inserting a record into a table that automatically generatesunique ids (i.e. Primary Key). Is there anyway to return this id. As Iam using this on ASP.net page and I really need the ID to update thepage with the new details.I think on mysql there is something called LAST_INSERT_ID which doesthis.
I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.
In the long sql, here is a part of it,
When Matched and
((Student.SchoolID <> esis.SchoolID OR Student.GradeLevel <> esis.GradeLevel OR Student.LegalName <> esis.LegalName OR Student.WithdrawDate <> esis.WithdrawDate Student.SPEDFlag <> esis.SPEDFlag OR Student.MailingAddress <> esis.MailingAddress)
Then update
Set Student.Schoolid=esis.schoolid, .....
My question is how about if the column has null values in it.for example
if schoolID is null in production table is null, but in staging table is not null, will the <> return true.or if either side of <> has a null value, will it return true.
I don't want it to omit some records and causing the students records not get updated.If not return true, how to fix this?
Can we insert into multiple table using merge statement?I'm using SQL Server 2008 R2 and below is my MERGE query...
-> I'm checking if the record exist in Contact table or not. If it exist then I will insert into employee table else I will insert into contact table then employee table.
WITH Cont as ( Select ContactID from Contact where ContactID=@ContactID) MERGE Employee as NewEmp Using Cont as con
I've been searching and trying out SQL statements for hours and I'mstill having a problem.I'm querying 3 tables... and I only want the first unique recordreturned.Currently, the data returned looks like this..............New York New York ANew York New York BNew York New York CLos Angeles California ALos Angeles California BLos Angeles California CI want the select statement to return this instead...New York New York ALos Angeles California AI'm using MS SQL server.please help?thanks for your help.
I would like to have a 'counter' table which will hold the last used number and return a new number. This is my schema:
if object_id('tempdb.dbo.#Counter', 'u') is not null drop table #Counter go create table #Counter ( Id int not null ) go if exists (select * from #Counter) update #Counter set Id = Id + 1 output inserted.Id else insert into #Counter (Id) output inserted.Id select 1
If the table is empty it returns 1 else it returns the next number (Id + 1). But this query is not atomic (i guess...?) so it could evaluate that the #Counter table is empty and then try to insert into the table, but inbetween someone else executes the insert also. Could this query be rewritten with the merge statement so that the whole operation is atomic?
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS] GO /****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT ee.EmployeeID, EmployeeName = LastName + ', ' + FirstName FROM EmployeeInvData ee --UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.
I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.
Have this problem solution? May be, I must use another method to get my data?
P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.
I have not yet succeeded in getting an aswer to this in a previous post, so I'll try again and rephrase the question
I have 2 fields, 1 called 'Created' the other 'Updated'
I would like to create a function, user procedure ,whatever in SQL something along the lines of...
FUNCTION myUID() RETURN Date(Today)+Time(Now)+USERNAME END FUNCTION
so that the value returned by the above pseudo code is something like '20080526T21:01:05.620SamL' where the date part is in yyyy0m0d format
I have got this working fine for the default 'Created' field but the calculated (and persisted) field comes up with something about being non-deterministic and refuses to play
Clearly something along these lines is a very straightforward requirement (I have it working fine in my 20+ year old database) , but have spent a week trying to get an answer (even from this forum) without success, so would be grateful for any help that you can provide me with to get round this barrier
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?
Hi All, I am using a trigger. I want to get the data of a row before updating inside this trigger and insert it into a backup table. Please anybody help me. Example with code is highly appreciated.
Hi - apologies if this is not the right forum for this - I've searched a bit and this seems to be the best fit. I have the following problem: I want to update records in a table that fit certain criteria. The way the insert logic works make sure that there will always be only one record that fits the criteria and I'd like to get the ID value of that record once the update went through. So here is what I tried: 1 UPDATE Timeslot 2 SET StartTime = @StartTime, EndTime = @EndTime 3 WHERE (ProfessionalID = @ProfessionalID) AND (ProviderLocationID = @ProviderLocationID) AND (RequestID IS NULL) AND (StartTime > @StartTime) AND 4 (EndTime < @EndTime); 5 SELECT SCOPE_IDENTITY()
My hope was that the select scope_identity would return the Timeslot ID of the row that was affected, but it doesn't. How do I get that row? Thanks!!! Oliver
I have written an update, if i see the execution plan of that update, it is showing the Non Clustered index are updated. I am not able to understand the fact, that when there was no change in Leaf level root data how does Non clustered index got updated.
I have some set of records in my table. The same set of records will be updated often. Now I have a column as "lastupdated" While i am displaying the records in a datagrid, The LAST UPDATED record should only be displayed. Means, the recently updated records should be displayed in datagrid. Pls give me the sql code / i am also in need of a Stored procedure for this. I am working in SQL 2005
I want to build an windows application by using a visual C# to Notify the user that his data in the database had been changed ..such like "New Message In Your Mail Box Alert"..So I need to know if there is way that to let the SQL Server send a notify (just like Trigger) ..
I am trying to write a trigger that, on an update, will check to see if more than one record is being updated. If more than one record is being updated, rollback the transaction and print a message. If not, then commit the transaction and print an OK message.
My problem is I am having troubles writing this trigger. I will post my SQL trigger code so far.
I am new with Triggers, so any help or advice is appreciated.
Code:
CREATE TRIGGER trgPaperCheck ON BOOK AFTER UPDATE AS IF UPDATE(Paperback) BEGIN DECLARE @count AS INT SELECT @count = COUNT(Paperback) FROM Book IF @count > 1 BEGIN PRINT('Only one Book record can be updated at a time!') ROLLBACK TRANSACTION END ELSE IF @count =1 BEGIN PRINT('Paperback Updated') COMMIT TRANSACTION END END
Hi, I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record, is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
Hi I have an application which get any change from database using sql dependency. When a record is inserted or updated it will fire an event and my application get that event and perform required operation. On the event handler I am usin select ID,Name from my [table]; this will return all record from database. I just want to get the record which is inserted or updated. Can u help me in that. Take care Bye
I would like to show when leads updated last their records in database. An automated report that tells me when the last date was that the leads updated an entry, only 1 entry per lead.
select Lead,LastUpdated from dbo.KPITbl
I have a table with data that looks like this:
Lead LastUpdated ----------- ----------------------- JOHN SMITH 2008-03-26 08:45:00 JOHN SMITH 2008-03-20 09:33:00 MEG RYAN 2008-02-21 16:16:00 JOHN SMITH 2008-02-21 16:19:00 MEG RYAN 2008-02-21 16:22:00 JOHN SMITH 2008-03-28 16:10:00 JOHN SMITH 2008-03-28 08:49:00 JOHN SMITH 2008-03-23 19:23:00 MARK MCRAE 2008-03-27 03:12:00 MARK MCRAE 2008-03-26 08:48:00 MARK MCRAE 2008-03-26 08:46:00 JOHN SMITH 2008-03-26 08:47:00 JOHN SMITH 2008-03-26 08:48:00 ALLAN WHITE 2008-03-26 08:43:00 ALLAN WHITE 2008-03-26 08:40:00 JOHN SMITH 2008-03-26 08:48:00
Hi, I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record, is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).
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.
I've been looking for examples online to write a SPROC to get some data. Here are the tables.
Album_Category AlbumCategoryID (PK, int, not null) Caption (nvarchar(max), not null) IsPublic (bit, not null)
Albums AlbumID (PK, int, not null) AlbumCategoryID (int, null) Caption (nvarchar(max), not null) IsPublic (bit, not null)
I need to return: -[Album_Category].[AlbumCategoryID] -[Album_Category].[Caption] -[Albums].[Single AlubmID for each AlbumCategoryID] -[Count of Albums in each AlbumCategory]
I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.
I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.
Hi All In My application when i want to work with sqldatasource for updation and delteion tasks it is working properly when all the columns consists the data. If any of the column consists the null values its not updating and deleting. Advices are needed.Thank uBaba
