jerry的成長之路

原始表格

SELECT * FROM DEPARTMENTS

1. 新增資料

   INSERT INTO DEPARTMENTS VALUES (72,'AAA',100,1700)

   常見錯誤

   --新增常見的錯誤1  重複主鍵  就是71已經建過了
   INSERT INTO departments VALUES ( 71, 'aaa', 100, 1700 );
   
   --新增常見的錯誤2 找不到外部鍵 就是1701對應到的LOCATION_ID表格根本沒有1701這個ID
   INSERT INTO departments VALUES ( 72, 'aaa', 100, 1701 );

    

2. 新增部份欄位 

   INSERT INTO DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME) VALUES (31,'PURCHASING');

   
   

3. 新增資料強迫補 NULL值

   INSERT  INTO  departments  VALUES  ( 101, 'Finance', NULL, NULL );

   

4. 一次新增多筆

   drop table t1
   create table t1(id int, n char(20))  
   go
                          
   insert into t1 values (1,'aa'), 
                         (2,'bb'),
                         (3,'cc')
   select * from t1
   
   

   

5. 流水號
   
   新增流水號,預設值

   use DB01
   go
   drop table shippers
   go
   
   create table Shippers(shipperid int identity,
                          company char(30) default 'abc',
                          phone   char(20) default '0920333555')
   go
   insert into shippers(company,phone  ) values('aaa', '0932556677')
   insert into shippers(company,phone  ) values(default, default)
   insert into shippers default values
   go
   select * from shippers
   go

   

   --3b流水號強迫輸入
   
   set IDENTITY_INSERT dbo.shippers ON
   go
   
   --須個別列出欄位名稱
   insert into shippers (shipperid,company,phone)  values (85,'ccc','0955555555')
   go
   
   --錯誤- 未個別列出欄位名稱
   /* 訊息 8101，層級 16，狀態 1，行 1
   位於資料表 'Shippers' 的識別欄位其外顯值只有當使用了資料行清單
   且 IDENTITY_INSERT 為 ON 時才能指定。
   */
   
   insert into  shippers values(81,'ccc','0955555555')
   go
   
   select * from shippers
   go
   
   --3c回復成流水號輸入
   
   set IDENTITY_INSERT dbo.shippers OFF
   go
   insert into shippers(company,phone  ) values('bbb', '0932556678')
   
   --刪除一筆資料,再新增
   delete from shippers where shipperid = 3
   
   --3d再新增時, 該筆被刪除的流水號, 會重覆使用嗎
   insert into shippers(company,phone  ) values(default, default)
   select * from shippers
   go

   
   
    

6. MERGE

   SELECT * 
   INTO  copy_emp
   FROM  employees
   WHERE 1=2;
   
   select * from copy_emp;
   
   MERGE INTO copy_emp c
        USING employees e
        ON (c.employee_id = e.employee_id)
      WHEN MATCHED THEN
        UPDATE SET
          c.first_name     = e.first_name,
          c.last_name      = e.last_name,
          c.email          = e.email,
          c.phone_number   = e.phone_number,
          c.hire_date      = e.hire_date,
          c.job_id         = e.job_id,
          c.salary         = e.salary,
          c.commission_pct = e.commission_pct,
          c.manager_id     = e.manager_id,
          c.department_id  = e.department_id
      WHEN NOT MATCHED THEN
        INSERT VALUES(e.employee_id, e.first_name, e.last_name,
             e.email, e.phone_number, e.hire_date, e.job_id,
             e.salary, e.commission_pct, e.manager_id, 
             e.department_id);
   
   select * from copy_emp;

   新增一筆時, 使用 MERGE會 新增一筆

   --新增一筆時, 使用 MERGE會 新增一筆
   insert into EMPLOYEES values (223,'張三','張', 'aaa','0122','1987-01-02','AD_VP',20000,null, 100,90)