Transact SQL :: Managing First Application Of MERGE Statement In ETL Logic
Apr 20, 2015
In order to feed a fact table of a dwh from a staging table I'm using the MERGE statement in order to control insert and update operations. It is possible that the staging table has duplicate rows respect to the fields controlled in the merge condition:
When I run the first time the MERGE statement unwanted rows could be inserted in the fact table.
Does the MERGE statement allow to manage this case or do I need to filter data from the staging table before to write them into the fact table?
I have a stored procedure that runs (SQL Server 2012 (SP1) Standard Ed) daily and I never had any problem with this stored procedure. However, there is MERGE statement on the stored procedure and I see an error saying that the MERGE statement failed..Here are the stored procedure and error message:
-- FlushQueue CREATE PROCEDURE [dbo].[FlushQueue] (@RowCount as int = 10000) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON;
[code]....
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. [SQLSTATE 42000] (Error 8672). The step failed.
Table definition:---CREATE TABLE [dbo].[ImportDefinitions] ( [NodeName] [varchar](20) NOT NULL, [ProcedureName] [varchar](100) NOT NULL, [FilePrefix] [varchar](20) NOT NULL, [ImportDelay] [int] NOT NULL CONSTRAINT [DF_ImportDefinitions_ImportDelay] DEFAULT ((0)),
In the following t-sql 2012 merge statement, the insert statement works but the update statement does not work. I know that is true since I looked at the results of the update statement:
Merge TST.dbo.LockCombination AS LKC1 USING (select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId from [LockerPopulation] A JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ...
Thus can you show me some t-sql 2012 that I can use to make update statement work in the merge function?
I was using Type 2 for one of our Fact table.... and need to put a flag to know which one is the Current record... I couldn't able to figure how to implement logic in the merge statement... This is an example Query ....I was using like this for my fact table...
Basically I need to track CustomerName and City... So I need a Currentflag (Y) for latest record....
MERGE INTO [dbo].[TargetCustomer] AS TRG USING [dbo].[MyCustomers] AS SRC ON TRG.[CustomerID] = SRC.[CustomerID] AND TRG.[CustomerName]=SRC.[CustomerName] AND TRG.[City]=SRC.[City]
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1 USING (select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber from [Inputtb] A JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[code]...
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
I am new to use MERGE statement. The MERGE cannot find any match Cardnumber in the target table. It inserts row into an existing row on the target table causing SQL rejected with duplicate key not allowed. The CardNumber is defined as a primary key on the target table with no duplicate allowed. Below snippet stop when MERGE insert a row exists on the target. The source table contains multiple rows with the same Cardnumber because it is a transactional table with multiple redemptions.
If MERGE cannot handle many (source) to one (target) relationship, what other method that I can change to in order to update the target GiftCard table which keeps track of gift card balance?
Below is the error message:
Msg 2627, Level 14, State 1, Line 5 Violation of PRIMARY KEY constraint 'PK_GiftCard'. Cannot insert duplicate key in object 'dbo.GiftCard'. The duplicate key value is (63027768).
I have got a business logic update conflict handler working, but I have had to work round what appears to be a bug.
Please can someone confirm if this is indeed a bug and if it is a known bug?
My conflict handler needs to take some columns from the publisher row and some from the subscriber row in the event of conflict.
I can quite happily generate a custom dataset which contains the winning row that I want I can see that because I can step through the conflict handler with debug when a conflict occurs.
However, just returning ActionOnUpdateConflict.AcceptCustomConflictData from the UpdateConflictsHandler method does not set the publisher and subscriber columns correctly. I end up with different values on the two databases.
I have found that the only way to get the correct rows on both publisher and subscriber is to create a new ADO connection to the publisher and actually perform an update updating all the modified columns. This now works reliably in my testing.
Fortunately, due to business rules the frequency of update conflicts are likely to be very infrequent, but I would very much like to avoid having to do the unnecessary update.
Notes:
I am using column level tracking but I have seen the problem with row level tracking too I have mainly been using SP1 but have repeated the test on a configuration using the SP2 CTP and the problem occurs there too The problem is not due to complex logic in my code. If the method just sets customDataSet = publisherDataSet.Copy and then returns ActionOnUpdateConflict.AcceptCustomConflictData, the changed and winning publisher values are not sent to the subscriber
ALTER TABLE [dbo].[bkrm_lendcoll] ADD CONSTRAINT ReqdCovgAmt_constraint33 CHECK ( case when CovgAmt = 0 and ReqdCovgAmt = 0 then 1 when CovgAmt = 0 and ReqdCovgAmt = 1 then 1 when CovgAmt = 1 and ReqdCovgAmt = 0 then 1 when CovgAmt = 1 and ReqdCovgAmt = 1 then 0 end =1 )
This is my first attempt to add a constraint for business logic. The desired behavior is that the two columns together have the same behavior as a radio button. (one can be true, the other true, both can be false, but both cannot be true.) I get this error when I attempt to update it.
The ALTER TABLE statement conflicted with the CHECK constraint "ReqdCovgAmt_constraint33". The conflict occurred in database "Std", table "dbo.lendcoll".
So, basically my question is, when you have two bit columns and want them to have the truth table such as described, how can I set a Check constraint to enforce this?
There are two seperate jobs,Job A and Job B, which run and insert records in a table. Job A runs first and then Job B runs. The task is to overwrite Job B records if Job A and Job B have same
We have a table X with a gender column taking values M(male) or F(Female).There are 52 rows in the table X.We want to update all the rows with Gender M as F and update all remaining F as males (M) in a single Update Query.Im looking for the exact logic we would write in the single update statement. Please help me out.
I'd like to make a logic statement, that would take as arguments result of the sql select query. In more details: I would like to create a local Bool variable that would be false if some value is NULL in the table (or select query).Query example:select taskID from Users where Login=@usernameWhich classes/methods should i use to solve this problem? I use SqlDataSource to get access to database and i think i should use something like SqlDataSource.UpdateCommand and SqlDataSource.UpdateParameters but dont know how to build from this a logic statement.Thanks in advance
hi.I am having probelms with an update statement. every timei run it, "every" row updates, not just the one(s) intended.so, here is what i have. i have tried this with both AND and ORand neither seem to work.i dont know why this is elluding me, but i'd appreciate help with thesolution.thanks.UPDATE addSET add_s = 1WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'or add_s in(SELECT a.add_sFROM add a, edit eWHERE a.email_address = e.email_addressand e.public_name = 'professor')
I am developing for a customer and they want a search facility that uses boolean logic and special characters. So they want to be able to add "AND" "OR" "NOT" "*" and "?". And for this to effect the search in the predicted way and ranked. I was wondering if there is any examples of this type of search implemented?
Aim – when Fee_Code = ‘42B’ and month_end_date =>2013-02-01 change the Fee_Code from “42B” to “42C”. Anything prior to 2013-02-01 the fee_code needs to remain the same
I can do this as a case statement(as seen below) but this creates a new column. How can i overwrite this logic in the fee_code column ?My query is
SELECT FDMSAccountNo, Fee_Code, month_end_date, sum(Fact_Fee_History.Retail_amount) as 'PCI', Case when fee_code = '42B' and (month_end_date >='2013-02-01') then '42C' end as Test from Fact_Fee_History
I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue AS ( declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt return @ConcatenatedString )
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
I would like to wrap the following code in a function and reuse it. I use this code in many triggers.
DECLARE @Action as char(1); SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) THEN 'U' -- Set Action to Updated. WHEN EXISTS(SELECT * FROM INSERTED) THEN 'I' -- Set Action to Insert. WHEN EXISTS(SELECT * FROM DELETED) THEN 'D' -- Set Action to Deleted. ELSE NULL -- Skip. It may have been a "failed delete". END)
Is it possible to write a function and pass the INSERTED and DELETED logical tables to it?
I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):
UPDATE dbo.target_table set target_column = case when source_column_1= 'ABC'then 'XYZ' when source_column_2= '123'then 'PDQ'
[Code] ....
The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.
The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.
What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.
I have two temp. tables. I am trying to show the agents how makes the sales and the ones how didnt make sales based on the time that they clock in. One table is called #sales which has only the agents that make sales and other tables is #hours which has both agents that do not make sale. the problem is that I can not get both agents to show on my report. I tried different ways but I could not. #sales table uses (select statement from AmountStats table that stores only the agents who make sale). #hours table uses different tables to store all gents who makes sale and ones that are not making sale.
Using SQL Server 2014 i try to merge data from database [Susi] on server2 to database [Susi] on server1. Server2 is a linked server in server1. The PK of the table Core.tKontakte is uniqueidentifier with rowguidcol.
I wrote the following script and get error 206: "uniqueidentifier ist inkompatibel mit int".
INSERT Core.tKontakte (KontaktID, AnredeID, Titel, Nachname) SELECT KontaktID, AnredeID, Titel, Nachname FROM [Susi].MSCMS.Core.tKontakte AS Client WHERE NOT EXISTS (SELECT KontaktID FROM Core.tKontakte AS Host WHERE Host.KontaktID = Client.KontaktID);
I am trying to create a merge publication in sql server 2005 and a merge subscription in sql server 2005 mobile through transact sql using sql server query analyzer.
I am succeeded in creating the publication but not subscription.
I am using sp_addmergepublication and sp_addmergesubscription.
sp_addmergepublication is working fine.
But the problem is in creating sp_addmergesubscription.
Can anyone please guide me how to create subscription to sql server 2005 mobile database using transact sql.
Hello, I have two tables: Orders and Appoitment. Each order can have up to 2 appointments. Now, I need a SELECT statement that gives me this:
ORDER APPT 1 appt1 appt2 2 appt1 appt2
and not this:
ORDER APPT 1 appt1 1 appt2 2 appt1 2 appt2
In other words, I want to merge the two appointments for each order. I tried using the merge statement but it does not work. Tried to google but saw nothing. My database is SQL server. Please help. Thanks
We have a current database table (PAF) that had a new column added to it named 'Email'. This table also has some other columns including one named [Employee Number].We also have an Excel spreadsheet that has 2 columns 'Employee Number' and 'E-mail Address'. I need to take the E-mail Address field from the spreadsheet, match it up with the employee number between the spreadsheet and PAF table, and then insert the email address into the database column.I'm guessing I would do this using a MERGE statement, correct?
I want to combine update and insert statement into single statement as follows.
MERGE INTO MyTable USING MyTempTable ON MyTempTable.MatchingField1 = MyTable.MatchingField1 WHEN MATCHED THEN UPDATE UpdateField1 = MyTempTable.UpdateField1 WHEN NOT MATCHED THEN INSERT VALUES(MyTempTable.MatchingField1, MyTempTable.UpdateField1)
Currently if I try to run this stmt, it gives error "Incorrect syntax near the keyword 'INTO'."
I used the MERGE function for the first time. Now I have to create a pipe-delimited delta file for a 3rd party client of any deltas that may exist in our database.
What is the best way to do this? I have OUTPUT to a result set of the deltas...but I have to send over the entire table to the 3rd party via a pipe-delimited file.
How to summarise the data in this table to a single row output per site (2 records for every SiteID). I am based in the UK so the data copied from SQL is in the US format. I can convert this to UK date without any issues.
CREATE TABLE [dbo].[MRMReadings]( [SiteIncomeID] [int] IDENTITY(1,1) NOT NULL, [SiteID] [nchar](5) NOT NULL,
[Code] ....
Is it possible to return the data in the following format merging 2 lines of data into one output:
I now want to merge those single records, which follow a pattern. For the above case this would be Row 1+2+3, so the result should be:
Row Leg-ID From To On DateFrom DateTo DOW 1 ABC123 AAA BBB CCC 01JAN15 14JAN15 1 3 ABC123 XXX YYY ZZZ 14JAN15 14JAN15 1
The pattern is, that the legs from Row 1 and 2 have identical attributes (Leg-ID, From, To, On, DOW) and are on consecutive weeks on the same weekday. I was doing this through a while-look:
check if there is a record matching the following week (a.DateTo = dateadd(d, 7, b.DateFrom))if there is a match, then update the previous week record DateTo with the following week DateFromdelete the following week record but this is very slow, for 50T rows it runs approx. 6 hrs to shrink everything..
I'm trying to use merge data from a staging table to a production table. There are a lot of duplicate values for serverName and I only want to insert one instance where there are duplicates.
How I can adapt the code I have so far to achieve this?
MERGE tblServer AS TARGET USING tblTemp AS SOURCE ON (TARGET.serverName = SOURCE.serverName)
WHEN MATCHED THEN UPDATE SET TARGET.serverName = SOURCE.serverName, TARGET.serverLocation = SOURCE.serverLocation
WHEN NOT MATCHED BY TARGET THEN INSERT (serverName, serverLocation) VALUES (SOURCE.serverName, SOURCE.serverLocation)
