运行select * into hh from h前: CREATE TABLE [h] ( [a] [int] IDENTITY (1, 1) NOT NULL , [b] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO 运行select * into hh from h后: CREATE TABLE [hh] ( [a] [int] IDENTITY (1, 1) NOT NULL , [b] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO可见:1、“表1中有自动加1的主键值,新表中则没有 2、生成“新表”中部分字段类型会变成varchar型,并且长度会变; 3、“表1”中允许为空的字段在“新表”中变的不允许为空。“得出的结论是不正确。 表的结构完全相同。但如果原表有约束,则后来的表将丢失。
before: CREATE TABLE [a] ( [a] [int] NULL , [b] [int] NULL , [c] [int] NULL , CHECK (((not([a] is null and [b] is null and [c] is null)))) ) ON [PRIMARY] GOselect * into aa from a after: CREATE TABLE [aa] ( [a] [int] NULL , [b] [int] NULL , [c] [int] NULL ) ON [PRIMARY] GO约束丢失。
--原来数据库中JOBS表的生成脚本 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__employee__job_id__1BFD2C07]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[employee] DROP CONSTRAINT FK__employee__job_id__1BFD2C07 GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[jobs] GOCREATE TABLE [dbo].[jobs] ( [job_id] [smallint] IDENTITY (1, 1) NOT NULL , [job_desc] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [min_lvl] [tinyint] NOT NULL , [max_lvl] [tinyint] NOT NULL ) ON [PRIMARY] GO --执行 SELEC INTO后的生成JOBS1的脚本if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobs1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[jobs1] GOCREATE TABLE [dbo].[jobs1] ( [job_id] [smallint] IDENTITY (1, 1) NOT NULL , [job_desc] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [min_lvl] [tinyint] NOT NULL , [max_lvl] [tinyint] NOT NULL ) ON [PRIMARY] GO 可以看出JOBS 和JOBS1在表结构上完全一致,除了外健约束, 如果你想让它们的约束都完全相同,就不能用SELECT INTO语句了
我已经说了啊,这样生成的新表,表结构和原表不一样,会发生变化的!!
运行select * into hh from h前:
CREATE TABLE [h] (
[a] [int] IDENTITY (1, 1) NOT NULL ,
[b] [char] (4) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
运行select * into hh from h后:
CREATE TABLE [hh] (
[a] [int] IDENTITY (1, 1) NOT NULL ,
[b] [char] (4) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO可见:1、“表1中有自动加1的主键值,新表中则没有
2、生成“新表”中部分字段类型会变成varchar型,并且长度会变;
3、“表1”中允许为空的字段在“新表”中变的不允许为空。“得出的结论是不正确。
表的结构完全相同。但如果原表有约束,则后来的表将丢失。
CREATE TABLE [a] (
[a] [int] NULL ,
[b] [int] NULL ,
[c] [int] NULL ,
CHECK (((not([a] is null and [b] is null and [c] is null))))
) ON [PRIMARY]
GOselect * into aa from a after:
CREATE TABLE [aa] (
[a] [int] NULL ,
[b] [int] NULL ,
[c] [int] NULL
) ON [PRIMARY]
GO约束丢失。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__employee__job_id__1BFD2C07]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[employee] DROP CONSTRAINT FK__employee__job_id__1BFD2C07
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[jobs]
GOCREATE TABLE [dbo].[jobs] (
[job_id] [smallint] IDENTITY (1, 1) NOT NULL ,
[job_desc] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[min_lvl] [tinyint] NOT NULL ,
[max_lvl] [tinyint] NOT NULL
) ON [PRIMARY]
GO
--执行 SELEC INTO后的生成JOBS1的脚本if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobs1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[jobs1]
GOCREATE TABLE [dbo].[jobs1] (
[job_id] [smallint] IDENTITY (1, 1) NOT NULL ,
[job_desc] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[min_lvl] [tinyint] NOT NULL ,
[max_lvl] [tinyint] NOT NULL
) ON [PRIMARY]
GO
可以看出JOBS 和JOBS1在表结构上完全一致,除了外健约束,
如果你想让它们的约束都完全相同,就不能用SELECT INTO语句了