RE: Using MERGE what is wrong with my Stored Procedure

Jacob Pressures

SSCertifiable

Points: 5804

March 28, 2013 at 4:20 pm

#1601904

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