DECLARE @ID INT SET @ID=1 DECLARE @COUNT INT SELECT @COUNT =COUNT(1) FROM B WHILE @ID<=@COUNT BEGIN INSERT INTO A SELECT @ID,NAME,Age from B SET @ID=@ID+1 END
declare @id int set @id=select id from A insert into A select isnull(max(@id),0),* from B 就这样的思路吧
INSERT INTO A SELECT rn=ROW_NUMBER()over(order by Name),Name,Age FROM B
-- 取出最大ID declare @maxID int select @maxID = max(ID) from A -- 插入数据 insert into A(ID, Name, AGE) select ID=(row_number() over (order by Name))+@MaxID, Name, Age from B
给你个结果CREATE TABLE A ( ID INT NOT NULL, Name VARCHAR(100), age INT ) GO CREATE TABLE B ( Name VARCHAR(100), Age INT ) GOINSERT INTO B SELECT 'haha',3 UNION SELECT 'hoho',3 UNION SELECT 'hehe',3 INSERT INTO A SELECT rn=ROW_NUMBER()over(order by Name),Name,Age FROM BSELECT * FROM AID Name age 1 haha 3 2 hehe 3 3 hoho 3
关键字 'select' 附近有语法错误。
若表A中已存在数据,则使用下面的语句INSERT INTO A SELECT rn=ROW_NUMBER()over(order by Name) + (SELECT MAX(Id) FROM A),Name,Age FROM B
A表中有数据 DECLARE @ID INT SET @ID=1 DECLARE @COUNT INT,@acount int SELECT @COUNT =COUNT(1) FROM B select @acount =ISnull(MAX(id),0) from a WHILE @ID<=@COUNT BEGIN INSERT INTO A SELECT @ID+@acount,NAME,Age from B SET @ID=@ID+1 END
INSERT INTO A
SELECT Name,Age
FROM B
DECLARE @ID INT
SET @ID=1
DECLARE @COUNT INT
SELECT @COUNT =COUNT(1) FROM B
WHILE @ID<=@COUNT
BEGIN
INSERT INTO A SELECT @ID,NAME,Age from B
SET @ID=@ID+1
END
set @id=select id from A
insert into A select isnull(max(@id),0),* from B
就这样的思路吧
INSERT INTO A
SELECT rn=ROW_NUMBER()over(order by Name),Name,Age
FROM B
declare @maxID int
select @maxID = max(ID) from A
-- 插入数据
insert into A(ID, Name, AGE)
select ID=(row_number() over (order by Name))+@MaxID, Name, Age from B
(
ID INT NOT NULL,
Name VARCHAR(100),
age INT
)
GO
CREATE TABLE B
(
Name VARCHAR(100),
Age INT
)
GOINSERT INTO B
SELECT 'haha',3 UNION
SELECT 'hoho',3 UNION
SELECT 'hehe',3
INSERT INTO A
SELECT rn=ROW_NUMBER()over(order by Name),Name,Age
FROM BSELECT * FROM AID Name age
1 haha 3
2 hehe 3
3 hoho 3
SELECT rn=ROW_NUMBER()over(order by Name) + (SELECT MAX(Id) FROM A),Name,Age
FROM B
DECLARE @ID INT
SET @ID=1
DECLARE @COUNT INT,@acount int
SELECT @COUNT =COUNT(1) FROM B
select @acount =ISnull(MAX(id),0) from a
WHILE @ID<=@COUNT
BEGIN
INSERT INTO A SELECT @ID+@acount,NAME,Age from B
SET @ID=@ID+1
END