SQL 表复制

1。表结构相同的表，且在同一数据库（如，table1,table2)

 

Sql ：insert into table1 select * from table2 (完全复制)

 

insert into table1 select distinct * from table2(不复制重复纪录）

 

insert into table1 select top 5 * from table2 (前五条纪录)

 

2。   不在同一数据库中（如，db1 table1,db2 table2)

 

sql:    insert into db1..table1 select * from db2..table2 (完全复制)

 

insert into db1..table1 select distinct * from db2table2(不复制重复纪录）

 

insert into tdb1..able1 select top 5 * from   db2table2 (前五条纪录)

 

3.      表结构不同的表或复制部分纪录（如，dn_user,dn_user2)

 

a.    建一个新表[DN_UserTemp]（在老表dn_user上增加一列)

 

CREATE TABLE [DN_UserTemp] ( [Num] [numeric](18, 0) IDENTITY (1, 1) NOT NULL）

 

[Id] [idtype] NOT NULL ,

 

[Name] [fntype] NOT NULL ,

 

[Descript] [dstype] NULL ,

 

[LogonNm] [idtype] NOT NULL ,

 

[Password] [idtype] NULL ,

 

[Gender] [char] (1) NULL ,

 

[Quited] [booltype] NOT NULL,

 

[OffDuty] [booltype] NOT NULL ,

 

[Stopped] [booltype] NOT NULL,

 

[OSBind] [booltype] NOT NULL,

 

[Domain] [idtype] NULL ,

 

[EMail] [fntype] NULL ,

 

[UnitId] [idtype] NULL ,

 

[BranchId] [idtype] NULL ,

 

[DutyId] [idtype] NULL ,

 

[LevelId] [idtype] NULL ,

 

[ClassId] [idtype] NULL ,

 

[TypeId] [idtype] NULL ,

 

[IP] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,

 

[ExpireDT] [datetime] NULL ,

 

[Sort] [int] NOT NULL ,

 

[AllowDel] [booltype] NOT NULL,

 

[UnitChief] [booltype] NOT NULL,

 

[BranchChief] [booltype] NOT NULL ,

 

[UnitDeputy] [booltype] NOT NULL ,

 

[BranchDeputy] [booltype] NOT NULL ,

 

[Num] [numeric](18, 0) IDENTITY (1, 1) NOT NULL

 

) ON [PRIMARY]

 

b. 将dn_uer2的数据拷入dn_usertemp

 

sql:insert into dn_usertemp select * from dn_user2

 

c.将dn_usertemp 拷入dn_user

 

sql:

 

declare @i int

 

declare @j int

 

declare @Name fntype

 

set @i=1

 

select @j=count(*) from dn_usertemp

 

while @i<@j 1

 

begin

 

select @Name=Name from dn_usertemp where Num=@i

 

print @Name

 

insert into dn_user (Name) values (@Name) where Num=@i

 

select @i=@i 1

 

end