[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