Sql 指令

/*反白表 alt+F1 找表幾個欄位, 資料型態*/
sp_help UPLOAD_REC

/*插入資料後立刻 select 流水編號的欄位, 可知最新的ID*/
INSERT INTO UPLOAD_REC(  upload_program, upload_desc, upload_filename, total_recno, upload_recno, upload_user,  upload_result, create_dt, modify_dt ) VALUES (  {upload_program}, {upload_desc}, {upload_filename}, {total_recno}, {upload_recno}, {upload_user},  {upload_result}, getdate(),getdate())
SELECT @@IDENTITY

CREATE TABLE PPP
(
Id_P int IDENTITY(1,1),
LastName varchar(20),
FirstName varchar(20),
Address varchar(20),
City varchar(20)
)

SELECT * FROM _PPP, PPP
Insert into _PPP VALUES('L','L','L','L');

/*複製新表 從 舊表*/
SELECT * into _PPP from PPP

/*新增欄位 表內 型態 */
ALTER TABLE VIP_INSURANCE
ADD A varchar(20),B varchar(20),C varchar(20),D varchar(20),E varchar(20)

/*刪除欄位 表內 型態 */
ALTER TABLE VIP_INSURANCE
DROP COLUMN A,B ,C ,D ,E 

/*表內  最新資料 有多少筆*/
sELECT * 
FROM VIP_INSURANCE_HISTORY
where CREATE_DT in(select MAX(CREATE_DT) from VIP_INSURANCE_HISTORY)

select * FROM WMPCHECKLIST
where CREATED in(select MAX(CREATED) from WMPCHECKLIST)


/*印出變數---多少筆資料*/
DECLARE @icount int
 Select @icount = COUNT(*) from VIP_INSURANCE;
PRINT @icount

/*小寫v = as v , 資料表的變數*/
DELETE *FROM VIP_INSURANCE where
exists( select 1 from  VIP_INSURANCE v where  

)

/*case when, 中間假表 新欄位*/
drop table store_information
create table store_information(
 Store_Name varchar(20),
 Sales int,
 Txn_Date varchar(20)
 )
 insert into store_information (Store_Name,Sales,Txn_Date )
 values('Los Angeles',1500,'05-Jan-1999')
  insert into store_information (Store_Name,Sales,Txn_Date )
 values('San Diego',250,'06-Jan-1999')
  insert into store_information (Store_Name,Sales,Txn_Date )
 values('San Fancisco',300,'07-Jan-1999')
  insert into store_information (Store_Name,Sales,Txn_Date )
  values('Boston',700,'08-Jan-1999')
  SELECT * FROM store_information
  
  SELECT Store_Name, CASE Store_Name
  WHEN 'Los Angeles' THEN Sales * 2
  WHEN 'San Diego' THEN Sales * 1.5
  ELSE Sales
  END
as "New Sales",
Txn_Date
FROM Store_Information;
  SELECT * FROM store_information

/*格式化 日期欄位
參考 http://www.dotblogs.com.tw/spark/archive/2009/08/05/9868.aspx
*/
    SELECT Store_Name, Sales , CONVERT(varchar(100), GETDATE(), 111) as PerDate
FROM store_information

/*smalldatetime 好用*/
select cast(getdate() as date  ) as a1
select cast(getdate() as datetime  ) as a2
select cast(getdate() as datetime2  ) as a3
select cast(getdate() as datetimeoffset  ) as a4
select cast(getdate() as smalldatetime  ) as a5