INSERT INTO ... SELECT语句

 

　　INERT INTO...SELECT语法：

INSERT INTO <table_name>
[<column list>]
<SELECT statement>

示例：将另外一个表作为数据源一次插入多条记录

 1 INSERT INTO dbo.T_TQM_DianjianRecord
 2         ( workDate ,
 3           lineCode ,
 4           lineName ,
 5           gongxuCode ,
 6           gongxuName ,
 7           zhandianCode ,
 8           zhandianName ,
 9           gangweiCode ,
10           gangweiName ,
11           banciCode ,
12           banciName ,
13           banzuCode ,
14           banzuName ,
15           dianjianOrderNo ,
16           dianjianStr ,
17           dianJianDate ,
18           dianjianUser ,
19           dianjianUserName ,
20           dianjianResult ,
21           lastResult ,
22           notes ,
23           createUser ,
24           createUserName 
25         )
26 SELECT  
27           '2017-07-25',
28           lineCode ,
29           lineName ,
30           gongxuCode ,
31           gongxuName ,
32           zhandianCode ,
33           zhandianName ,
34           gangweiCode ,
35           gangweiName ,
36           banciCode ,
37           banciName ,
38           banzuCode ,
39           banzuName ,
40           dianjianOrderNo ,
41           dianjianStr ,
42           DATEADD(dd,1,dianJianDate) ,
43           dianjianUser ,
44           dianjianUserName ,
45           dianjianResult ,
46           lastResult ,
47           notes ,
48           createUser ,
49           createUserName 
50         
51 FROM  dbo.T_TQM_DianjianRecord

插入前：

插入后：

 

示例：声明table类型的变量，向变量中一次插入多条记录

USE Portal
GO

DECLARE @tbl TABLE
(
    ProductName VARCHAR(50) NULL,
    CreateDate DATETIME NULL
)
INSERT INTO @tbl
SELECT
    [ProductName], [CreateDate]
FROM 
    [dbo].[Product]

SELECT * FROM @tbl

GO

 

posted @ 2017-07-24 09:19  奔驰毛毛虫  阅读(1171)  评论(0)  编辑  收藏  举报