[MSSQL]兩個insert的語法

1.將view或是tableB的資料匯到table A

insert into tableA (field1,field2,field3)
select F1,F2,F3 from 
tableB
where F4=0

這個方式之前就有了,在這只是記錄一下.

 

2.insert大量資料 (new feature of SQL 2008)

insert into tableA (field1,field2,field3) 
values
('xxxa','xxxxb','xxxxc'),
('xxxa','xxxxb','xxxxc'),
('xxxa','xxxxb','xxxxc'),
('xxxa','xxxxb','xxxxc')

這個是SQL 2008的新功能.

不過用這樣的語法,一次最多只能新增1000筆.

如果要一次新增更多,可以使用BULK INSERT.

 

參考資料:

SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor
http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/

BULK INSERT to table with specific columns
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx