How do I make a copy of the inserted records and keep the original id of the record it copied and put it in the Temp table. When I add Docs.DocID to the OUTPUT I get. "The multi-part identifier "Docs.DocID" could not be bound."
DECLARE @CopiedDocIDs TABLE(NewDocID int, CurrentDocID int)
INSERT INTO Docs (Filename,Backup)
OUTPUT INSERTED.DocID, Docs.DocID INTO @CopiedDocIDs
SELECT Filename, 1
FROM Docs
WHERE Filename like 'MyDoc%'
Oh yeah I'm working with: Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
EDIT: Ok somewhat of a hack but it works. Here's it working but I'll add in another column to the RowKey just make sure it's unique.
DECLARE @Docs TABLE (DocID int IDENTITY(1, 1), [FileName] varchar(10), FileDate datetime)
INSERT INTO @Docs VALUES('Doc1','01-01-2011 12:21:12:003')
INSERT INTO @Docs VALUES('Doc2','01-01-2013 02:41:32:120')
INSERT INTO @Docs VALUES('Doc3','01-01-2014 09:30:12:023')
INSERT INTO @Docs VALUES('Doc','01-01-2014 09:30:12:111')
INSERT INTO @Docs VALUES('Doc','01-01-2014 09:30:15:123')
DECLARE @NewDocIDs TABLE(NewDocID int, CopyDocID int, RowKey Varchar(50))
INSERT INTO @Docs ([FileName],FileDate)
OUTPUT INSERTED.DocID, null, (INSERTED.[FileName]+'-'+CONVERT(varchar(50),INSERTED.FileDate,126)) INTO @NewDocIDs
SELECT [FileName],FileDate
FROM @Docs
UPDATE @NewDocIDs SET CopyDocID=(SELECT TOP 1 DocID FROM @Docs WHERE [FileName]+'-'+CONVERT(varchar(50),FileDate,126)=RowKey)
select * from @Docs
select * from @NewDocIDs --## output I need
Aucun commentaire:
Enregistrer un commentaire