Inserted from another table and get both table Id
Sql:
CREATE TABLE OldTable
(
TaskId INT PRIMARY KEY IDENTITY,
TaskName VARCHAR(32)
)
CREATE TABLE NewTable
(
TaskId INT PRIMARY KEY IDENTITY,
TaskName VARCHAR(32)
)
INSERT INTO dbo.oldTable VALUES('TaskNo1')
INSERT INTO 开发者_如何学编程dbo.oldTable VALUES('TaskNo2')
INSERT INTO dbo.oldTable VALUES('TaskNo3')
INSERT INTO dbo.oldTable VALUES('TaskNo4')
DECLARE @MappingTableVar TABLE
(
NewTaskid INT,
OldTaksId int
);
INSERT INTO NewTable(TaskName)
OUTPUT INSERTED.TaskId INTO @MappingTableVar(NewTaskId)
SELECT TaskName from OldTable
SELECT * FROM @MappingTableVar
DROP TABLE NewTable;
DROP TABLE OldTable;
I would like to get mapping of OldTable's TaskId vs NewTable's TaksId. I managed to get NewTaskId only.How can i get OldTask?
Need help on this.
Thanks
The simplest solution be:
Insert NewTable( TaskName )
Select TaskName
From OldTable
Insert @MappingTableVar( NewTaskId, OldTaskId )
Select NewTable.TaskId, OldTable.TaskId
From NewTable
Join OldTable
On OldTable.TaskName = NewTable.TaskName
There is no way to get the OldTable's TaskId using the Output clause unless you include it in the insert (which means you need a column for it in NewTable):
Alter Table NewTable
Add OldTaskId int
GO
Insert NewTable( TaskName, OldTaskId )
Output inserted.TaskId, inserted.OldTaskId Into @MappingTableVar( NewTaskId, OldTaskId )
Select TaskName, TaskId
From OldTable
精彩评论