Consume adapter service Insert-operation and identity value

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › Consume adapter service Insert-operation and identity value

Viewing 1 reply thread
  • Author
    Posts
    • August 15, 2009 at 10:00 AM #23045
      community-content

        Hi,

        I’m working with my first Biztalk application, and trying to save data from XML-file to multiple SQL-tables.

        I found article written by Richard Seroter where’s shown how to do that without using Stored procedures. Article is very good, but i faced problem on SQL identity values and foreign keys between tables.

        Example like this:

        TABLE1:
        Table1ID(MSSQL identity on)
        FieldName1
        FieldName2

        TABLE2:
        Table2ID(MSSQL identity on)
        Table1ID(foreign key)
        FieldName1
        FIeldName2

        So, does anyone know is there way to select Table1ID-value from first table and put that to Table2 fk-value?

        Here’s link to the article: http://www.packtpub.com/article/soa-capabilities-in-bizTalk-wcf

        ps.sorry my bad english 🙂

      • August 19, 2009 at 6:31 AM #23065
        community-content

          One possibility is to use a stored procedure:

          mystoredprc @T1Field1, @T1 Field2, @T2Field1, @T2field2
          BEGIN
            INSERT INTO Table1 (FieldName1, FieldName2) VALUES (@T1Field1, @T1 Field2)
            INSERT INTO Table2 (Table1ID, FieldName1, FieldName2) VALUES (@@IDENTITY, @T2Field1, @T2field2)
          END

          Use a composite request as described by Richard in his blog to group multiple stored proc calls.

           

      • Author
        Posts
      Viewing 1 reply thread
      • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.