mssql 将临时表中随机一条数据，并修改部分字段，写入到另一张表中

例：表A，查询出部分数据生成临时表#B，从临时表B中随机取一条记录，修改其部分字段（创建时间等），再插入表A，请教应该怎么写，我写出来老提示'关键字 order 附件有错误'

9 个解决方案

#1

贴你的语句看看

#2

insert into A
select top 1 ..... from #B order by newid()

你的语句贴出来啊，不然怎么知道哪有问题

#3

drop table ##t
select top 100 * into ##t from busiorder where year(createtime)=2013 and companyid=@companyid and [status]<5
select top 1 * from ##t order By NewID()//这里是可以查询到数据

INSERT INTO busiorder(orderno, busiorderno, subsysid, classid, goodsid, goodscount,customid
,[description],[type],goodsoutreason,serviceday,refusereason,phonenum,[address],recordpoint,
creator,createtime,updater,lastmodify,paymode,contacttime,callbackflag,processorid,companyid,
syncflag,accountflag,needbill,billtitle,billtype,customname,[status],proccontent,totalprice,
porderno,priority,areano,callid,billno,resultperiod,resultdesc,resultservice,resultname,sbegindate,
senddate,ticketcallback,callbackcontent,ticketstatus,assigner,allotmode,billdate
) (SELECT top 1 @maxorderno+1, @maxbusiorderno+1, subsysid, classid, goodsid, goodscount,customid
,[description],[type],goodsoutreason,@xdate,refusereason,phonenum,[address],recordpoint,
creator,@xdate,updater,@xdate,paymode,contacttime,callbackflag,processorid,companyid,
syncflag,accountflag,needbill,billtitle,billtype,customname,[status],proccontent,totalprice,
porderno,priority,areano,callid,billno,resultperiod,resultdesc,resultservice,resultname,@xdate,
@xdate,ticketcallback,callbackcontent,ticketstatus,assigner,allotmode,billdate FROM ##t order By NewID())

#4

drop table ##t

select top 100 * into ##t from busiorder where year(createtime)=2013 and companyid=@companyid  and [status]<5

select top 1 * from ##t order By NewID()//这里是可以查询到数据


INSERT INTO busiorder(orderno, busiorderno, subsysid, classid, goodsid, goodscount,customid

,[description],[type],goodsoutreason,serviceday,refusereason,phonenum,[address],recordpoint,

creator,createtime,updater,lastmodify,paymode,contacttime,callbackflag,processorid,companyid,

syncflag,accountflag,needbill,billtitle,billtype,customname,[status],proccontent,totalprice,

porderno,priority,areano,callid,billno,resultperiod,resultdesc,resultservice,resultname,sbegindate,

senddate,ticketcallback,callbackcontent,ticketstatus,assigner,allotmode,billdate

) SELECT top 1 @maxorderno+1, @maxbusiorderno+1, subsysid, classid, goodsid, goodscount,customid

,[description],[type],goodsoutreason,@xdate,refusereason,phonenum,[address],recordpoint,

creator,@xdate,updater,@xdate,paymode,contacttime,callbackflag,processorid,companyid,

syncflag,accountflag,needbill,billtitle,billtype,customname,[status],proccontent,totalprice,

porderno,priority,areano,callid,billno,resultperiod,resultdesc,resultservice,resultname,@xdate,

@xdate,ticketcallback,callbackcontent,ticketstatus,assigner,allotmode,billdate FROM ##t order By NewID()

#5

insert into xxx select的时候，外层不要加括号

#6

另外，更随机的方法是order by checksum(newid())

#7

谢拉，为什么那里不能加括号？

#8

语法规定，insert into xxx values()这个要加括号，但是insert into xxx select就不能，括号是指一个“值”，而select的时候指一个“集合”

#9

恩，谢啦，呵呵，结贴

#1