Data Migration

[WPedersen]

Hello -

I'm trying to migrate data from an Access 2000 database to SQL 2005
Express. 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.tblCompa ny.row(x).item( "CompanyID" ) =
ds_Access.tblCo mpany.row(x).it em("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.

[Dave Sexton]

Re: Data Migration

Hi Wayne,

If the Sql database column is an Identity column, then you will not be able to suppress the auto-numbering from code. In that case
you can use IDENTITY_INSERT in your insert procedure:

ALTER PROC ImportCompany (int @CompanyID, nvarchar(max) @Name)
AS BEGIN

SET NOCOUNT OFF;
SET IDENTITY_INSERT dbo.Companies ON;

INSERT dbo.Companies SELECT @CompanyID, @Name;

SET IDENTITY_INSERT dbo.Companies OFF;

END

--
Dave Sexton

"WPedersen" <Wayne.Pedersen @no.spam.teksol .comwrote in message news:OC7VHcTwGH A.4576@TK2MSFTN GP03.phx.gbl...

Hello -
>
I'm trying to migrate data from an Access 2000 database to SQL 2005 Express. 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.tblCompa ny.row(x).item( "CompanyID" ) = ds_Access.tblCo mpany.row(x).it em("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.

[Dave Sexton]

Re: Data Migration

Hi Wayne,

If the Sql database column is an Identity column, then you will not be able to suppress the auto-numbering from code. In that case
you can use IDENTITY_INSERT in your insert procedure:

ALTER PROC ImportCompany (int @CompanyID, nvarchar(max) @Name)
AS BEGIN

SET NOCOUNT OFF;
SET IDENTITY_INSERT dbo.Companies ON;

INSERT dbo.Companies SELECT @CompanyID, @Name;

SET IDENTITY_INSERT dbo.Companies OFF;

END

--
Dave Sexton

"WPedersen" <Wayne.Pedersen @no.spam.teksol .comwrote in message news:OC7VHcTwGH A.4576@TK2MSFTN GP03.phx.gbl...

Hello -
>
I'm trying to migrate data from an Access 2000 database to SQL 2005 Express. 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.tblCompa ny.row(x).item( "CompanyID" ) = ds_Access.tblCo mpany.row(x).it em("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.