table 1pid1
2
3
4
5table 2 前id rid pid
1 a 1
2 a 2
5 b 1
6 b 2
7 b 3
8 b 4
-----------------------------------------
table 2 后id rid pid
1 a 1
2 a 2
3 a 3
4 a 4
5 a 5
6 b 1
7 b 2
8 b 3
9 b 4
10 b 5表结构如上图,table2 的 id 是自动增长,rid 另一个表的外键,pid 是table1的外键.想要多条插入table2 要求rid对应全部的pid 如现在:b 只对应了 pid{1,2,3,4} c pid{1,2}
更新后应该所有的rid都对应table1的每一条数据,及{1,2,3,4,5}我知道现在数据库可能设计的有点问题,但是我不可以修改。求高手帮忙 这个 insert 怎么写
2
3
4
5table 2 前id rid pid
1 a 1
2 a 2
5 b 1
6 b 2
7 b 3
8 b 4
-----------------------------------------
table 2 后id rid pid
1 a 1
2 a 2
3 a 3
4 a 4
5 a 5
6 b 1
7 b 2
8 b 3
9 b 4
10 b 5表结构如上图,table2 的 id 是自动增长,rid 另一个表的外键,pid 是table1的外键.想要多条插入table2 要求rid对应全部的pid 如现在:b 只对应了 pid{1,2,3,4} c pid{1,2}
更新后应该所有的rid都对应table1的每一条数据,及{1,2,3,4,5}我知道现在数据库可能设计的有点问题,但是我不可以修改。求高手帮忙 这个 insert 怎么写
select t.rid,r.pid
from #temp t,table1 r
order by t.rid,r.piddrop table #temp
-- Author :SQL77(只为思齐老)
-- Date :2010-01-26 17:07:08
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#T1
if object_id('tempdb.dbo.#T1') is not null drop table #T1
go
create table #T1([pid] int)
insert #T1
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5
--> 测试数据:#t2
if object_id('tempdb.dbo.#t2') is not null drop table #t2
go
create table #t2([id] int,[rid] varchar(1),[pid] int)
insert #t2
select 1,'a',1 union all
select 2,'a',2 union all
select 5,'b',1 union all
select 6,'b',2 union all
select 7,'b',3 union all
select 8,'b',4
--------------开始查询----------------------------select * from #T1
--select * from #t2--INSERT #T2
SELECT * FROM ( SELECT * FROM (SELECT DISTINCT RID FROM #T2)AS T,#T1)AS TWHERE NOT EXISTS(SELECT 1 FROM #T2 T2
WHERE T.RID=T2.RID AND T.PID=T2.PID)----------------结果----------------------------
/* (所影响的行数为 5 行)
(所影响的行数为 6 行)RID pid
---- -----------
a 3
a 4
a 5
b 5(所影响的行数为 4 行)
*/
--不能该数据?
if object_id('table1') is not null drop table table1
go
create table table1([pid] int)
insert table1
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5
if object_id('table2') is not null drop table table2
go
create table table2([id] int identity(1,1),[rid] varchar(1),[pid] int)
insert table2
select 'a',1 union all
select 'a',2 union all
select 'b',1 union all
select 'b',2 union all
select 'b',3 union all
select 'b',4select distinct rid into #temp from table2truncate table table2insert into table2(rid,pid)
select t.rid,r.pid
from #temp t,table1 r
order by t.rid,r.pidselect * from table2drop table #temp
--------------------------
1 a 1
2 a 2
3 a 3
4 a 4
5 a 5
6 b 1
7 b 2
8 b 3
9 b 4
10 b 5
--sql 2008
if exists(select 1 from sysobjects where OBJECT_ID('table1') is not null and [type]='U' )
drop table table1
Create table table1 (pid int)
insert into table1 select 1 union all select 2 union all
select 3 union all select 4 union all select 5if exists(select 1 from sysobjects where OBJECT_ID('table2') is not null and [type]='U' )
drop table table2
Create TABLE [dbo].[Table2](
[id] [int] IDENTITY(1,1) NOT NULL,
[rid] varchar(3) NOT NULL,
[pid] [int] NOT NULL
) ON [PRIMARY]
insert into table2 select 'a', 1 union all select 'a', 2 union all select 'b', 1 union all
select 'b', 2 union all select 'b', 3 union all select 'b', 4
declare @pids varchar(8000),@rids varchar(8000)
set @pids=''
set @rids=''
select @pids = @pids +','+ Convert(varchar,pid) from table1
print @pids
select @rids = @rids +',' + Convert(varchar,a.rid) from (select distinct rid from Table2) a
print @rids
delete Table2
declare @PreU int
declare @CurU int
set @rids = ltrim(rtrim(@rids))
set @PreU = 0
set @CurU = charindex(',', @rids+',')
declare @PreU_pid int
declare @CurU_pid int
declare @rid varchar(50),@pid varchar(50)
while (@CurU>0)
begin
set @rid = substring(@rids, @PreU+1, @CurU - @PreU -1)
set @pids = ltrim(rtrim(@pids))
set @PreU_pid = 0
set @CurU_pid = charindex(',', @pids+',')
if(@rid<>'')
begin
--这个里面在循环pids即可
while (@CurU_pid > 0)
begin
set @pid = substring(@pids, @PreU_pid+1, @CurU_pid - @PreU_pid -1)
if(@pid <>'')
begin
--print @rid+','+@pid +'|'
insert table2 select @rid,Convert(int,@pid)
end
set @PreU_pid = @CurU_pid
set @CurU_pid = charindex(',', @pids+',', @CurU_pid+1)
end
end
set @PreU = @CurU
set @CurU = charindex(',', @rids+',', @CurU+1)
end
select * from table2