求数据插入SQL语句一条。

[opxilong]

求表A
ID VALUE
1 名1
2 名2
3 名3
1 名1"
2 名2"
3 名3"
实现从上表中选择ID为1和2的数据插入到新表B的SQL语句，谢谢！

表B
ID_1 VALUE_1 ID_2 VALUE_2
1 名1 2 名2
1 名1" 2 名2"

[opxilong]

表B
ID_1 VALUE_1 ID_2 VALUE_2
1 名1 2 名2
1 名1" 2 名2"

[-狙击手-]

declare @t table( ID int,VALUE varchar(10))
insert @t select
  1,'名1' union select    
  2,'名2' union select    
  3,'名3' union select 
  1,'名1"' union select    
  2,'名2"' union select    
  3,'名3"' 

 
select id,value,px = identity(int,1,1) into #1
from @t
where id = 1 
select id,value,px = identity(int,1,1) into #2
from @t
where id = 2 

select a.id,a.value,b.id,b.value
from #1 a,#2 b
where a.px = b.px

/*


id          value      id          value      
----------- ---------- ----------- ---------- 
1           名1         2           名2
1           名1"        2           名2"

（所影响的行数为 2 行）
*/

drop table #1,#2

[pt1314917]

declare @a table(id int,value varchar(10))
insert into @a select 1,'名1'
insert into @a select 2,'名2'
insert into @a select 3,'名3'
insert into @a select 1,'名1"'
insert into @a select 2,'名2"'
insert into @a select 3,'名3"'


declare @b table(id_1 int,value_1 varchar(10),id_2 int,value_2 varchar(10))


insert into @b select * from @a a,@a b 
where a.id=1 and b.id=2 and charindex('"',a.value)=charindex('"',b.value)

select * from @b

[-晴天]

这两个表都不满足完整性要求,已经不能在数据库中进行处理.如一定要这样做,只能如此:

declare @a char(10),@b char(10)

set @a=(select top 1 value from A where id=1)
set @b=(select top 1 value from A where id=2)

insert into B values(1, @a, 2, @b)
insert into B values(1, @a, 2, @b)

[dawugui]

create table A(ID int, VALUE varchar(10))
insert into A values(1, '名1') 
insert into A values(2, '名2') 
insert into A values(3, '名3') 
insert into A values(1, '名1"') 
insert into A values(2, '名2"') 
insert into A values(3, '名3"') 
create table B(ID_1 int, VALUE_1 varchar(10), ID_2 int, VALUE_2 varchar(10))
go

insert into B
select m.id , m.value , n.id , n.value from
(select * , px = (select count(1) from A where id = 1 and value < t.value) + 1 from A t where id = 1) m
full join
(select * , px = (select count(1) from A where id = 2 and value < t.value) + 1 from A t where id = 2) n
on m.px = n.px

select * from B

drop table A,B

/*
ID_1        VALUE_1    ID_2        VALUE_2    
----------- ---------- ----------- ---------- 
1           名1         2           名2
1           名1"        2           名2"
（所影响的行数为 2 行）
*/

[opxilong]

谢谢各位的提供的SQL CODE，
INSERT INTO B(ID_1,VALUE_1,ID_2,VALUE_2)
SELECT ID, VALUE FROM B WHERE ID=1 ,SELECT ID, VALUE FROM B WHERE ID=2
插入子查询结果中子查询能连接吗，从而实现同时插和入多项子查询结果，以上语句如何调整后能实现该功能。

[-狙击手-]

INSERT INTO B(ID_1,VALUE_1,ID_2,VALUE_2)
SELECT ID, VALUE FROM B WHERE ID=1 ,SELECT ID, VALUE FROM B WHERE ID=2
-----------------

不能这样写的

[-狙击手-]

看你的value在ID一样的前提下是否有重复值
如果没有可用子查询生成顺序号关联
如果有重复在2000下能自增列的中间表，在05可用row_number()