Insert From Table Update Identity to table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert From Table Update Identity to table

Author

Topic

psycotech
Starting Member

5 Posts

Posted - 2009-05-04 : 21:31:28

Sounds Like a @@Identity...

INSERT INTO Values(aValue)
OUTPUT INSERTED.NewID, FromTable.OriginalID INTO TempTable
SELECT Value
FROM FromTable

UPDATE F
SET F.NewID = T.NewID
FROM FromTable F, TempTable T
WHERE F.OriginalID = T.OriginalID

SELECT OriginalID, NewID, Value
FROM FromTable

------------------------------------------------

OK So I tried this way and no suprise OUTPUT doesn't support FromTable on INSERT, works on UPDATE DELETE but not INSERT.

Is there something i'm missing here like an OUTPUTACADABRAH Function.
I don't want to insert the originalID into my values table.
PS values are not unique.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-07 : 13:48:43

how are FromTable and Values related? what determines which value of FromTable.OriginalID goes with which newly generated NewID of Values? may be you could explain with data sample to make it clear.

psycotech
Starting Member

5 Posts

Posted - 2009-05-11 : 05:54:55

ok I have a table with a list of values each value has a unique ID its only for reference.
The values are in a semi-temp Table and need to be inserted into a master Table.
The master table has an Identifier (auto incremented).
I need to retrieve the inserted ID from the master table into the Semi-temp table.

I tried to place a trigger into a view so that instead of insert it would also update the temp table with an @@Identity but all it did was update to the last ID inserted. It would work if used one record at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources