存在tableA表
Value Name
10024 AAA
12354 BBB
14756 CCC
14586 CCC存在tableB表ID Name Type
70001 DDD TypeA
70002 AAA TypeA
70003 BBB TypeA
80000 nnn TypeB要将表tableA中的记录插入tableB中:
条件1: tableA中 Name值重复的记录只插入一次。
条件2: tableB中 Name值已经存在的,则不需要插入。
条件3: tableB新增的记录必须是以Type=TypeA最大的ID为起点,自增1.
注意:tableB中的ID并没有设置为自增字段,需要用语句实现。
期望得到效果
tableB表:
ID Name Type
70001 DDD TypeA
70002 AAA TypeA
70003 BBB TypeA
70004 CCC TypeA
80000 nnn TypeB多谢!
Value Name
10024 AAA
12354 BBB
14756 CCC
14586 CCC存在tableB表ID Name Type
70001 DDD TypeA
70002 AAA TypeA
70003 BBB TypeA
80000 nnn TypeB要将表tableA中的记录插入tableB中:
条件1: tableA中 Name值重复的记录只插入一次。
条件2: tableB中 Name值已经存在的,则不需要插入。
条件3: tableB新增的记录必须是以Type=TypeA最大的ID为起点,自增1.
注意:tableB中的ID并没有设置为自增字段,需要用语句实现。
期望得到效果
tableB表:
ID Name Type
70001 DDD TypeA
70002 AAA TypeA
70003 BBB TypeA
70004 CCC TypeA
80000 nnn TypeB多谢!
select distinct b.ID+1 as ID, tableA.name,'TypeA' as type
from tableA ,(select max(id) as Id from tableb where type='TypeA')b
where tableA.name not in ( select name from tableB)
(1 row(s) affected)select * from tableB70001 DDD TypeA
70002 AAA TypeA
70003 BBB TypeA
80000 nnn TypeB
70004 CCC TypeA
create table tablea(value int,name varchar(10))
create table tableb(id int,name varchar(10),type varchar(10))
insert into tablea
select 10024,'AAA' union all
select 12354,'BBB' union all
select 14756,'CCC' union all
select 14586,'CCC' union all
select 14587,'EEEE' insert into tableB
SELECT 70001,'DDD','TypeA' union all
SELECT 70002,'AAA','TypeA' union all
SELECT 70003,'BBB','TypeA' union all
SELECT 80000,'nnn','TypeB'
insert into tableb(name,type)
select a.name,'TypeA'
from tablea a where not exists(select 1 from tablea where name=a.name and value>a.value)
AND NOT EXISTS(SELECT 1 FROM TABLEB WHERE NAME=A.NAME)
DECLARE @ID INT
SET @ID=(select max(ID) from tableb where type='TypeA')
UPDATE TABLEB SET ID=@ID,@ID=@ID+1 WHERE ID IS NULL
SELECT * FROM TABLEB ORDER BY ID
DROP TABLE TABLEA,TABLEB
insert @a select 10024 ,'AAA'
union all select 12354 ,'BBB'
union all select 14756 ,'CCC'
union all select 14759 ,'ggg'
union all select 14586 ,'CCC'
declare @b table(ID decimal(10,0), Name varchar(10), Type varchar(20))
insert @b select 70001, 'DDD', 'TypeA'
union all select 70002 ,'AAA', 'TypeA'
union all select 70003 ,'BBB' ,'TypeA'
union all select 80000 ,'nnn' ,'TypeB'select aa.name,min(a) a into #tmp from @a
aa where name not in(select name from @b) group by aa.name
insert @b
select (select max(id) from @b where type='TypeA')+(select count(1) from #tmp where a<=a.a),a,'TypeA' from #tmp aselect * from @bdrop table #tmp
create table tableb(id int,name varchar(10),type varchar(10))
insert into tablea
select 10024,'AAA' union all
select 12354,'BBB' union all
select 14756,'CCC' union all
select 14586,'CCC'
insert into tableB
SELECT 70001,'DDD','TypeA' union all
SELECT 70002,'AAA','TypeA' union all
SELECT 70003,'BBB','TypeA' union all
SELECT 80000,'nnn','TypeB'SELECT IDENTITY(int,1,1) AS [id],A.[name] INTO #T
FROM tablea AS A
WHERE NOT EXISTS(SELECT 1 FROM tableb AS B WHERE B.[name]=A.[name])
GROUP BY A.[name]
DECLARE @MaxID int
SELECT @MaxID=ISNULL(MAX([id]),0) FROM tableb WHERE type=N'TypeA'
INSERT INTO tableb SELECT [id]+@MaxID,[name],N'TypeA' FROM #T
/*
id name type
---------------------
70001 DDD TypeA
70002 AAA TypeA
70003 BBB TypeA
70004 CCC TypeA
80000 nnn TypeB*/SELECT * FROM tableb ORDER BY [id]
DROP TABLE #T
DROP TABLE tablea,tableb
--借下数据:
create table tablea(value int,name varchar(10))
create table tableb(id int,name varchar(10),type varchar(10))
insert into tablea
select 10024,'AAA' union all
select 12354,'BBB' union all
select 14756,'CCC' union all
select 14586,'CCC'
insert into tableb
SELECT 70001,'DDD','TypeA' union all
SELECT 70002,'AAA','TypeA' union all
SELECT 70003,'BBB','TypeA' union all
SELECT 80000,'nnn','TypeB'
if exists(select 1 from tablea a where not exists(select 1 from tableb where a.name=name) )
begin
insert into tableb(name,type) select distinct name,'typea' from tablea a where not exists(select 1 from tableb where a.name=name)
declare @i int
set @i=(select max(id) from tableb where type='typea')
update tableb set @i=@i+1,id=@i
where id is null
end
select * from tableb
order by idid name type
----------- ---------- ----------
70001 DDD TypeA
70002 AAA TypeA
70003 BBB TypeA
70004 CCC typea
80000 nnn TypeB(所影响的行数为 5 行)