Insert a row and gets its id? - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Insert a row and gets its id?

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2009-07-15 : 12:12:08

I have a table (A) that needs a corresponding row in (B). Is there a way to loop through (A), Insert into (B) and Get (B.UniqueId)? Its imported data hence copying some of the fields over


Table A
-------
UniqueId, Name, Date, Details

Table B
-------
Name, Date, Details, ParentId

Something like


UPDATE B SET ParentID =(
INSERT INTO A(Name,Date,Details) 
  SELECT Name,Date,Detail FROM B
)

Any ideas greatly recieved :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-15 : 14:16:48

you need to do it in two steps


INSERT INTO A(Name,Date,Details) 
SELECT Name,Date,Detail FROM B

UPDATE b
SET b.ParentID=a.UniqueId
FROM TableB b
INNER JOIN TableA a
ON a.Name = b.Name 
AND a.Date=b.Date
AND a.Detail=b.Detail

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2009-07-16 : 03:40:55

Thanks visakh, good solution :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 14:21:37

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources