A表有ID,NAME,AGE,三列,且ID是主键且不是自动增长列
B表有NAME和AGE两列,NAME是主键
现在要把B表所有记录插入到A表中,
sqs语句怎么写？谢谢！
------解决方案--------------------
- SQL code
INSERT INTO ASELECT Name,AgeFROM B
------解决方案--------------------
- SQL code
DECLARE @ID INTSET @ID=1DECLARE @COUNT INTSELECT @COUNT =COUNT(1) FROM BWHILE @ID<[email protected]BEGIN INSERT INTO A SELECT @ID,NAME,Age from B SET @[email protected]+1END
------解决方案--------------------
declare @id int
set @id=select id from A
insert into A select isnull(max(@id),0),* from B
就这样的思路吧
------解决方案--------------------
- SQL code
INSERT INTO ASELECT rn=ROW_NUMBER()over(order by Name),Name,AgeFROM B
------解决方案--------------------
- SQL code
-- 取出最大IDdeclare @maxID intselect @maxID = max(ID) from A-- 插入数据insert into A(ID, Name, AGE)select ID=(row_number() over (order by Name))[email protected], Name, Age from B
------解决方案--------------------
给你个结果
- SQL code
CREATE TABLE A( ID INT NOT NULL, Name VARCHAR(100), age INT)GOCREATE TABLE B( Name VARCHAR(100), Age INT)GOINSERT INTO BSELECT 'haha',3 UNIONSELECT 'hoho',3 UNIONSELECT 'hehe',3INSERT INTO ASELECT rn=ROW_NUMBER()over(order by Name),Name,AgeFROM BSELECT * FROM AID Name age1 haha 32 hehe 33 hoho 3
------解决方案--------------------
若表A中已存在数据，则使用下面的语句
- SQL code
INSERT INTO ASELECT rn=ROW_NUMBER()over(order by Name) + (SELECT MAX(Id) FROM A),Name,AgeFROM B