jeudi 19 février 2015

SQL Insert with Select and OUTPUT to create a temp copy table

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


enter image description here


Aucun commentaire:

Enregistrer un commentaire