通过更改几个列值将行复制到同一表中

Question

我想通过更改几个列值将表中的一行复制到同一个表中。根据该行中的形状，我必须将行从形状表复制到具有新 id 的同一表中。

我试过这个：

CREATE PROCEDURE Sp_CopyAnnotation
@AnnotationID int,
@AssetVersionID int,
@NewAssetVersionID int
AS
DECLARE @NewAnnotationID int
DECLARE @Shape varchar(50)
BEGIN
insert into AnnotationMain(Text,x, y,Shape,Color,z1,JobID,AssetID,UserID,UserName,Department,AssetVersionID,HideAnnotation)
select  Text
        ,x
        ,y
        ,Shape
        ,Color
        ,z1
        ,JobID
        ,AssetID
        ,AssetVersionID
        ,UserID,UserName
        ,Department
        ,@NewAssetVersionID
        ,1
        from AnnotationMain where AnnotationID=@AnnotationID;
        --set @NewAnnotationID=@@IDENTITY
select @NewAnnotationID=AnnotationID,@Shape=Shape from Proofing.AnnotationMain where AnnotationID=@@IDENTITY  
update AnnotationMain 
set Annoation_num=(select (max(Annoation_num)+1) 
                   from AnnotationMain
                   where AssetVersionID=@AssetVersionID)
where AnnotationID=@NewAnnotationID
IF(@shape='ellipse')
BEGIN 
Insert into CircleTable(AnnotationID,x1,x2,y1,y2)
select @NewAnnotationID
       ,x1
       ,x2
       ,y1
       ,y2
from CircleTable where AnnotationID=@AnnotationID
select result=1;
END
ELSE IF(@shape='rectangle')
BEGIN
Insert into RectangleTable(AnnoationID,x,y,w,h)
select @NewAnnotationID
       ,x
       ,y
       ,w
       ,h
from RectangleTable where AnnoationID=@AnnotationID
select result=1;
END
ELSE IF(@shape='pencil')
BEGIN
Insert into PencilTable(AnnoationID,x1,y1,cord)
select @NewAnnotationID
       ,x1
       ,y1
       ,cord
from PencilTable where AnnoationID=@AnnotationID
select result=1;
END
END
GO

但它给了我一个错误

Msg 121，级别 15，状态 1，过程 Sp_CopyAnnotation，第 23 行 INSERT 语句的选择列表包含的项目多于插入列表。选择值的数量必须与插入列的数量相匹配。

而且我不会纠结于找出我哪里出了问题

Answer 1

插入

列列表中缺少AssetVersionID

INSERT INTO AnnotationMain
            (Text,
             x,
             y,
             Shape,
             Color,
             z1,
             JobID,
             AssetID,
             AssetVersionID, --Missing 
             UserID,
             UserName,
             Department,
             AssetVersionID,
             HideAnnotation)
SELECT Text,
       x,
       y,
       Shape,
       Color,
       z1,
       JobID,
       AssetID,
       AssetVersionID,
       UserID,
       UserName,
       Department,
       @NewAssetVersionID,
       1
FROM   AnnotationMain
WHERE  AnnotationID = @AnnotationID;