insert指令

 

一、INSERT基本語法

INSERT [INTO] table_or_view_name [(column_list)]

VALUES ({ DEFAULT | NULL | expression } [,...n]) [,...n]

 

慣例	說明
大寫	Transact-SQL關鍵字
小寫	由使用者提供的Transact-SQL語法參數
| (分隔號)	被分隔號所分隔的所有項目中，可以擇其中一個項目
[] (中括號)	選擇性的語法項目，但不要輸入中括號
{} (大括號)	必要的語法項目，請不要輸入大括號
[,...n]	指出前一個項目可以重複n次，以逗號分隔項目

 

二、範例

先行準備一張資料表

CREATE TABLE [dbo].[CUSTOMER2] 
  ( 
     [customer_id] [TINYINT] NOT NULL, 
     [address]     [NVARCHAR](50) NULL, 
     [cell]        [NCHAR](10) NULL, 
     PRIMARY KEY (customer_id) 
  ) 

go

 

1、不指定column_list的INSERT

INSERT CUSTOMER2 
VALUES (1,'my address','0800123123')

或

INSERT INTO CUSTOMER2 
VALUES      (1,'my address','0800123123')

皆可

 

2、指定column_list的INSERT

INSERT CUSTOMER2 
       (customer_id,address,cell) 
VALUES (1,'my address','0800123123')

 

3、給定預設值DEFAULT的INSERT

INSERT CUSTOMER2 
       (customer_id,address,cell) 
VALUES (1,'my address',DEFAULT)

 

4、同時新增多筆資料的INSERT

INSERT CUSTOMER2 
       (customer_id,address,cell) 
VALUES (1,'my address','0800000123'), 
       (2,'my address','0800000124'), 
       (3,'my address','0800000125')

 

5、結合函數的INSERT

INSERT CUSTOMER2 
       (customer_id,address,cell) 
VALUES (1,Upper('my address'),'0800000123')

 

6、考量INSERT資料表之間的順序問題

先準備父資料表CUSTOMER2與子資料表ORDER2

CREATE TABLE [dbo].[CUSTOMER2] 
  ( 
     [customer_id] [TINYINT] NOT NULL, 
     [address]     [NVARCHAR](50) NULL, 
     [cell]        [NCHAR](10) NULL, 
     PRIMARY KEY (customer_id) 
  ) 

go

CREATE TABLE [dbo].[ORDER2] 
  ( 
     [order_id]    [SMALLINT] NOT NULL, 
     [customer_id] [TINYINT] REFERENCES CUSTOMER2(customer_id)
     ON UPDATE CASCADE 
     ON DELETE CASCADE, 
     [outitem]     [DATETIME2](7) NOT NULL, 
     PRIMARY KEY ([order_id]) 
  ) 

go

假設父資料表裡沒有資料，但子資料表的FK卻硬要新增一筆資料則會出錯，

FK可以為null但不可以捏造父資料表所沒有的PK

INSERT ORDER2 
VALUES (1,1,'0001-1-1')

錯誤訊息如下

訊息 547，層級 16，狀態 0，行 4
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__ORDER2__customer__673F4B05". The conflict occurred in database "testDB", table "dbo.CUSTOMER2", column 'customer_id'.
The statement has been terminated.

 

7、透過SQL statement的INSERT

先準備一個內含值的資料表

CREATE TABLE [dbo].[BOOK_DATA] 
  ( 
     [book_no]            [INT] NOT NULL, 
     [publish_company_no] [INT] NOT NULL, 
     [book_name]          [VARCHAR](30) NULL, 
     [publish_date]       [DATE] NOT NULL, 
     [amount]             [INT] NOT NULL, 
     PRIMARY KEY ([book_no]) 
  ) 

go

INSERT [BOOK_DATA] 
VALUES (1,1,'SQL SERVER','0001-1-1',10) 

go

 

(1)、SELECT...INTO...

此語法可以從已存在的資料表篩選出想要的資料，

並根據select column_list預先自動建立COPY_BOOK_DATA資料表(該資料表先前不存在)，

然後將剛剛篩選出的資料放進COPY_BOOK_DATA資料表裡。

SELECT book_no,publish_company_no,book_name,publish_date,amount 
INTO   COPY_BOOK_DATA 
FROM   BOOK_DATA

 

(2)、INSERT...SELECT...

此語法可以從已存在的資料表篩選出想要的資料，並根據select column_list將已篩選出的資料，

放進先前已存在的資料表COPY_BOOK_DATA2

INSERT COPY_BOOK_DATA2 
SELECT book_no,publish_company_no,book_name,publish_date,amount 
FROM   BOOK_DATA

等同於

INSERT COPY_BOOK_DATA2 (book_no,publish_company_no,book_name,publish_date,amount)
SELECT book_no,publish_company_no,book_name,publish_date,amount 
FROM   BOOK_DATA

Tips：

可以先利用SELECT...INTO...配合WHERE 1 = 0 的矛盾條件創立COPY_BOOK_DATA2資料表，

然後再使用INSERT...SELECT... insert資料

SELECT book_no,publish_company_no,book_name,publish_date,amount
INTO COPY_BOOK_DATA2
FROM BOOK_DATA
WHERE 1 = 0

go

INSERT COPY_BOOK_DATA2 
SELECT book_no,publish_company_no,book_name,publish_date,amount 
FROM   BOOK_DATA

 

8、透過execute_statement的INSERT

(1)、透過執行「預存程序」的INSERT

INSERT COPY_BOOK_DATA2 
       (book_no,publish_company_no,book_name,publish_date,amount) 
EXEC Book_data_procedure

 

(2)、透過執行「SQL字串」的INSERT

INSERT COPY_BOOK_DATA2 (book_no,publish_company_no,book_name,publish_date,amount)
exec ('SELECT book_no,publish_company_no,book_name,publish_date,amount FROM BOOK_DATA')