sql語法

一般新增、修改、刪除
insert (into) TableName
(column1, column2, …, columnN )
Values(v1, v2, …, vN)

update TableName
set column1=v1, column2=v2, …, colunmN=vN
from TableName1 a , TableName2 b,…
where condition

delete *
from TableName
where condition

//增加欄位及資料型態
alter table CarCodeInfo
add test1 int
go

//修改欄位資料型態
alter table CarCodeInfo
alter column test1 char(4)
go


//跨server 新增
insert OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=xxx;Password=xxx'
).cartest.dbo.QCItem(QCItemID,CarCategory,TestMethod,Contents,AdopStandard,ToSort,IsShow)
select newid(),CarCategory,TestMethod,Contents,AdopStandard,ToSort,IsShow
from dbo.QCItem
where CarCategory='2' and (TestMethod='9' or TestMethod='10')
//跨server update
update OPENDATASOURCE('SQLOLEDB','Data Source=17-0526277-01;User ID=sa;Password=sa').CarCert0109_online.dbo.carsaledetail
set csd_carmodelname = (select...)
where csd_splyno = '093'

ps：SQL Server2005需設定SQL Server Surface Area Configuration，點選"功能的介面區組態"，勾選"啟用OPENROWSET和OPENDATASOURCE支援"


//查詢完整的table資訊
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='xxxx'

//新增流水號
select Num = identity(int,1,1), * into #temp from TableName
drop table #temp

ps：int為小寫 1,1同識別序號用法

//sql中使用indexof的寫法
charindex(exp1, exp2 [, start location])
exp1 => 目標字
exp2 => 搜尋字串
start location => exp2開始的位置
ex：
select charindex('2','3521468524') ==> 3
select charindex('2','3521468524',4) ==> 9 (因為從第4個位置開始找，所以第一個找到的2在位置9)