0

I have a Hotel table.

 create table DimHotel
 (
  Hotel_ID int Identity(1, 1),
  HotelName nvarchar(max)
 )

Now i get some feeds as HotelNames, now our business logic is use Hotel_ID for existing HotelName and create new record for HotelName which is new and use newly generated Hotel_ID. Now I understood that I can direct "Lookup No Match Output" to OLE DB Command Transformation but then how i get newly generated key. One option is do one more lookup on DimHotel table (I guess this is stupid, scanning a table twice). What other option do we have.

I can use but Merge statement but our ETL team wants to avoid hand coding.

Marcel N.
  • 13,726
  • 5
  • 47
  • 72
Pritesh
  • 1,938
  • 7
  • 32
  • 46

1 Answers1

2

I think its better to use a stored procedure which retrieves the SCOPE_IDENTITY as an output parameter .

Example :-

     Create Procedure 
     (
      column names ,
      @id int 
     ) 
     AS
     BEGIN
     INSERT INTO TableName  VALUES (@col1)
     SELECT @id = SCOPE_IDENTITY()
    END
praveen
  • 12,083
  • 1
  • 41
  • 49