Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using MERGE what is wrong with my Stored Procedure Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 1:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:24 AM
Points: 328, Visits: 395
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!
Post #1436710
Posted Thursday, March 28, 2013 4:07 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 4,311, Visits: 6,099
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 at GMail
Post #1436777
Posted Thursday, March 28, 2013 4:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:24 AM
Points: 328, Visits: 395
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

Post #1436783
Posted Thursday, March 28, 2013 4:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:24 AM
Points: 328, Visits: 395
Well there is a sample. No I've not tried BOL on Merge. I'll check it out thanks!
Post #1436784
Posted Thursday, March 28, 2013 4:45 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 4,311, Visits: 6,099
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 at GMail
Post #1436788
Posted Friday, March 29, 2013 7:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:24 AM
Points: 328, Visits: 395
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.

Post #1436909
Posted Friday, March 29, 2013 8:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:24 AM
Points: 328, Visits: 395
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.
Post #1436964
Posted Friday, March 29, 2013 9:02 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 4,311, Visits: 6,099
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 at GMail
Post #1436967
Posted Friday, March 29, 2013 9:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:24 AM
Points: 328, Visits: 395
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.
Post #1436981
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse