question on OUTPUT insert.x - 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 2005 Forums

Transact-SQL (2005)

question on OUTPUT insert.x

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-23 : 10:58:19

Bonjour!

I have following table
CREATE TABLE [dbo].[blabla](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BranchID] [int] NOT NULL,
[TypeID] [int] NOT NULL,
[Number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Quantity] [int] NULL,
) ON [PRIMARY]
Then I do the following
INSERT INTO dbo.blabla
(BranchID
,TypeID
,Number
,Quantity)
OUTPUT INSERTED.ID, INSERTED.PRLNumber INTO @Inserted
SELECT 1,
2,
'01212009-184',
CAST(QuantityNeeded AS INT)
FROM SM cs
INNER JOIN Quantities cq
ON cs.TableID = cq.TableID
I need to the insert indentity id of SM table in the @inserted table variable. Been banging my head for too long so I thought I would ask the experts.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:09:41

the query looks fine. whats the problem you're facing ? also is @Inserted declared before this?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-23 : 11:11:50

yes @inserted is a table variable declared earlier ---did not want to bore you with the details---
query is good but I need a third column inserted into @inserted table variable.
the OUTPUT INSERTED.ID you see for table blabla...I want the id for table SM in the FROM.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:16:50

is ID being inserted into blabla? if not, it wont be available in INSERTED and so you cant retrieve it using OUTPUT

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-23 : 11:20:49

no SM.ID is not being inserted into blabla...but how can I retrieve
I read that you can do
INSERT INTO t
OUTPUT INSERT.id, INSERTED.meow, s.ID
SELECT x,x,x
FROM table s
INNER JOIN ....... sort of thingy.

Thank you much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:42:53

quote:
Originally posted by yosiasz

no SM.ID is not being inserted into blabla...but how can I retrieve
I read that you can do
INSERT INTO t
OUTPUT INSERT.id, INSERTED.meow, s.ID
SELECT x,x,x
FROM table s
INNER JOIN ....... sort of thingy.

Thank you much

what happened when you tried like above?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-23 : 11:57:10

[code]-- Prepare sample data
DECLARE @Master TABLE (MasterID INT IDENTITY(1, 1), Name VARCHAR(20))

DECLARE @Slave TABLE (SlaveID INT IDENTITY(1, 1), MasterID INT, Name VARCHAR(20))

-- Insert first dummy record
INSERT @Master
(
Name
)
SELECT 'Master 1'

-- Insert second dummy record
INSERT @Master
(
Name
)
OUTPUT inserted.MasterID,
'Slave 1'
INTO @Slave
SELECT 'Master 2'

-- Show results
SELECT *
FROM @Master

SELECT *
FROM @Slave[/code]

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-23 : 13:34:41

thank you all!
visakh16 I tried like that but no go because I am missing a key to join them with. I had to abandone this approach

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-24 : 10:49:27

ok...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources