sql中一个表的数据插入到另一个表中sql语句怎么写？谢谢！

[myl_top]

A表有ID,NAME,AGE,三列,且ID是主键且不是自动增长列
B表有NAME和AGE两列,NAME是主键
现在要把B表所有记录插入到A表中,
sqs语句怎么写？谢谢！

[孤独加百列]

INSERT INTO A
SELECT Name,Age
FROM B

[myl_top]

请高手指点一下！

[Felixzhaowenzhong]

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 

[wuhailin010]

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

[simonxt]

-- 取出最大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
)
GO

INSERT 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 B

SELECT * FROM A

ID	Name	age
1	haha	3
2	hehe	3
3	hoho	3

[myl_top]

[Quote=引用 4 楼 的回复:]
declare @id int
set @id=select id from A
insert into A select isnull(max(@id),0),* from B
就这样的思路吧
[/Quote]关键字 'select' 附近有语法错误。

[myl_top]

3楼的可以

[孤独加百列]

若表A中已存在数据，则使用下面的语句

INSERT INTO A
SELECT rn=ROW_NUMBER()over(order by Name) + (SELECT MAX(Id) FROM A),Name,Age
FROM B

[myl_top]

[Quote=引用 10 楼 magician547 的回复:]
若表A中已存在数据，则使用下面的语句

SQL code

INSERT INTO A
SELECT rn=ROW_NUMBER()over(order by Name) + (SELECT MAX(Id) FROM A),Name,Age
FROM B
[/Quote]谢谢

[Felixzhaowenzhong]

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 

[myl_top]

[Quote=引用 12 楼 的回复:]
SQL code
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 SELEC……
[/Quote]谢谢高手指点