Source & Destination
Info:
1)
Source Excel Sheet
Name - “Sheet1”
in “excel2sqltable.xls”
Contains 2 columns namely Column1 and
Column2
It contains data as 1, Prakash
2, Bharath
3, Sastry
2)
Destination Sql Server Table
Name - “excel2sqltable”
Contains 2 columns namely Column1 and
Column2
Query to select the records
from the excel sheet:
SELECT Column1,Column2
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel8.0;Database=L:\practice\excel2sqltable.xls', [Sheet1$])
Query to select and insert
the records from excel to table:
INSERT INTO dbo.excel2sqltable
(
Column1,
Column2
)
SELECT Column1,Column2
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel
8.0;Database=L:\practice\excel2sqltable.xls', [Sheet1$])
** Note:
On
executing the above query you may get Error :
“Msg
15281, Level 16, State 1, Line 1
SQL
Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad
Hoc Distributed Queries' because this component is turned off as part of the
security configuration for this server. A system administrator can enable the
use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information
about enabling 'Ad Hoc Distributed Queries', see "Surface Area
Configuration" in SQL Server Books Online.”
To enable
the property run the below query :
EXEC sp_configure 'show advanced options',
1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries',
1;
RECONFIGURE;
It will
enable the property which allows to modify
advanced options.
Then it
will enable the execution of the ‘d Hoc Distributed Queries’.
Transfer data sql server to excel
sheet
Query to transfer the
records from table to excel:
INSERT INTO
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel
8.0;Database=L:\practice\excel2sqltable.xls;',
'SELECT
* FROM [Sheet1$]')
SELECT * FROM dbo.excel2sqltable