Post reply

DTS select/insert with set identity_insert on

  • July 8, 2004 at 9:52 am

    #76174

     

    I want to use DTS for data conversion; however I want to do something like the following T-SQL:

    SET IDENTITY_INSERT ON

    GO

    SELECT

     Id=CAST (c.Idn AS INTEGER),

    I.e. I want to load the result identity field with the old data from the source table.  DTS doesn't seem to allow me to have the multiple statements.

    Suggestions for using DTS to accomplish this?

    Thanks,

    Al

  • July 8, 2004 at 10:31 am

    #513881

    You may have to elaborate on what the problem is.

    DTS doesn't allow you to have the multiple statements where? In an Execute-SQL task? It should do...

     

    One other thing. The syntax for setting identity_insert is:

    set identity_insert <table_name> ON

    In the code above you missed <table_name>

    Regards

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • July 9, 2004 at 9:10 am

    #514109

    Another option is to use a Transform Data Task.  You can put your select statement in the Source tab then check 'Enable identity insert' on the Options tab.

     

    Greg

    Greg

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply