Answer 6
Hi JBSync,
We could use the
OPENROWSET function or the Import and Export Wizard to import
data from the Excel file into SQL Server 2008 R2, please take a look on the following example:
Use the distributed query import the data from Excel to SQL Server:
--import a 2007-2010 Excel file
SELECT* INTO TESTFROMOPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=YES;Database=E:\Test\Test.xlsx',
'SELECT * FROM [Test$]')
GO
--import a 97-2003 Excel file
SELECT* INTO TESTFROMOPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;HDR=YES;IMEX=1;Database=E:\Test\Test.xls',
'SELECT * FROM [Test$]')
GO
However, before we execute the above statements, we need to enable the 'Ad Hoc Distributed Queries' options:
EXECsp_configure'show advanced options',1
RECONFIGURE
EXECsp_configure'Ad Hoc Distributed Queries',1
RECONFIGURE
For your second questions, to find the duplicate rows in the table, please refer the below statements:
WITH CTE
AS
(
SELECT sending_id, project_no, Bill_no, paid, ROW_NUMBER() OVER( PARTITION BY project_no, Bill_no, paid, [other rest columns] ORDER BY sending_id DESC)AS ROW_NO FROM
TEST
)
SELECT sending_id, project_no, Bill_no, paid FROM CTE WHERE ROW_NO>1
In addition, about how to use Import and Export wizard, please reference the following links:
Importing and Exporting Data by Using the SQL Server Import and Export Wizard
http://dotnetslackers.com/articles/sql/Importing-MS-Excel-data-to-SQL-Server-2008.aspx
(This is an third-party link just for your reference, hope this article helpful since it provide the steps with images )
If there is anything unclear, please feel free to ask.
Thanks,
Weilin Qiao