1、SELECT * INTO FROM:要求新表不存在,将数据和表结构复制到新表,主外键等约束不会复制
SELECT * INTO 新表 FROM 旧表 WHERE 1=2 -- 仅仅复制表结构SELECT * INTO 新表 FROM 旧表
2、INSERT INTO:要求新表存在,将数据复制到新表
INSERT INTO 新表 SELECT * FROM 旧表INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表
例子:
--insert into select 和 select into
create table p_table (
id int primary key not null,
name nvarchar(50),
age int
)
insert into p_table values(1,'xiao八',18)
insert into p_table values(2,'qiqi',17)
insert into p_table values(3,'老十',20)
select * from p_table
create table c_table (
id int primary key not null,
name nvarchar(50),
age int
)
select * from c_table
insert into c_table select * from p_table
select * from c_table
-- select into
drop table c_table
select * into c_table from p_table
select * from c_table
drop table p_table,c_table
查看表结构:
-- 查看表结构
select * from information_schema.columns where table_name = 'c_table'
查看存储过程:
exec sp_help 'c_table'