Import excel if value not same in the reference

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Import excel if value not same in the reference

Author

Topic

SQL_Support_2015
Starting Member

7 Posts

Posted - 2015-01-28 : 10:26:42

Hello,

I am trying to achieve the task related with updates into my destination table via store procedure using dynamic sql. Basically I am importing the excel file into sql server.

For further step, I want to update only the records that do not exist already in the table with reference to primary key. For this the user will provide as an input for procedure the column (or columns) that compose the primary key in excel.

If [Test Column1] value match with db [Test Column1] then skip rows and if not match then insert row entry.

Here is my complete code not working as per the expectation:

----------------------------

--------------------Declaring variables------------------------------
CREATE PROCEDURE [dbo].[ImportExcelFile_Update_Error_Handling]
(@TABLENAME varchar(1000)
,@SourceSheet varchar (100)
,@DestinationTable varchar (100)
,@TypeOfUpdate varchar (100)
,@PrimaryLeyColumn VARCHAR(255))

as

declare @retval int --Remaing Value
EXEC master..xp_fileexist @TABLENAME, @retval output -- check if file exists

if @retval = 0
begin
print 'file does not exists.'
return
end

if @SourceSheet is null or @SourceSheet = ''
set @SourceSheet = '[Sheet1$]' -- assume that the Sheet name on excel file is the default name
else
set @SourceSheet = '[' + ltrim(rtrim(@SourceSheet)) + '$]'

if @DestinationTable is null or @DestinationTable = ''

set @DestinationTable = substring(@SourceSheet, 2, len(@SourceSheet) - 3) + convert(varchar, getdate(), 126)

if @TypeOfUpdate='Update'

Begin

Begin try

--if @PrimaryLeyColumn =''
set @PrimaryLeyColumn = '[]'--here i think need to add the value or updates

exec('Insert into [' + @DestinationTable + '] select * from openrowset(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;IMEX=1;Database=' + @TABLENAME + ''', ' + ''' select * from '+@SourceSheet + ''')t WHERE NOT EXISTS (SELECT 1 FROM [' + @DestinationTable + '] WHERE [' + @PrimaryLeyColumn + '] = t.[' + @PrimaryLeyColumn + ']')

declare @Appendcount INT

/**/

declare @SQLStringCount nvarchar(500);
declare @ParamDef nvarchar(500);
declare @TabName nvarchar(500);

SET @SQLStringCount = N'SELECT @COUNT=COUNT(1) FROM '+ @DestinationTable;
SET @ParamDef = N'@TABLE varchar(30),@COUNT BIGINT OUTPUT';

EXECUTE sp_executesql @SQLStringCount, @ParamDef ,@Table=@TabName, @COUNT=@Appendcount OUTPUT; --Getting the rowcount as a output parameter from the sp_executesql.

/**/

if @Appendcount != 0

PRINT 'Database is "Updated" with total number of Records= ' + Cast(@Appendcount AS VARCHAR(200))

end try

Begin Catch

if @DestinationTable is null or @DestinationTable = ''

DECLARE @ErrorNumberUT INT = ERROR_NUMBER();
DECLARE @ErrorMessageofUpdateTable NVARCHAR(max) = ERROR_MESSAGE();

PRINT 'Error, Table not exists in the database'
PRINT 'Actual error number: ' + CAST(@ErrorNumberUT AS VARCHAR(10));
PRINT 'Actual Error Message: ' + CAST(@ErrorMessageofUpdateTable AS VARCHAR(max));

end Catch

end

-----------------------------------------
To execute this code I am using this script
EXEC [ImportExcelFile_Update_Error_Handling] 'D:\TestFiles\Test_Updates.xlsx','sheet1','Error_Handing','update','[SUPLR_DUNS_NBR]';

-----------------
[SUPLR_DUNS_NBR] is the column where I have "ID to match" with new and old content in my excel and if this content same in my sql table then skip else insert the row.

--------------
Error Number: 105
Facing ERROR: Unclosed quotation mark after the character string '[] = t.[[]'.

Any support in this would ge really helpful to achive my tasks goal.

thanks

SQL_Support_2015

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-29 : 09:12:41

Instead of doing these codes simply you could achieve this in SSIS. That would be easy for performing and maintanence

Regards
Viggneshwar A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources