July 22, 2016 at 9:08 am
Guys-
I have the following update statement followed by the insert statement. What I am looking to do is use the values of the current update search for the insert of the @@rowcount = 0 values. How can I do this effectively? The primary key is also used with the datetime stamp so the PK is not unique.
Update Table2 Set Table2=Table1 From Table1, Table2
where Table2.PK=Table1.PK
and Table2.DateTm=Table1.Datetm
IF @@ROWCOUNT = 0
Insert Table2(Columns) values(Variables from Update above....)
Here is my live code in the wild so to speak:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE Mtr_Read_KWH_Hourly SET SDP_EffBegDate=temp.SDP_EffBegDate,
Mtr_Read_Status_Id=temp.Mtr_Read_Status_Id,
Src_Del_Flag=temp.Src_Del_Flag,
ETL_Batch_Id=temp.ETL_Batch_Id,
Mtr_Read=temp.Mtr_Read
FROM Mtr_Read_KWH_Hourly_Temp temp, Mtr_Read_KWH_Hourly AS Hourly
WHERE hourly.SDP_Id = temp.SDP_Id and Hourly.Mtr_Read_Dttm = Temp.Mtr_Read_Dttm and Hourly.Mtr_Read_Dttm >= '2015-05-28 07:00:00';
IF @@ROWCOUNT = 0
BEGIN
INSERT Mtr_Read_KWH_Hourly (
SDP_Id,
SDP_EffBegDate,
Mtr_Read_Status_Id,
ETL_Batch_Id,
Src_Del_Flag,
Mtr_Read_Dttm,
Mtr_Read) values(UpdateInsertcolumns....);
END
COMMIT TRANSACTION;
July 22, 2016 at 9:12 am
If I understand correctly, you need an OUTPUT clause in your UPDATE statement.
John
July 22, 2016 at 9:17 am
Yes, but I guess thats where I am not sure whats best. Output insert.* for values or output.insert.column? Its not overly clear in a transactional statement what would be best for a dynamic approach if at all?
July 22, 2016 at 9:27 am
I'm not sure what you mean. There are plenty of examples on that page I linked to. "Inserted." refers to the new value of the column after the update; "Deleted." refers to its value before. You can always try stuff out - you won't lose anything by doing that (provided it's on a test server and you have some way of putting things back to how they were at the start). What do you mean by "dynamic approach"?
John
July 22, 2016 at 9:32 am
John -
So I tried the following in 2008 R2,
OUTPUT Updated.* INTO @UpdateCol and get syntax errors when used before or after the @@rowcount section. I was hoping to have it be:
Update Table1 from table2
if @@RowCount = 0 then
Insert Table1 values(@UpdateCol )
Or something to that effect. Does that make sense? My sql server wont allow this syntax on the output of the Updated records. So perhaps I'm missing something in your suggestion
July 22, 2016 at 9:40 am
Because there's no such virtual table as "Updated". Like I said, Inserted is the values after the update; Deleted is the values before.
John
Edit: By way of further explanation, an update is treated as two statements: a delete followed by an insert. That's why you get the two virtual tables. If you used an OUTPUT clause in an INSERT or DELETE statement, you'd only get one.
July 22, 2016 at 9:47 am
John-
Please bare with me, Im sure you have done this a million times. So I add the output inside the Update statement as below then I can query that in my insert as Select * from Output.* or...Output.columnname, ...
So:
UPDATE Meter_Read_KWH_Hourly SET SDP_EffBegDate=temp.SDP_EffBegDate,
Meter_Read_Status_Id=temp.Meter_Read_Status_Id,
Src_Del_Flag=temp.Src_Del_Flag,
ETL_Batch_Id=temp.ETL_Batch_Id,
Meter_Read=temp.Meter_Read
OUTPUT Inserted.SDP_EffBegDate, etc....
FROM Meter_Read_KWH_Hourly_Temp temp, Meter_Read_KWH_Hourly AS Hourly
WHERE hourly.SDP_Id = temp.SDP_Id and Hourly.Meter_Read_Dttm = Temp.Meter_Read_Dttm and Hourly.Meter_Read_Dttm >= '2015-05-28 07:00:00';
IF @@ROWCOUNT = 0
BEGIN
INSERT Meter_Read_KWH_Hourly (
SDP_Id,
SDP_EffBegDate,
Meter_Read_Status_Id,
ETL_Batch_Id,
Src_Del_Flag,
Meter_Read_Dttm,
Meter_Read) values(Select * from Inserted?);
END
COMMIT TRANSACTION;
July 22, 2016 at 9:53 am
I don't know where you're getting "Output.ColumnName" from. It's either Inserted.ColumnName or Deleted.ColumnName. Do it like it's shown in the examples, and don't be afraid to try stuff out.
John
Edit: when you insert the results of a query into a table, don't use the VALUES keyword or the parentheses:
INSERT INTO MYTable (columnlist)
SELECT columnlist FROM MyOtherTable
July 22, 2016 at 10:00 am
Hmm, this seems to try and just create yet another temp table. I was trying to keep this inline transaction from my already existing update statement not adding more trips. Ill keep playing with it I guess and see if there are other ways to do this. Merge is not a good bet for millions of records and I'm not sure output is what I'm looking for.
July 22, 2016 at 11:53 am
fergfamster (7/22/2016)
John-Please bare with me, Im sure you have done this a million times. So I add the output inside the Update statement as below then I can query that in my insert as Select * from Output.* or...Output.columnname, ...
So:
UPDATE Meter_Read_KWH_Hourly SET SDP_EffBegDate=temp.SDP_EffBegDate,
Meter_Read_Status_Id=temp.Meter_Read_Status_Id,
Src_Del_Flag=temp.Src_Del_Flag,
ETL_Batch_Id=temp.ETL_Batch_Id,
Meter_Read=temp.Meter_Read
OUTPUT Inserted.SDP_EffBegDate, etc....
FROM Meter_Read_KWH_Hourly_Temp temp, Meter_Read_KWH_Hourly AS Hourly
WHERE hourly.SDP_Id = temp.SDP_Id and Hourly.Meter_Read_Dttm = Temp.Meter_Read_Dttm and Hourly.Meter_Read_Dttm >= '2015-05-28 07:00:00';
IF @@ROWCOUNT = 0
BEGIN
INSERT Meter_Read_KWH_Hourly (
SDP_Id,
SDP_EffBegDate,
Meter_Read_Status_Id,
ETL_Batch_Id,
Src_Del_Flag,
Meter_Read_Dttm,
Meter_Read) values(Select * from Inserted?);
END
COMMIT TRANSACTION;
There are several issues with this.
Try the following modification
UPDATE Meter_Read_KWH_Hourly
SET SDP_EffBegDate=temp.SDP_EffBegDate,
Meter_Read_Status_Id=temp.Meter_Read_Status_Id,
Src_Del_Flag=temp.Src_Del_Flag,
ETL_Batch_Id=temp.ETL_Batch_Id,
Meter_Read=temp.Meter_Read
OUTPUT Inserted.SDP_EffBegDate, etc....
-- place your secondary insert here.
INTO Meter_Read_KWH_Hourly (
SDP_Id,
SDP_EffBegDate,
Meter_Read_Status_Id,
ETL_Batch_Id,
Src_Del_Flag,
Meter_Read_Dttm,
Meter_Read)
FROM Meter_Read_KWH_Hourly_Temp temp
INNER JOIN Meter_Read_KWH_Hourly AS Hourly
ON hourly.SDP_Id = temp.SDP_Id
AND Hourly.Meter_Read_Dttm = Temp.Meter_Read_Dttm
WHERE Hourly.Meter_Read_Dttm >= '2015-05-28 07:00:00';
I also replaced your CROSS JOIN with an INNER JOIN, because it clearly delineates which criteria are being used to match records in two tables and which criteria are being used as filters.
Drew
1. There are times when the input to VALUES may appear not to be a scalar expression, but it is being applied to every row of a result set (as in a CROSS APPLY), and each of those is indeed a scalar expression.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 22, 2016 at 12:35 pm
Drew-
Thanks for the input. The table I am updating from is a daily updated temp table to a primary table of 23 billion records. So rather than using Merge I am attempting to perform a single update statement with transaction that then allows insert of the upload fails. The table will never be empty. In addition, the keys are SDP_ID and DateTime both, and there is no auto increment key so using a @@Identity scope is not possible either.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply