CODEWITHSUNDEEP
sql-serverscope-identity
Hariom
Hariom

Reputation: 1

Get ID In Bulk inserting

I am inserting SQL table data through XML file

<root>
  <Attributedata>
    <AttributeLabelID>-1</AttributeLabelID>
    <ProductID>1</ProductID>
    <AttributeLabel>User Label</AttributeLabel>
    <AttributeTypeID>1</AttributeTypeID>
    <Value>
        <valueID>-1 </valueID>
        <Uservalue>sss </Uservalue>
        <ProductAttributelID>1</ProductAttributelID>
    </Value>
    <Value>
        <valueID>-1 </valueID>
        <Uservalue>sss </Uservalue>
        <ProductAttributelID>1</ProductAttributelID>
    </Value>
    <CreatedBy >1</CreatedBy>
</Attributedata>
<Attributedata>

    <AttributeLabelID>-1</AttributeLabelID>
    <ProductID>1</ProductID>
    <AttributeLabel>User Label</AttributeLabel>
    <AttributeTypeID>1</AttributeTypeID>
    <Value>
        <valueID>-1 </valueID>
        <Uservalue>sss </Uservalue>
        <ProductAttributelID>1</ProductAttributelID>
    </Value>

    <CreatedBy >1</CreatedBy>
</Attributedata>
<Attributedata>

    <AttributeLabelID>-1</AttributeLabelID>
    <ProductID>1</ProductID>
    <AttributeLabel>User Label</AttributeLabel>
    <AttributeTypeID>1</AttributeTypeID>
    <Value>
        <valueID>-1 </valueID>
        <Uservalue>sss </Uservalue>
        <ProductAttributelID>1</ProductAttributelID>
    </Value>
    <Value>
        <valueID>-1 </valueID>
        <Uservalue>sss </Uservalue>
        <ProductAttributelID>1</ProductAttributelID>
    </Value>
    <CreatedBy >1</CreatedBy>
</Attributedata>
</root>

In table 1, Attributevalueid is auto generated and primary key. I want to insert at that time Attributevalueid in another table table2 - how it is possible through a stored procedure?

Upvotes: 0

Views: 320

Answers (3)

Mark Brackett
Mark Brackett

Reputation: 85685

Use the OUTPUT clause:

DECLARE @t TABLE ( AttributeValueId int )

INSERT TABLE_NAME ( ... )
SELECT ...
FROM ....
OUTPUT 
    Inserted.AttributeValueId INTO @t

INSERT OTHER_TABLE ( ... )
SELECT AttributeValueId
FROM @t

Upvotes: 1

Kevin Buchan
Kevin Buchan

Reputation: 2868

I agree with the trigger response, but you could also change the field from Identity to a regular int and use a counter table. It would require some additional logic on your end but not a lot and you could keep all of your business logic within your app.

I would still go with the trigger suggestion. I'm only offering this as an alternative that has worked well for me in other situations.

Upvotes: 0

ykatchou
ykatchou

Reputation: 3727

You should probably use a trigger.

Please, retag your questions.

Upvotes: 1

Related Questions