Has anyone been successful in doing explicit inserts into columns with
identity properties from PERL? I would appreciate any help, or any
experience anyone has had with this.
I am using dbi with dbd::odbc, and MS SQL 7.0. autocommit is left on the
default (off). I have thus far tried unsuccessfully two different ways to do
this.
The first way is as follows:
$sth = $dbh->prepare(qq{SET identity_insert $target..$table ON});
$sth->execute() || print "$target..$table does not have the identity
property \n";
my $insert=qq{INSERT INTO $target..$table ($columnlist) SELECT
$columnlist FROM $source..$table};
my $sth = $dbh->prepare($insert);
$sth->execute || die "Can't execute ($insert): $dbh->errstr";
This prints the message I specified for all tables that do not have identity
property, and the insert works perfectly on them. When it hits a table with
an identity column, even though I have set identity insert on for that
table - and that is session-specific normally - I still get the error
telling me:
Cannot insert explicit value for identity column in table
<tablename> when IDENTITY_INSERT is set to off.
The second way I have tried (Doesn't really make sense that a table property
can be set here, but if it can, I don't have it right) is to connect as so:
my $dbh = DBI->connect($dsn, $db_user, $db_password,
{IDENTITY_INSERT=>1});
This does not produce an error - it just doesn't work.
In our case a DTS package to do this migration will be much less flexible,
and much more work for the number of databases, and possible situations than
what we have almost worked out. I would appreciate any help as this is too
close to perfection to be hung on this detail at this point in the project.
Thanks,
Steve Howard
