October 3, 2012 at 5:23 pm
I have two separate Insert and Update stored procedures and I am looking for a way to add a Variable to the Merge statement.
Here is an example of my two stored procedures:
---UPDATE
ALTER PROCEDURE [dbo].[sp_UpdateSpace]
@PullDate as Date
AS
BEGIN
SET NOCOUNT ON;
update space
set
[space].[SPACE_DESC]=[rawdata_space].[SPACE_DESC]
,[space].[EXTERNAL_SPACE_ID]=[rawdata_space].[EXTERNAL_SPACE_ID]
,[space].[SPACE_SQFT]=[rawdata_space].[SPACE_SQFT]
,[space].[SPACE_TABORDER]=[rawdata_space].[SPACE_TABORDER]
,[space].[LAST_UPDATE_DATE_GMT]=[rawdata_space].[LAST_UPDATE_DATE_GMT]
,[space].[PROPERTYDETAIL_Id]=[rawdata_space].[PROPERTYDETAIL_Id]
,[space].Pulldate = @PullDate
FROM [DBname].[dbo].[RawData_Space]
inner join [DBname].[dbo].[Space] on [Space].space_id=[RawData_Space].space_id
END
---INSERT
ALTER PROCEDURE [dbo].[sp_InsertSpace]
@PullDate date
AS
BEGIN
SET NOCOUNT ON;
insert into Space
SELECT [SPACE_ID]
,[SPACE_DESC]
,[EXTERNAL_SPACE_ID]
,[SPACE_SQFT]
,[SPACE_TABORDER]
,[LAST_UPDATE_DATE_GMT]
,[PROPERTYDETAIL_Id]
,@PullDate as Pulldate
FROM [DBname].[dbo].[RawData_Space]
where SPACE_ID not in (select SPACE_ID from [DBname].[dbo].Space)
END
These two above are examples from multiple insert and update stored procedures I run. I call each insert group of procedures from one main procedure using the following code: (I run another SP to call the update SPs)
ALTER procedure [dbo].[sp_Run_All_Inserts]
@PullDate date
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Date date = Getdate();
EXEC sp_InsertSpace @Date
END
This adds an entry for the date when the records are inserted or updated.
I have used merge and I am fimliar with the basic syntax, my issue is passing the @Date variable and how to add this to my Merge code. Maybe there is another way to do this, but I am stumped!
Any help is very appreciated! Please let me know if more info is needed! Thanks in advance 🙂
October 3, 2012 at 6:17 pm
October 4, 2012 at 1:21 am
just pass any variable ..it works . in the MERGE T-SQL statement.
and yes what merge statement ??
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
October 4, 2012 at 1:56 am
as an example this is how you would put a variable in to a merge
MERGE TargetTable as Target
Using SourceTable as Source
On Source.Key=Target.Key
When Matched
And Target.SomeColumn!=Source.SomeColumn
Then Update Set
Target.SomeColumn2=Source.SomeColumn2
,Target.Somecolumn3=@Variable
WHEN NOT MATCHED BY TARGET THEN
Insert (SomeColumn1
,SomeColumn2
,SomeColumn3)
VALUES (Source.SomeColumn1
,Source.SomeColumn2
,@Variable)
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 4, 2012 at 5:19 am
What part of the Merge Statement are you trying to add the variabe to?
October 4, 2012 at 6:08 am
This should be close.
MERGE [DBname].[dbo].[Space] AS target
USING [DBname].[dbo].[RawData_Space] AS source
ON target.SPACE_ID = source.SPACE_ID
WHEN MATCHED THEN
UPDATE SET
[SPACE_DESC] = source.[SPACE_DESC],
[EXTERNAL_SPACE_ID] = source.[EXTERNAL_SPACE_ID],
[SPACE_SQFT] = source.[SPACE_SQFT],
[SPACE_TABORDER] = source.[SPACE_TABORDER],
[LAST_UPDATE_DATE_GMT] = source.[LAST_UPDATE_DATE_GMT],
[PROPERTYDETAIL_Id] = source.[PROPERTYDETAIL_Id],
Pulldate = @PullDate
WHEN NOT MATCHED THEN
INSERT (
[SPACE_ID],
[SPACE_DESC],
[EXTERNAL_SPACE_ID],
[SPACE_SQFT],
[SPACE_TABORDER],
[LAST_UPDATE_DATE_GMT],
[PROPERTYDETAIL_Id],
Pulldate)
VALUES (
source.[SPACE_ID],
source.[SPACE_DESC],
source.[EXTERNAL_SPACE_ID],
source.[SPACE_SQFT],
source.[SPACE_TABORDER],
source.[LAST_UPDATE_DATE_GMT],
source.[PROPERTYDETAIL_Id],
@PullDate)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2012 at 9:25 am
ChrisM@Work (10/4/2012)
This should be close.
MERGE [DBname].[dbo].[Space] AS targetUSING [DBname].[dbo].[RawData_Space] AS source
ON target.SPACE_ID = source.SPACE_ID
WHEN MATCHED THEN
UPDATE SET
[SPACE_DESC] = source.[SPACE_DESC],
[EXTERNAL_SPACE_ID] = source.[EXTERNAL_SPACE_ID],
[SPACE_SQFT] = source.[SPACE_SQFT],
[SPACE_TABORDER] = source.[SPACE_TABORDER],
[LAST_UPDATE_DATE_GMT] = source.[LAST_UPDATE_DATE_GMT],
[PROPERTYDETAIL_Id] = source.[PROPERTYDETAIL_Id],
Pulldate = @PullDate
WHEN NOT MATCHED THEN
INSERT (
[SPACE_ID],
[SPACE_DESC],
[EXTERNAL_SPACE_ID],
[SPACE_SQFT],
[SPACE_TABORDER],
[LAST_UPDATE_DATE_GMT],
[PROPERTYDETAIL_Id],
Pulldate)
VALUES (
source.[SPACE_ID],
source.[SPACE_DESC],
source.[EXTERNAL_SPACE_ID],
source.[SPACE_SQFT],
source.[SPACE_TABORDER],
source.[LAST_UPDATE_DATE_GMT],
source.[PROPERTYDETAIL_Id],
@PullDate)
Thanks! I had started writing the merge code for a different set of tables and used the examples I posted above to give an idea of the two SPs i wanted to merge. I was going to post my intended code today (once I wrote it) but the code I quoted from ChrisM is exactly what I was planning to use. (Now with the variable added!) I didnt intend on getting free coding writing service here, but thanks! 😛
I was getting stuck with the insert part, and how to properly split the declared variable and its value.
Now I will test this on my dev server and report back when I have success!
Thanks to everyone here, this forum has helped tons!
October 5, 2012 at 2:37 pm
Got it working thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply