SELECT INTO FROM 与 insert into from

1.SELECT INTO FROM语句

      语句形式为：SELECT vale1, value2 into Table2 from Table1

     要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中，请注意vale1,vale2一定要是Table1有的字段，否则就会出错
。示例如下： 

View Code

 1  --1.创建测试表
 2     create TABLE Table1
 3     (
 4         a varchar(10),
 5         b varchar(10),
 6         c varchar(10),
 7         CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
 8         (
 9             a ASC
10         )
11     ) ON [PRIMARY]
12     GO
13 
14     --2.创建测试数据
15     Insert into Table1 values('赵','asds','90')
16     Insert into Table1 values('钱','asds','100')
17     Insert into Table1 values('孙','asds','80')
18     Insert into Table1 values('李','asds',null)
19     GO
20 
21     --3.SELECT INTO FROM语句创建表Table2并复制数据
22     select a,c INTO Table2 from Table1
23     GO

如果像这样：

select count(*) into Table2 from table1

就会出错，因为Table1没有count(*)这个字段。

2、insert into from語句

语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1

View Code

   --1.创建测试表
create TABLE Table1
(
    a varchar(10),
    b varchar(10),
    c varchar(10),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
    (
        a ASC
    )
) ON [PRIMARY]

create TABLE Table2
(
    a varchar(10),
    c varchar(10),
    d int,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
    (
        a ASC
    )
) ON [PRIMARY]
GO

--2.创建测试数据
Insert into Table1 values('赵','asds','90')
Insert into Table1 values('钱','asds','100')
Insert into Table1 values('孙','asds','80')
Insert into Table1 values('李','asds',null)
GO
select * from Table2

--3.INSERT INTO SELECT语句复制表数据
Insert into Table2(a, c, d) select a,c,5 from Table1
GO

從Table1表中查詢出a,c數據插入到Table2表。

转载于:https://www.cnblogs.com/koeltp/archive/2012/01/17/2324973.html