sql使用另一张表的结构或数据创建新表,查询主关键字重复数据,删除主关键字重复数据

select * into dano from ocy002 where 1=2; 

其中，dano为新表，ocy002为原有的另一张表， 指的条件是原来的库结构。1=2表示没有其它条件时不查出数据，用作where拼接sql句子 ,如果条件是“1=1”就是选择所有记录。

将一个表数据插入到另一个表中

insert into dano3 select * from dano 

想要将值插入到自动编号（或者说是标识列，IDENTITY）中去，需要设定　SET IDENTITY_INSERT

set IDENTITY_INSERT h7113 off
INSERT INTO h7113 (jh,km,dm,jlzh,rq,cyfs,scsj,bj,bs,cc,cc1,yy,ty,hy,rcyl1,rcyl,hs,rcql,qyb,lysj,lyds,JKWD,sxdl,xxdl,lg,ym,ymcsrq,bz,hdl,yz) Select jh,km,dm,jlzh,rq,cyfs,scsj,bj,bs,cc,cc1,yy,ty,hy,rcyl1,rcyl,hs,rcql,qyb,lysj,lyds,JKWD,sxdl,xxdl,lg,ym,ymcsrq,bz,hdl,yz From oc52 where jh='H7-113'
set IDENTITY_INSERT h7113 OFF
注意：若不关闭，则再次添加数据时，无法自增长，提示错误信息：

/*select * into dano from ocy0502 where 1=2; 
插入数据
insert into dano3 select * from dano 
查询主关键字重复数据
select jh,rq From dano Group BY  jh,rq HAVING COUNT(1)>1

用一个表结构建立另一个表的结构
select * into dano5 from dano where 1=2; 

insert into dano5 select * from dano 


  select * from dano where jh,rq in (select jh,rq from dano group by jh,rq having count(1) = 1)


查询主关键字不重复数据
select jh,rq From dano Group BY  jh,rq HAVING COUNT(1)=1

创建表结构
create table ddd (id int identity(1,1),v1 int)

删除重复数据

DELETE 
FROM
dano6 
WHERE
id NOT IN (
SELECT
id 
FROM
( SELECT min( main.id ) id FROM dano6 main GROUP BY main.jh,main.rq) children 
)

*/