March 28, 2013 at 1:50 pm
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
@FileNameVARCHAR(256) = NULL
, @FilePathVARCHAR(1024) = NULL
, @cDBConnection VARCHAR(256) = NULL
, @rDBConnection VARCHAR(256) = NULL
, @CustomerIDINT
, @ImportTFBIT
, @cpTFBIT
, @FilterTFBIT
, @MatchTFBIT
, @exportTFBIT
, @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!
March 28, 2013 at 4:07 pm
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
March 28, 2013 at 4:20 pm
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
March 28, 2013 at 4:21 pm
Well there is a sample. No I've not tried BOL on Merge. I'll check it out thanks!
March 28, 2013 at 4:45 pm
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
@FileNameVARCHAR(256) = NULL
, @FilePathVARCHAR(1024) = NULL
, @cDBConnection VARCHAR(256) = NULL
, @rDBConnection VARCHAR(256) = NULL
, @CustomerIDINT
, @ImportTFBIT
, @cpTFBIT
, @FilterTFBIT
, @MatchTFBIT
, @exportTFBIT
, @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
March 29, 2013 at 7:16 am
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.
March 29, 2013 at 8:41 am
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.
March 29, 2013 at 9:02 am
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
March 29, 2013 at 9:31 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply