如何中表A中随机选择6条,添加到表B中,并在表B中的字段id依次为1、2、3、4、5、6

[hhb518]

表A字段：testno bigint 8
testmore ntext 16
test1 nvarchar 100
test2 nvarchar 100
test3 nvarchar 100
testright nvarchar 100
testpic nvarchar 8

表B字段：id bigint 8
username nvarchar 50
examid nvarchar 4
testmore ntext 16
test1 nvarchar 100
test2 nvarchar 100
test3 nvarchar 100
testright nvarchar 100
testpic nvarchar 8
examquestion nvarchar
examscorn int 4

如何从表A中随机选择6条字段为testmore 、test1、test2、test3、testright、testpic记录,添加到表B中的testmore 、test1、test2、test3、testright、testpic，并且examid自动生成为1、2、3、4、5、6 username添加为“小黄”（username字段要通过程序加进去）

[you34]

将examid设为自增长型，然后
insert into b(username,testmore,test1,test2,test3,testright,testpic) select '小黄',testmore,test1,test2,test3,testright,testpic from a

[hhb518]

将examid设为自增长型,不行
我要examid从1到100后
到100后,有要从1到100

[paoluo]

Try

Select Top 6 N'小黄',examid=Identity(Int,1,1),testmore,test1,test2,test3,testright,testpic Into #T from A Order By NewID()
Insert B(username,examid,testmore,test1,test2,test3,testright,testpic) Select * from #T
Drop Table #T

[paoluo]

改下

Select Top 6 N'小黄' As username,examid=Identity(Int,1,1),testmore,test1,test2,test3,testright,testpic Into #T from A Order By NewID()
Insert B(username,examid,testmore,test1,test2,test3,testright,testpic) Select * from #T
Drop Table #T

[hhb518]

examid 编号好象不是1、2、3、4、5、6

[paoluo]

不好意思，寫錯了一點地方。


--建立測試環境
Create Table A
(testno bigint,
testmore ntext,
test1 nvarchar(100),
test2 nvarchar(100),
test3 nvarchar(100),
testright nvarchar(100),
testpic nvarchar(8))

Create Table B
(id bigint,
username nvarchar(50),
examid nvarchar(4),
testmore ntext,
test1 nvarchar(100),
test2 nvarchar(100),
test3 nvarchar(100),
testright nvarchar(100),
testpic nvarchar(8),
examquestion nvarchar(100),
examscorn int)
--插入數據
Insert A Select 1,'AA','A','B','C','KK','MM'
Union All Select 2,'BB','A','B','C','KK','MM'
Union All Select 3,'CC','A','B','C','KK','MM'
Union All Select 5,'DD','A','B','C','KK','MM'
Union All Select 9,'EE','A','B','C','KK','MM'
Union All Select 10,'FF','A','B','C','KK','MM'
Union All Select 11,'GG','A','B','C','KK','MM'
Union All Select 12,'HH','A','B','C','KK','MM'
Union All Select 13,'II','A','B','C','KK','MM'
Union All Select 15,'JJ','A','B','C','KK','MM'
Union All Select 17,'KK','A','B','C','KK','MM'
--測試
Select N'小黃' As username,examid=Identity(Int,1,1),testmore,test1,test2,test3,testright,testpic Into #T from (Select Top 6 * from A Order By NewID()) T
Insert B(username,examid,testmore,test1,test2,test3,testright,testpic) Select * from #T

Select * from B
--刪除測試環境
Drop Table A,B,#T
--結果
/*
id username examid testmore test1 test2 test3 testright examquestion examscorn
NULL 小黃 1 HH A B C KK MM NULL NULL
NULL 小黃 2 EE A B C KK MM NULL NULL
NULL 小黃 3 AA A B C KK MM NULL NULL
NULL 小黃 4 FF A B C KK MM NULL NULL
NULL 小黃 5 DD A B C KK MM NULL NULL
NULL 小黃 6 CC A B C KK MM NULL NULL
*/

[hhb518]

太谢谢您了!

[hhb518]

我顶