Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: MS SQL Server Set Identity Insert on Multiple Tables

  1. #1
    Join Date
    Aug 2012

    Default MS SQL Server Set Identity Insert on Multiple Tables

    Hi Friends,

    I have a transforamtion which transfers data from source to desitnation (SQL table To Sybase table).

    The table has an identity column , hence i have written 'Set Indentity insert table1 On' in the 'Advanced Tab' of 'Database Connection'.

    This is working fine for 1 table , but if i have 2 tables i have put as:

    'Set Indentity insert table1 On';'Set Indentity insert table2 On'

    This is not working.

    Since both the tables have identity column, i want to first make their identity inserts on and then insert the data.

    How can i achieve the same?
    I want to create a single job which will calls the 2 transformations and inserts data into the 2 tables at one time.

    Please suggest.

    Thank you.

  2. #2
    Join Date
    Apr 2008


    MS SQL does not support having Set Identity Insert on on two tables at the same time.

    You would have to do one table output, then do your Set Table Identity on in a SQL script, then do the next table.
    HOWEVER, you would have to use Transform is Database Transactional, *AND* figure out a way to run the SQL script once *AFTER* the table output is done.

    Not an easy setup.
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  3. #3
    Join Date
    Jun 2012


    Maybe there is a simple solution: Just don't SET IDENTITY_INSERT ON in the connection object, try to make it part of your transformation (Execute SQL script).
    So long, and thanks for all the fish.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.