将Excel的数据导入SQL server :
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]仅供参考,
或者用数据导入导出向导,
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]仅供参考,
或者用数据导入导出向导,
比如:
EXCEL表:(t_excel)
Item A Item B Item C Item D
SQL表:(t_sql)
r1 r2 r3 r4 r5 r6 r7
一、我只要把EXCEL中的数据插入SQL中的r1,r2,r4,r5
insert into t_sql (r1,r2,r4,r5)
select * from t_excel二、我只要把EXCEL中的Item B,Item C插入SQL中的r1,r7
insert into t_sql(r1,r7)
select Item_B,Item_C from t_excel
insert into SQL_table(a,b,c)
select a,b,c FROM Openrowset( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
(Vendor, Purchasing, Trans, Material_Doc, Item, Posting_da, Quantity,Price, Amount,Curre, Reference, Document, Reference_Doc, Material, Plant)
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Data Source="F:\Fa-Excel\W0443\GRW0443.XLS";User ID=Admin;Password=;Extended properties=Excel 8.0'
)...[GRW0443$] 这错在哪里啊??
INSERT INTO ERS
(Vendor, Purchasing, Trans, Material_Doc, Item, Posting_da, Quantity,Price, Amount,Curre, Reference, Document, Reference_Doc, Material, Plant)
select * from (SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]) aa
SELECT 语句的可读性可通过为表指定别名来提高,别名也称为相关名称或范围变量。指派表的别名时,可以使用也可以不使用 AS 关键字: table_name AS table aliastable_name table_alias 在下例中,为 publishers 指派了别名 p。
USE pubs
SELECT p.pub_id, p.pub_name
FROM publishers AS p如果为表指派了别名,那么在该 Transact-SQL 语句中对该表的所有显式引用都必须使用别名,而不能使用表名。例如,下列 SELECT 语句将产生语法错误,因为该语句在已指派别名的情况下又使用了表名:SELECT Customers.CustomerID, /* Illegal reference to Customers. */
Cst.FirstName, Cst.LastName
FROM Northwind.dbo.Customers AS Cst
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message:Cannot start your application.The workgroup information file is missing or opened exclusively by another user]T-SQl语句
INSERT INTO ERS
(Vendor, Purchasing, Trans, Material_Doc, Item, Posting_da, Quantity,Price, Amount,Curre, Reference, Document, Reference_Doc, Material, Plant)
select * from (SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]) aa