I'm trying to migrate data from an Access 2000 database to SQL 2005
Express programatically. Both databases have the same schema.
Most primary keys are Auto Increment fields. I'm having the issue that
even if I set primary key manually in my code, the Auto Increment rules
seem to override this. This is very undesirable, as it would corrupt
the relationships of existing data when I read from Access and Write to SQL.
How can I add rows to the SQL database bypassing the Auto Increment value?
i.e.:
ds_SQL.tblCompany.row(x).item("CompanyID") = ds_Access.tblCompany.row(x).item("CompanyID")
The data migrates, but the Auto Increment value is used. I'm using
typed datasets, ensured the ReadOnly value is False in the Dataset, and
even tried turning off Auto Increment at the Dataset level.
Thanks - I'm lost...
Wayne P.This is a multi-part message in MIME format.
--=_NextPart_000_040F_01C6C10F.1CE1AD20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Add the following statement BEFORE each of your insert statements.
SET IDENTITY_INSERT dbo.MyTable ON
INSERT INTO dbo.MyTable
SELECT {ColumnList}
FROM {AccessTable}
When finished, determine the highest IDENTITY value and reset the =IDENTITY seed for the table.
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"WPedersen" <wayne.pedersen@.no.spam.teksol.com> wrote in message =news:OIfLoXUwGHA.4416@.TK2MSFTNGP03.phx.gbl...
> Hello -
> > I'm trying to migrate data from an Access 2000 database to SQL 2005 > Express programatically. Both databases have the same schema.
> > Most primary keys are Auto Increment fields. I'm having the issue that =
> even if I set primary key manually in my code, the Auto Increment =rules > seem to override this. This is very undesirable, as it would corrupt > the relationships of existing data when I read from Access and Write =to SQL.
> > How can I add rows to the SQL database bypassing the Auto Increment =value?
> i.e.:
> ds_SQL.tblCompany.row(x).item("CompanyID") =3D > ds_Access.tblCompany.row(x).item("CompanyID")
> > The data migrates, but the Auto Increment value is used. I'm using > typed datasets, ensured the ReadOnly value is False in the Dataset, =and > even tried turning off Auto Increment at the Dataset level.
> > Thanks - I'm lost...
> > > Wayne P.
--=_NextPart_000_040F_01C6C10F.1CE1AD20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Add the following statement BEFORE each =of your insert statements.
SET IDENTITY_INSERT dbo.MyTable =ON
INSERT INTO =dbo.MyTable
SELECT {ColumnList}
FROM {AccessTable}
When finished, determine the highest =IDENTITY value and reset the IDENTITY seed for the table.
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"WPedersen"
--=_NextPart_000_040F_01C6C10F.1CE1AD20--|||WPedersen wrote:
> Hello -
> I'm trying to migrate data from an Access 2000 database to SQL 2005
> Express programatically. Both databases have the same schema.
> Most primary keys are Auto Increment fields. I'm having the issue that
> even if I set primary key manually in my code, the Auto Increment rules
> seem to override this. This is very undesirable, as it would corrupt
> the relationships of existing data when I read from Access and Write to SQL.
> How can I add rows to the SQL database bypassing the Auto Increment value?
> i.e.:
> ds_SQL.tblCompany.row(x).item("CompanyID") => ds_Access.tblCompany.row(x).item("CompanyID")
> The data migrates, but the Auto Increment value is used. I'm using
> typed datasets, ensured the ReadOnly value is False in the Dataset, and
> even tried turning off Auto Increment at the Dataset level.
> Thanks - I'm lost...
>
> Wayne P.
Look at identity_insert option in BOL.
Regards
Amish Shah
http://shahamishm.tripod.com|||Arnie / Amish:
Thanks!
This is what I needed!
Wayne P.
Arnie Rowland wrote:
> Add the following statement BEFORE each of your insert statements.
> SET IDENTITY_INSERT dbo.MyTable ON
> INSERT INTO dbo.MyTable
> SELECT {ColumnList}
> FROM {AccessTable}
>
> When finished, determine the highest IDENTITY value and reset the
> IDENTITY seed for the table.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "WPedersen" <wayne.pedersen@.no.spam.teksol.com
> <mailto:wayne.pedersen@.no.spam.teksol.com>> wrote in message
> news:OIfLoXUwGHA.4416@.TK2MSFTNGP03.phx.gbl...
> > Hello -
> >
> > I'm trying to migrate data from an Access 2000 database to SQL 2005
> > Express programatically. Both databases have the same schema.
> >
> > Most primary keys are Auto Increment fields. I'm having the issue that
> > even if I set primary key manually in my code, the Auto Increment rules
> > seem to override this. This is very undesirable, as it would corrupt
> > the relationships of existing data when I read from Access and Write
> to SQL.
> >
> > How can I add rows to the SQL database bypassing the Auto Increment
> value?
> > i.e.:
> > ds_SQL.tblCompany.row(x).item("CompanyID") => > ds_Access.tblCompany.row(x).item("CompanyID")
> >
> > The data migrates, but the Auto Increment value is used. I'm using
> > typed datasets, ensured the ReadOnly value is False in the Dataset, and
> > even tried turning off Auto Increment at the Dataset level.
> >
> > Thanks - I'm lost...
> >
> >
> > Wayne P.
No comments:
Post a Comment