mssql 某table的column存在，傳回1；column不存在，傳回0

參考引用
--

--某table的column存在，傳回1；column不存在，傳回0
select count(name) from syscolumns
where id=(select id from sysobjects where name='tableName') 

and name='columnName'

mssql 當資料重覆時,只更新一筆資料

引用來源
--

 SET ROWCOUNT 1   /*要先下此條件*/
 GO   
 UPDATE table_name   
 set field_1 = GETDATE()    

 WHERE field_2 = 'Y'

另一個方法(20140227):

UPDATE note
SET picture=@picture
WHERE user_id=@userid AND no=(SELECT TOP 1 no FROM note WHERE user_id=@userid ORDER BY no DESC)

或

UPDATE note

SET picture=@picture

WHERE user_id=@userid AND no=(SELECT max(no) FROM note WHERE user_id=@userid)

mssql 找尋table條件規則內有重覆的資料

引用來源
--

SELECT col2,col3,count(*) qty    
FROM table2   
where col2 is null   

group by col2,col3 having count(*) > 1  

mssql 取代字串裡的部份文字

引用來源
--

update USER_DATA
set email = REPLACE(email,'123' ,'456') 

where email like '%123'

mssql 更改欄位名稱

引用來源
--
 alter table TABLEA rename COLUMN columnABC to columnBCD;

mssql Update from another table / 更新資料使用別的TABLE

引用來源
--

UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)

update (
select a.fileld, b.fileld
from TABLEA a, TABLE b
where 
a.key = b.key 

) set fileld = fileld  

ms-sql 判斷欄位是否有中文

引用來源
--

select COLUMN2,COLUMN3
from TABLENAME 

where len(COLUMN3) != datalength(convert(varchar(1000),COLUMN3))

OPENROWSET open Access Database

參考引用:
---
SELECT top 10 *
FROM
OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:Testdata est.mdb';
    'admin';'',Table1)

---
回答區:

This works (ODBC):


select *
  from openrowset('MSDASQL',
                             'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:MBK.mdb;SystemDB=C:SECURED.MDW;Uid=me;Pwd=pw;',
                             'select * from [Mbk]')

This works (Jet.OLEDB):

select *
  from opendatasource('Microsoft.Jet.OLEDB.4.0',
  'Data Source=C:MBK.mdb;Jet OLEDBystem Database=C:SECURED.MDW;User ID=me;Password=pw;')
  ...Mbk

This won't work (Jet.OLEDB):

select *
  from openrowset('Microsoft.Jet.OLEDB.4.0',
                  'MS Access;Database=C:MBK.mdb;System Database=C:SECURED.MDW;UID=me;PWD=pw;',
                  'select * from [Mbk]')

saying ... "Wrong argument".

This won't work (Jet.OLEDB):

select *
  from openrowset('Microsoft.Jet.OLEDB.4.0',
                  'MS Access;Database=C:MBK.mdb;SystemDB=C:SECURED.MDW;UID=me;PWD=pw;',
                  'select * from [Mbk]')