OpenRowset with dynamic sql variable insert query

Question

I want to execute this query. Because only OPENROWSET don't work with variable :

EXEC
 (
 '
 Insert into Table1 
 SELECT *
From OPENROWSET(MICROSOFT.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Users\AA\Desktop\Table1.xlsx',
'SELECT *
 FROM [Sheet1$] ) '
)

For insert in a table on SQL Server 2008 with a variable SQL. My objectif is to make the filepath dynamic like database='+@FilePath+'

Finally using this code in ado.Net in openFiledialog control

But it doesn't work I get a syntax error

I have solved but without insert query I think the same thing with insert always escape the quotes like that

EXEC
 (
'SELECT *
From OPENROWSET(''MICROSOFT.ACE.OLEDB.12.0'',
''Excel 12.0;Database=C:\Users\AA\Desktop\Table1.xlsx'',
''SELECT *
 FROM [Sheet1$]'') T
 ')

and with FilePath :

Declare 
@FilePath nvarchar(50)
SET @FilePath='C:\Users\AA\Desktop\Table1.xlsx'
 EXEC
 (
'SELECT *
From OPENROWSET(''MICROSOFT.ACE.OLEDB.12.0'',
''Excel 12.0;Database='+@FilePath+''',
''SELECT *
 FROM [Sheet1$]'') T
 ')

Well what did you actually try? If you want quotes inside quotes you have to escape them (double them up). e.g 'select ''somestring'' From Something' — Tony Hopkinson
– Tony Hopkinson, Commented Mar 16, 2014 at 9:51
Firstval i want to know if i can write like in my first message, and if is true I want to correct my query in my example — Ahmed Khalil
– Ahmed Khalil, Commented Mar 16, 2014 at 9:55
Well add your query (the one that gave the syntax error) to the question and we'll help. Syntax error could be almost anything. — Tony Hopkinson
– Tony Hopkinson, Commented Mar 16, 2014 at 10:02
So wheres this @filePath??? I can't help with syntax error using a different query can I? — Tony Hopkinson
– Tony Hopkinson, Commented Mar 16, 2014 at 10:21

M.Ali · Accepted Answer · 2014-03-16 14:44:24Z

4

Your need to use dynamic sql for this And also it best to explicitly use Column Names in your INSERT INTO and SELECT statements. you can do something as follows.

DECLARE @SheetName NVARCHAR(MAX);
DECLARE @FilePath  NVARCHAR(MAX);
DECLARE @Sql       NVARCHAR(MAX);

SET @Sql =  N' INSERT INTO Table1 '                            +
            N' SELECT *  '                                     +
            N' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ' +
            N' ''Excel 8.0;Database='+ @FilePath + ';'' ,'     +
            N' ''SELECT* FROM ['+ @SheetName +']'')' 

EXECUTE sp_executesql @Sql

answered Mar 16, 2014 at 14:44

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

OpenRowset with dynamic sql variable insert query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related