April 16, 2014 at 2:57 pm
Hi All,
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
,@ComputerModel varChar(64) = NULL
,@NumberProcessors INT = NULL
,@TotalPhysicalMemory BIGINT = NULL
,@NewCompID INT OUTPUT
)
AS
BEGIN
MERGE INTO [dbo].ComputerInformation AS TARGET
USING (VALUES (
@FullComputerName
,@ComputerDescription
,@ComputerSystemType
,@ComputerManufacturer
,@ComputerModel
,@NumberProcessors
,@TotalPhysicalMemory
)
) AS SOURCE (
[FullComputerName]
,[ComputerDescription]
,[ComputerSystemType]
,[ComputerManufacturer]
,[ComputerModel]
,[NumberProcessors]
,[TotalPhysicalMemory]
)
ON TARGET.[FullComputerName] = SOURCE.[FullComputerName]
WHEN MATCHED
THEN UPDATE
SET [FullComputerName] = @FullComputerName
,[ComputerDescription] = @ComputerDescription
,[ComputerSystemType] = @ComputerSystemType
,[ComputerManufacturer] = @ComputerManufacturer
,[ComputerModel] = @ComputerModel
,[NumberProcessors] = @NumberProcessors
,[TotalPhysicalMemory] = @TotalPhysicalMemory
,[EntryDate] = GETDATE()
WHEN NOT MATCHED
THEN INSERT(
[FullComputerName]
,[ComputerDescription]
,[ComputerSystemType]
,[ComputerManufacturer]
,[ComputerModel]
,[NumberProcessors]
,[TotalPhysicalMemory]
,[EntryDate]
)
VALUES(
@FullComputerName
,@ComputerDescription
,@ComputerSystemType
,@ComputerManufacturer
,@ComputerModel
,@NumberProcessors
,@TotalPhysicalMemory
,GETDATE()
);
SET @NewCompID = SCOPE_IDENTITY();
RETURN 0;
END
Thank you.
gdr
April 16, 2014 at 3:15 pm
Take a look at the documentation for MERGE. http://msdn.microsoft.com/en-us/library/bb510625.aspx
Look closely at the OUTPUT clause. You should be able to utilize that for what you need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2014 at 2:03 pm
Hi Sean,
Thanks for the "where-to-look" directions. I added a temp table, populated it using INSERTED.ComputerPKID INTO @MergeOutput and then was able to SELECT it out to the Output parameter, @NewCompID. I've tested it a couple of dozen times, but I still need to ask the question...Is there anything, that you can see or know, that will prevent this from working as I think it should?
Thank you again.
gdr
Completed code follows:
CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL
,@ComputerModel varChar(64) = NULL
,@NumberProcessors INT = NULL
,@TotalPhysicalMemory BIGINT = NULL
,@InsertedCompID INT = NULL
,@NewCompID INT OUTPUT
)
AS
BEGIN
-- declare @MergeOutput1 table variable
DECLARE @MergeOutput table
(
InsertedCompID INT
);
MERGE INTO [dbo].ComputerInformation AS TARGET
USING (VALUES (
@FullComputerName
,@ComputerDescription
,@ComputerSystemType
,@ComputerManufacturer
,@ComputerModel
,@NumberProcessors
,@TotalPhysicalMemory
)
) AS SOURCE (
[FullComputerName]
,[ComputerDescription]
,[ComputerSystemType]
,[ComputerManufacturer]
,[ComputerModel]
,[NumberProcessors]
,[TotalPhysicalMemory]
)
ON TARGET.[FullComputerName] = SOURCE.[FullComputerName]
WHEN MATCHED
THEN UPDATE
SET [FullComputerName] = @FullComputerName
,[ComputerDescription] = @ComputerDescription
,[ComputerSystemType] = @ComputerSystemType
,[ComputerManufacturer] = @ComputerManufacturer
,[ComputerModel] = @ComputerModel
,[NumberProcessors] = @NumberProcessors
,[TotalPhysicalMemory] = @TotalPhysicalMemory
,[EntryDate] = GETDATE()
WHEN NOT MATCHED
THEN INSERT(
[FullComputerName]
,[ComputerDescription]
,[ComputerSystemType]
,[ComputerManufacturer]
,[ComputerModel]
, [NumberProcessors]
,[TotalPhysicalMemory]
,[EntryDate]
)
VALUES(
@FullComputerName
,@ComputerDescription
,@ComputerSystemType
,@ComputerManufacturer
,@ComputerModel
,@NumberProcessors
,@TotalPhysicalMemory
,GETDATE()
)
OUTPUT INSERTED.ComputerPKID INTO @MergeOutput;
SET @NewCompID = (SELECT * FROM @MergeOutput);
RETURN 0;
END
April 17, 2014 at 2:10 pm
Seems like it should work. However, you should NOT use select * when populating your variable. You should ALWAYS use the actual column name. Consider what happens if at some point you want to add another column to the table. Your code will be broken. Also there really is no need to combine set and select in that statement.
SELECT @NewCompID = InsertedCompID
FROM @MergeOutput
Simple and precise!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2014 at 8:45 pm
Thank you Sean....
I'd've changed the * before it went to production....too many years of Access to not to. 😀
Thanks for the output shortcut too!
gdr
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply