dbo.spget_data_modulsupplier

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spget_data_modulsupplier
(@dbpath varchar(500))
AS
BEGIN
  declare @filexls varchar(max)
  Declare @tblSupplier varchar(MAX) 
  
   SET ANSI_NULLS ON
 SET ANSI_WARNINGS ON

  
  set @tblSupplier= 'tblSupplier' + replace(convert(varchar(255) , newid()), '-','')
 
  exec(
' declare @icount int BEGIN TRY set @icount = (select count(*) as cnt from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+@dbpath+
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblSupplier) if @icount = 0 begin SELECT ''06'' AS status, ''DATA KOSONG'' AS description RETURN end if @icount > 0 begin create table ' + @tblSupplier + 
' ( PasokNama varchar(50) null, PasokAlmt varchar(70) null, PasokNeg varchar(2) null, ) insert into ' + @tblSupplier + 
' ( PasokNama ,PasokAlmt ,PasokNeg ) select PasokNama ,PasokAlmt ,PasokNeg from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+@dbpath+
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblSupplier MERGE tblSupplier AS TARGET USING ' + @tblSupplier + 
' AS SOURCE ON (TARGET.PasokNama = SOURCE.PasokNama) WHEN MATCHED THEN UPDATE SET TARGET.PasokAlmt = SOURCE.PasokAlmt, TARGET.PasokNeg = SOURCE.PasokNeg WHEN NOT MATCHED BY TARGET THEN INSERT (PasokNama, PasokAlmt, PasokNeg) VALUES (SOURCE.PasokNama, SOURCE.PasokAlmt, SOURCE.PasokNeg) ; SELECT ''00'' AS status, ''SUKSES DATA GET'' AS description end drop table '+ @tblSupplier +
' END TRY BEGIN CATCH SELECT ''06'' AS status ,ERROR_MESSAGE() AS description; END CATCH; ')
 
END
GO