SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using MERGE what is wrong with my Stored Procedure


Using MERGE what is wrong with my Stored Procedure

Author
Message
Jacob Pressures
Jacob Pressures
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 410
Can you tell me what could be wrong with my SP? I'm using Merge and I'm new to it. What i was hoping is that this query would check to see if a FileName i tried to insert was already there and if there was NO match insert it into the table. But it doesn't work. I assume it must be something obvious but if i need to i can create the table and some sample data.

The actual Execution code looks like this:


EXEC InsertBatches @FileName= 'af', @CustomerID = 9999, @ImportTF = 1, @cpTF = 1, @FilterTF = 1, @MatchTF = 1, @exportTF = 1, @InitialCutoffDate='1/1/2020', @TerminalCutoffDate='1/1/2020';





CREATE PROCEDURE InsertBatches
@FileName VARCHAR(256) = NULL
, @FilePath VARCHAR(1024) = NULL
, @cDBConnection VARCHAR(256) = NULL
, @rDBConnection VARCHAR(256) = NULL
, @CustomerID INT
, @ImportTF BIT
, @cpTF BIT
, @FilterTF BIT
, @MatchTF BIT
, @exportTF BIT
, @InitialCutoffDate DATETIME
, @TerminalCutoffDate DATETIME
AS

BEGIN

SET NOCOUNT ON;

BEGIN TRY
BEGIN TRANSACTION

MERGE Batches b
USING (SELECT ID, FileName, cDBConnection, rDBConnection, CustomerID
FROM Batches
WHERE FileName = @FileName AND cDBConnection = @cDBConnection) o
ON o.ID = b.ID
WHEN NOT MATCHED THEN

INSERT (FileName, cDBConnection, rDBConnection, CustomerID , ImportTF, cpTF, FilterTF, MatchYN, exportYN, InitialCutoffDate, TerminalCutoffDate)
VALUES (@FileName, @cDBConnection, @rDBConnection, @CustomerID , @ImportTF, @cpTF, @FilterTF, @MatchTF, @exportTF, @InitialCutoffDate, @TerminalCutoffDate);

COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(500)
SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback InsertBatches '
Print @ErrorMessage
ROLLBACK TRANSACTION
RAISERROR (@ErrorMessage, 16,1)
END CATCH

END
GO






Thanks!
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12337 Visits: 8546
it is always best to provide table scripts, sample data and expected outcomes.

BTW, did you review MERGE in Books Online? There are some VERY good examples there to guide you.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jacob Pressures
Jacob Pressures
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 410
Here is the table structure and some sample data.


CREATE TABLE [Batches](
[FileName] [varchar](256) NULL,
[cpFolderPath] [varchar](1024) NULL,
[eFolderPath] [varchar](1024) NULL,
[PayerPath] [varchar](1024) NULL,
[ProviderPath] [varchar](1024) NULL,
[cdbConnection] [varchar](256) NULL,
[rdbConnection] [varchar](256) NULL,
[CustomerID] [int] NOT NULL,
[ImportTF] [bit] NOT NULL,
[cpTF] [bit] NOT NULL,
[FilterTF] [bit] NOT NULL,
[MatchTF] [bit] NOT NULL,
[ExportTF] [bit] NOT NULL,
[InitialCutoffDate] [datetime] NOT NULL,
[TerminalCutoffDate] [datetime] NOT NULL,
[StatusID] [int] NOT NULL,
[StartDateTime] [datetime] NOT NULL,
[EndDateTime] [datetime] NULL,
[ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [Batches] ON

INSERT [Batches] ([FileName], [cpFolderPath], [eFolderPath], [PayerPath], [ProviderPath], [cdbConnection], [rdbConnection], [CustomerID], [ImportTF], [cpTF], [FilterTF], [MatchTF], [ExportTF], [InitialCutoffDate], [TerminalCutoffDate], [StatusID], [StartDateTime], [EndDateTime], [ID]) VALUES (N'ex_20121009_florida-ny-a', N'\\mwww\ex_20121009_ny-a', N'\\mwww\BoyOhBoy\dojo\ex_20121009_un-a', NULL, NULL, NULL, NULL, 15, 1, 1, 1, 1, 1, CAST(0x00009C9500000000 AS DateTime), CAST(0x00011BEC00000000 AS DateTime), 4, CAST(0x0000A17300BB6E30 AS DateTime), CAST(0x0000A17300D2970F AS DateTime), 1)
INSERT [Batches] ([FileName], [cpFolderPath], [eFolderPath], [PayerPath], [ProviderPath], [cdbConnection], [rdbConnection], [CustomerID], [ImportTF], [cpTF], [FilterTF], [MatchTF], [ExportTF], [InitialCutoffDate], [TerminalCutoffDate], [StatusID], [StartDateTime], [EndDateTime], [ID]) VALUES (N'ex_20130129_alabama-a', N'\\mwww\o_20130129_-a', N'\\mwww\BoyOhBoy\dojo\ex_20130129_ths-a', NULL, NULL, NULL, NULL, 17, 1, 1, 1, 1, 1, CAST(0x00009DA600000000 AS DateTime), CAST(0x00011BEC00000000 AS DateTime), 4, CAST(0x0000A17B00FCD183 AS DateTime), NULL, 2)
INSERT [Batches] ([FileName], [cpFolderPath], [eFolderPath], [PayerPath], [ProviderPath], [cdbConnection], [rdbConnection], [CustomerID], [ImportTF], [cpTF], [FilterTF], [MatchTF], [ExportTF], [InitialCutoffDate], [TerminalCutoffDate], [StatusID], [StartDateTime], [EndDateTime], [ID]) VALUES (N'ex_20130218_miss-a', N'\\mwww\o_20130218_a', N'\\mwww\BoyOhBoy\dojo\ex_20130218_beth-a', NULL, NULL, NULL, NULL, 13, 0, 0, 1, 1, 1, CAST(0x00009E5E00000000 AS DateTime), CAST(0x00011BEC00000000 AS DateTime), 4, CAST(0x0000A17300B66DB2 AS DateTime), CAST(0x0000A17300B868A8 AS DateTime), 3)
INSERT [Batches] ([FileName], [cpFolderPath], [eFolderPath], [PayerPath], [ProviderPath], [cdbConnection], [rdbConnection], [CustomerID], [ImportTF], [cpTF], [FilterTF], [MatchTF], [ExportTF], [InitialCutoffDate], [TerminalCutoffDate], [StatusID], [StartDateTime], [EndDateTime], [ID]) VALUES (N'ex_20130207_tenn-a', N'\\mwww\o_20130207_a', N'\\mwww\BoyOhBoy\dojo\ex_20130207_first-a', NULL, NULL, NULL, NULL, 4, 0, 0, 0, 0, 1, CAST(0x00009EF500000000 AS DateTime), CAST(0x00011BEC00000000 AS DateTime), 4, CAST(0x0000A17900DD27AC AS DateTime), CAST(0x0000A17900DDD30A AS DateTime), 4)
INSERT [Batches] ([FileName], [cpFolderPath], [eFolderPath], [PayerPath], [ProviderPath], [cdbConnection], [rdbConnection], [CustomerID], [ImportTF], [cpTF], [FilterTF], [MatchTF], [ExportTF], [InitialCutoffDate], [TerminalCutoffDate], [StatusID], [StartDateTime], [EndDateTime], [ID]) VALUES (N'ex_20130227_potdee', NULL, NULL, NULL, NULL, N'Data Source=dcDB2aps\dcDB202;Initial Catalog=Direct;Provider=DB211.1;User Id=BoyOhBoyLS;Password=!BoyOhBoyLS!;', N'Data Source=MSCONFIG;Initial Catalog=ERCurrent;Provider=DB2NCLI1Integrated Security=DB2;Auto Translate=True;', 18, 0, 1, 1, 1, 1, CAST(0x00009E5E00000000 AS DateTime), CAST(0x00011BEC00000000 AS DateTime), 4, CAST(0x0000A17301095213 AS DateTime), CAST(0x0000A173010A20B5 AS DateTime), 5)
INSERT [Batches] ([FileName], [cpFolderPath], [eFolderPath], [PayerPath], [ProviderPath], [cdbConnection], [rdbConnection], [CustomerID], [ImportTF], [cpTF], [FilterTF], [MatchTF], [ExportTF], [InitialCutoffDate], [TerminalCutoffDate], [StatusID], [StartDateTime], [EndDateTime], [ID]) VALUES (N'ex_20130228_capetownhs-nh-a', N'\\mwww\o20130228_capetownhs-nh-a', N'\\mwww\BoyOhBoy\dojo\ex_20130228_capetownhs-nh-a', NULL, NULL, NULL, NULL, 5, 0, 0, 0, 0, 1, CAST(0x00009ED600000000 AS DateTime), CAST(0x00011BEC00000000 AS DateTime), 4, CAST(0x0000A179008DE2DB AS DateTime), NULL, 6)
INSERT [Batches] ([FileName], [cpFolderPath], [eFolderPath], [PayerPath], [ProviderPath], [cdbConnection], [rdbConnection], [CustomerID], [ImportTF], [cpTF], [FilterTF], [MatchTF], [ExportTF], [InitialCutoffDate], [TerminalCutoffDate], [StatusID], [StartDateTime], [EndDateTime], [ID]) VALUES (N'cp_20130320_hospital-a', N'\\mwww\ATE\ospital-a', N'\\mwww\porting\Customer\Swope\Staging2\ex_20130326_dodo-a', N'\\mwww\BoyOhBoy\Billporting\Customer\Swope\LocalCopywal-a\Outing\a_Payer.csv', NULL, NULL, NULL, 19, 0, 0, 1, 1, 1, CAST(0x00009CF100000000 AS DateTime), CAST(0x00011BEC00000000 AS DateTime), 4, CAST(0x0000A18E00DCCEA7 AS DateTime), CAST(0x0000A18E00DF4A9F AS DateTime), 7)
INSERT [Batches] ([FileName], [cpFolderPath], [eFolderPath], [PayerPath], [ProviderPath], [cdbConnection], [rdbConnection], [CustomerID], [ImportTF], [cpTF], [FilterTF], [MatchTF], [ExportTF], [InitialCutoffDate], [TerminalCutoffDate], [StatusID], [StartDateTime], [EndDateTime], [ID]) VALUES (N'cp_20130319_windows-a', N'\\mwww\ATE\Billporting\Customer\kl\ham-a', N'\\mwww\BoyOhBoy\Billporting\Customer\Swope\Staging2\ex_20130325_windham-a', N'\\BoyOhBoy\Billporting\Customer\Swope\LocalCopy\Outing\a_Payer.csv', NULL, NULL, NULL, 20, 0, 0, 1, 1, 1, CAST(0x00009FCB00000000 AS DateTime), CAST(0x00011BEC00000000 AS DateTime), 4, CAST(0x0000A18E00F42A71 AS DateTime), CAST(0x0000A18E00F4B82A AS DateTime), 8)
INSERT [Batches] ([FileName], [cpFolderPath], [eFolderPath], [PayerPath], [ProviderPath], [cdbConnection], [rdbConnection], [CustomerID], [ImportTF], [cpTF], [FilterTF], [MatchTF], [ExportTF], [InitialCutoffDate], [TerminalCutoffDate], [StatusID], [StartDateTime], [EndDateTime], [ID]) VALUES (N'cp_20130325_wu-unitedwestand-a', N'\\mwww\porting\a', N'', N'', NULL, NULL, NULL, 21, 0, 1, 1, 0, 0, CAST(0x00009E5E00000000 AS DateTime), CAST(0x00011BEC00000000 AS DateTime), 4, CAST(0x0000A18E00DF4AA6 AS DateTime), CAST(0x0000A18E00E08612 AS DateTime), 9)
INSERT [Batches] ([FileName], [cpFolderPath], [eFolderPath], [PayerPath], [ProviderPath], [cdbConnection], [rdbConnection], [CustomerID], [ImportTF], [cpTF], [FilterTF], [MatchTF], [ExportTF], [InitialCutoffDate], [TerminalCutoffDate], [StatusID], [StartDateTime], [EndDateTime], [ID]) VALUES (N'cp_20130328_Redo', NULL, NULL, NULL, NULL, N'Data Source=dcDB2aps\dcDB202;Initial Catalog=Direct;Provider=DB2NCLI Id=BoyOhBoyLS;Password=!BoyOhBoyLS!;', N'Data Source=MSCONFIG;Initial Catalog=ER;Provider=DB2NCLI11.1;Integrated Security=SI;Auto Translate=True;', 22, 1, 1, 1, 0, 0, CAST(0x00009E5E00000000 AS DateTime), CAST(0x00011BEC00000000 AS DateTime), 1, CAST(0x0000A18F00A34B1A AS DateTime), NULL, 10)
SET IDENTITY_INSERT [Batches] OFF


Jacob Pressures
Jacob Pressures
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 410
Well there is a sample. No I've not tried BOL on Merge. I'll check it out thanks!
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12337 Visits: 8546
1) ALWAYS USE BOL!!! It is an AWESOME resource. I NEVER have it closed, and I have been working with SQL Server for 15+ years! :-)

2) Your sample and sproc had numerous problems. This should get you most of the way there:



ALTER PROCEDURE InsertBatches
@FileName VARCHAR(256) = NULL
, @FilePath VARCHAR(1024) = NULL
, @cDBConnection VARCHAR(256) = NULL
, @rDBConnection VARCHAR(256) = NULL
, @CustomerID INT
, @ImportTF BIT
, @cpTF BIT
, @FilterTF BIT
, @MatchTF BIT
, @exportTF BIT
, @InitialCutoffDate DATETIME
, @TerminalCutoffDate DATETIME
AS

BEGIN

SET NOCOUNT ON;

BEGIN TRY
BEGIN TRANSACTION

;MERGE Batches AS b
USING (VALUES (@FileName, @cDBConnection)) AS o (FileName, cDBConnection)
ON b.FileName = o.FileName AND b.cDBConnection = o.cDBConnection

WHEN NOT MATCHED BY TARGET THEN

INSERT (FileName, cDBConnection, rDBConnection, CustomerID , ImportTF, cpTF, FilterTF, MatchTF, exportTF, InitialCutoffDate, TerminalCutoffDate, StatusID, StartDateTime, EndDateTime)
VALUES (@FileName, @cDBConnection, @rDBConnection, @CustomerID , @ImportTF, @cpTF, @FilterTF, @MatchTF, @exportTF, @InitialCutoffDate, @TerminalCutoffDate, 0, GETDATE(), GETDATE());

COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(500)
SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback InsertBatches '
Print @ErrorMessage
ROLLBACK TRANSACTION
RAISERROR (@ErrorMessage, 16,1)
END CATCH

END
GO


BEGIN TRAN

EXEC InsertBatches @FileName= 'af', @CustomerID = 9999, @ImportTF = 1, @cpTF = 1, @FilterTF = 1, @MatchTF = 1, @exportTF = 1, @InitialCutoffDate='1/1/2020', @TerminalCutoffDate='1/1/2020';

SELECT *
FROM BATCHES
WHERE filename = 'af'

ROLLBACK TRAN



Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jacob Pressures
Jacob Pressures
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 410
I could never understand the BOL so I've always avoided it. But I'll take a look.

Sorry about the sample data. I got into a rush and should have tested it. I had to change somethings given that it was connection strings and passwords.
Jacob Pressures
Jacob Pressures
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 410
So how do you find this on BOL? I've been searching all over MS website and all i can find is one article.

This is the only thing i've found.
http://msdn.microsoft.com/en-us/library/bb510625.aspx

BOL is very frustrating.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12337 Visits: 8546
That isn't an article, that is the actual BOL entry for MERGE. It has examples (including one that is specifically for your solution). However, I STRONGLY recommend you download and install the local copy of BOL 2012. More/better, and you don't have delay of online access, etc.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jacob Pressures
Jacob Pressures
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 410
See how lost i was, i didn't even know that was the actual BOL on Merge. I thought i was in the wrong place. ;-) I should have it on my laptop at home but this is my work computer and I don't think its installed.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search