用其它的 Table 來更新資料(Update From Table)

UPDATE table1
      SET table1.col = table2.col1
FROM table2 WHERE table2.oldCol = table1.col

MS SQL SSIS

參考

SQL SERVER and ACCESS, EXCEL data conversion

1. 參考
2. SQL SERVER ACCESS?EXCEL  
3.   
4. SQL SERVER 2000?DTS??Transact-SQL?Transact-SQL?OpenDataSource?OPENROWSET ??SQL??SQL SERVER?ACCESS?EXCEL??  
5.   
6. ?SQL SERVER ACCESS  
7.   
8. ?  
9.   
10. DTSAccessSQL Server?:   
11.   
12. 1SQL SERVERTools???Data Transformation   
13. 2Services???  czdImport Data???   
14. 3Choose a Data Source??Microsoft Access as the Source?.mdb(.mdb)?   
15. 4Choose a Destination???Microsoft OLE?DB Prov ider for SQL?Server???   
16. 5Specify Table Copy??Query???Copy tables???   
17. 6Select Source Tables???Select All?????  
18.   
19. Transact-SQL?  
20.   
21. 1.SQL SERVERaccess:  
22. -- ======================================================  
23. SELECT *   
24. FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  
25. 'Data Source="c:\DB.mdb";User ID=Admin;Password=')...  
26. -------------------------------------------------------------------------------------------------  
27. 2.accessSQL server   
28. -- ======================================================  
29. SQL SERVER :  
30. SELECT *  
31. INTO newtable  
32. FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',   
33.       'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...  
34. -------------------------------------------------------------------------------------------------  
35. 3.SQL SERVERAccess  
36. -- ======================================================  
37. SQL SERVER ?  
38. insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  
39.   'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...   
40. (1,2)  
41. select 1,2  from  sql  
42. ?  
43. insert into  OPENROWSET('Microsoft.Jet.OLEDB.4.0',   
44.    'C:\db.mdb';'admin';'', Test)   
45. select id,name from Test  
46. INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', )  
47. SELECT *  
48. FROM sqltablename  
49. -------------------------------------------------------------------------------------------------  
50.   
51. ?SQL SERVER EXCEL  
52. 1?SQL SERVERExcel:  
53. -- ======================================================  
54. SELECT *   
55. FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  
56. 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]  
57. ? Jet  OLE DB  Excel ?  
58. SELECT *   
59. FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',  
60.   'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions  
61. -------------------------------------------------------------------------------------------------  
62.   
63. 2?ExcelSQL server :  
64. -- ======================================================  
65. SELECT * into newtable  
66. FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  
67.   'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]  
68. :  
69. SELECT * into newtable  
70. FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  
71.   'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions  
72. -------------------------------------------------------------------------------------------------  
73.   
74. 3?SQL SERVERExcel  
75. -- ======================================================  
76. T-SQL?  
77. EXEC master..xp_cmdshell 'bcp .dbo.out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'  
78. ?S SQL?U?P  
79. ?  
80. :EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'  
81. EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'  
82. VB6ADOEXCEL?   
83. Dim cn  As New ADODB.Connection  
84. cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"  
85. cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM .dbo." queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"  
86. ------------------------------------------------------------------------------------------------  
87.   
88. 4?SQL SERVERExcel:  
89. -- ======================================================  
90. insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  
91. 'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)  
92.   
93. T-SQL?  
94. INSERT INTO    
95. OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',    
96. 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]    
97. (bestand, produkt) VALUES (20, 'Test')    
98. -------------------------------------------------------------------------------------------------  
99.   
100. ??SQL SERVER?ACCESSEXCEL??  
101. EXEC   master..xp_cmdshell   'bcp   "select OrderID,ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Northwind.dbo.orders"   queryout   "d:\Oreders.txt" -t"|"    -c   -q   -S"127.0.0.1"   -U"sa"   -P""'    
102. SELECT  *      
103.   FROM  OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data  Source="xxxx.xls";Extended  Properties="Excel  8.0";IMEX=1;Persist  Security  Info=False')...[a1$]   

Microsoft.Jet.OLEDB.4.0

參考

Importing data from MS Access into SQL Server with TSQL

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;

-----Microsoft.Jet.OLEDB.4.0 vs Microsoft.ACE.OLEDB.12.0

需將txt 檔案 轉入至ms sql，個人是習慣用sql語法來達成目的，而不用SSIS或DTS這類工具。

利用opendatasource 函數,Provider:Microsoft.Jet.OLEDB.4.0
會報錯:

訊息 7308，層級 16，狀態 1，行 1
OLE DB 提供者 'Microsoft.Jet.OLEDB.4.0' 不能用來散佈查詢，因為提供者是設定成以單一執行緒 Apartment 模式執行。

後來了解到，我的系統是window server2008R2 x64 + MS SQL 2008R2 x64 而Microsoft.Jet.OLEDB.4.0是沒有x64的版本，
可改用Microsoft.ACE.OLEDB.12.0，而系統預設上是沒安裝的，這時可去微軟網站上
下載安裝 X64版的 Microsoft Access Database Engine 2010 可轉散發套件

http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displayLang=zh-tw

安裝完即可使用 Microsoft.ACE.OLEDB.12.0 來存取txt了。

補充:
使用上沒問題一陣子後，有天突然報錯了，DEBUG的結果是，原TABLE轉出的TXT檔中的欄位字串資料有 , 存在，系統預設將 ,  當作欄位的delimited。

因此將TABLE 資料匯出成TXT時，改用別的符號當作欄位的dellimited，例如:  ~ ，當然也要跟ap人員協調 勿再輸入此符號，以免又誤判了。

然後txt檔匯入至另個instance的db時，在原本的 Extended Properties 加上FMT=Delimited(~)
EX:
select *
from OpenDataSource('Microsoft.ACE.OLEDB.12.0',
'Data Source = d:\;Extended Properties="Text;HDR=no;FMT=Delimited(~)"')...abc#txt

但似乎無法正確將 ~ 當作欄位delimited，
HDR=no 是ok的，但FMT=Delimited(~) 或 Format=Delimited(~) 都不work，
不知是否為屬性名稱的問題?

後來改在txt檔的同層目錄下 建立一個Schema.ini，讓text driver 依此設定來存取

ex:
[abc.txt]
Format=Delimited(~)
ColNameHeader=FALSE

Create linked server to MS Access database

https://msdn.microsoft.com/zh-tw/library/ms190479(v=sql.120).aspx
https://msdn.microsoft.com/zh-tw/library/ff772782(v=sql.120).aspx

EXEC sp_addlinkedserver
   @server = N'Access',
   @provider = N'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = N'OLE DB Provider for Jet',
   @datasrc = N'D:\F2\App_Data\ya.mdb';
GO

Create linked server to MS Access database

Now in the provider name select Microsoft.Jet.OLEDB.4.0 Provider (OLEDB is faster then ODBC AFAIK)
Specify a linked server name you wish
Datasource will the Path to mdb file Ex: "c:\test.mdb"
Product Name will be "Access"

Else you can try below T-SQL too

exec sp_addlinkedserver @server='Access',
@srvproduct='Access',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='c:\test.mdb'

exec sp_addlinkedsrvlogin @rmtsrvname='Access', 
@useself='false', 
@rmtuser='Admin', 
@rmtpassword=''

Regarding login use Username Admin and NULL as password when you trying in GUI.

Now to get the remote table run below T-SQl

sp_tableex N'Access'

For more explanation on T-SQL and Linked Servers, Refer to BOL...

sp_addlinkedserver

建立連結伺服器。連結伺服器可讓您對 OLE DB 資料來源存取分散式異質性查詢。

sp_addlinkedserver  　　@server = 'server' , 　　　　-- 要建立的連結伺服器名稱。 　　@srvproduct = 'product_name' , 　　　　-- 加入的 OLE DB 資料來源產品名稱。 　　　　-- nvarchar(128)，預設值是 NULL。　　@provider = 'provider_name' , 　　　　-- 唯一程式化識別碼 (PROGID)。 　　　　-- nvarchar(128)，預設值是 NULL。　　@datasrc = 'data_source' ,  　　　　-- 資料來源名稱。 　　　　-- nvarchar(4000)，DBPROP_INIT_DATASOURCE 屬性。　　@location = 'location' ,  　　　　-- 資料庫位置。 　　　　-- nvarchar(4000)，預設值是 NULL，DBPROP_INIT_LOCATION 屬性。　　@provstr = 'provider_string' ,  　　　　-- 連接字串，用來識別唯一資料來源。 　　　　-- nvarchar(4000)，預設值是 NULL，DBPROP_INIT_PROVIDERSTRING 屬性。 　　@catalog = 'catalog' 　　　　-- 所用的目錄。 　　　　-- 是 sysname，預設值是 NULL，DBPROP_INIT_CATALOG 屬性。 傳回碼值：0 (成功) 或 1 (失敗) 權限：需要伺服器的 ALTER ANY LINKED SERVER 權限。 * 資料來源產品名稱如果是 SQL Server，則不必指定 provider_name、data_source、location、provider_string 和 catalog。 * 連結伺服器資料紀錄於 master.dbo.sysservers 中。

sp_dropserver

從 SQL Server 本機執行個體上的已知遠端和連結伺服器清單中移除伺服器。 

sp_dropserver 　　@server = 'server' ,  　　　　-- 要移除的伺服器　　@droplogins = {'droplogins' | NULL}  　　　　-- 如果指定了 droplogins，也必須移除 server 的相關遠端和連結伺服器登入 　　　　-- char(10)，預設值是 NULL。 傳回碼值：0 (成功) 或 1 (失敗) 權限：需要伺服器的 ALTER ANY LINKED SERVER 權限。 * 若要在移除伺服器時移除伺服器的所有遠端和連結伺服器登入，請使用 droplogins 引數。

[範例] 連接到 SQL Server，建立一個名叫 SEATTLESales 的連結伺服器。

EXEC sp_addlinkedserver   　　'SEATTLESales',  　　N'SQL Server'

[範例] 連接到 Microsoft Access 2002-2003

EXEC sp_addlinkedserver  　　 @server = 'SEATTLE Mktg',  　　@provider = 'Microsoft.Jet.OLEDB.4.0',  　　@srvproduct = 'OLE DB Provider for Jet', 　　@datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'

[範例] 連接到 Microsoft Access 2007

EXEC sp_addlinkedserver 　　@server = 'SEATTLE Mktg',  　　@provider = Microsoft.ACE.OLEDB.12.0',  　　@srvproduct = 'OLE DB Provider for ACE', 　　@datasrc = 'C:\MSOffice\Access\Samples\Northwind.accdb'

[範例] 連接到 Microsoft Excel 1997 - 2003

EXEC sp_addlinkedserver  　　'ExcelShare', 　　'Jet 4.0', 　　'Microsoft.Jet.OLEDB.4.0', 　　'\\MyServer\MyShare\Spreadsheets\DistExcl.xls', 　　NULL, 　　'Excel 5.0'

[範例] 連接到 Microsoft Excel 2007

EXEC sp_addlinkedserver 　　@server = N'ExcelDataSource', 　　@srvproduct=N'ExcelData', 　　@provider=N'Microsoft.ACE.OLEDB.12.0', 　　@datasrc=N'C:\DataFolder\People.xlsx', 　　@provstr='EXCEL 12.0' ;

[範例] 連接到 Oracle，建立一個名為 LONDON Mktg 的連結伺服器

EXEC sp_addlinkedserver 　　@server = 'LONDON Mktg', 　　@srvproduct = 'Oracle', 　　@provider = 'MSDAORA', 　　@datasrc = 'MyServer'

[範例] 連接到 ODBC，建立一個名為 SEATTLE Payroll 的連結伺服器

EXEC sp_addlinkedserver  　　@server = 'SEATTLE Payroll',  　　@srvproduct = '', 　　@provider = 'MSDASQL',  　　@datasrc = 'LocalServer'

[範例] 連接到 DB2

EXEC sp_addlinkedserver　　@server='DB2', 　　@srvproduct='Microsoft OLE DB Provider for DB2', 　　@catalog='DB2', 　　@provider='DB2OLEDB', 　　@provstr='Initial Catalog=PUBS; 　　　　Data Source=DB2; 　　　　HostCCSID=1252; 　　　　Network Address=XYZ; 　　　　Network Port=50000; 　　　　Package Collection=admin; 　　　　Default Schema=admin;'

[範例] 連接到 文字檔

--Create a linked server. EXEC sp_addlinkedserver  　　txtsrv,  　　'Jet 4.0',  　　'Microsoft.Jet.OLEDB.4.0', 　　'c:\data\distqry', 　　NULL, 　　'Text' GO --Set up login mappings.EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL GO --List the tables in the linked server.EXEC sp_tables_ex txtsrv GO --Query one of the tables: file1#txt --using a four-part name.  SELECT *  FROM txtsrv...[file1#txt]

[範例] 移除遠端伺服器 ACCOUNTS 和所有相關聯的遠端登入。

EXEC sp_dropserver 'ACCOUNTS', 'droplogins';