Data Modeling Question: One Entity or Two

I am evaluating an existing MS Access database for a new client - with the
intent to migrate it to SQL Server and possibly change/improve the
relational database design in the process.
The client is a medical specialty office in which patients show up with lab
results taken by their primary care physician (PCP) prior to the patient
showing up at my client's specialty office. My client's office then proceeds
to take additional lab measurements over time. The data collected by the PCP
and my client's office is practically the same.
My client's existing MS Access database stores this information in two
tables - one for labs taken at the PCP office (and apparently only the most
recent set of results prior to the patient showing up at my client's
office); and another table for labs taken at my client's office (and
measured over time). In the existing database these two tables have similar
(almost identical) columns.
The client's in-house DBA sees these tables as representing two entities,
not one (one entity is "last set of labs measured by the PCP" and the other
entity is "labs measured in-house; over time"). I understand all these lab
results as one entity ("lab results"); and therefore we can/should move all
this data into one table when we migrate the data to the new SQL Server
database.
What do you think? Do these lab results represent one entity or two?
FWIW: The business managers do not differentiate between the two types of
labs (measured by PCP vs in-house).
Thanks!Sounds to me like one entity. I would add a column to one existing table
(possibly a CHAR(1)) that specified the source of the results. Be careful
in how you approach it though, as they might understand some arcane medical
regulation as specifying that they have to keep these data items physically
separated in some fashion (usually a misunderstanding of the regulations by
management, but I've seen stranger things...)
"Jeff" <A@.B.COM> wrote in message
news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
>I am evaluating an existing MS Access database for a new client - with the
>intent to migrate it to SQL Server and possibly change/improve the
>relational database design in the process.
> The client is a medical specialty office in which patients show up with
> lab results taken by their primary care physician (PCP) prior to the
> patient showing up at my client's specialty office. My client's office
> then proceeds to take additional lab measurements over time. The data
> collected by the PCP and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the
> most recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have
> similar (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the
> other entity is "labs measured in-house; over time"). I understand all
> these lab results as one entity ("lab results"); and therefore we
> can/should move all this data into one table when we migrate the data to
> the new SQL Server database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
>|||A good case for horizontal partitioning. With sql2k, I would keep them as 2
seperate entities (i.e 2 tables). But if you're on sql2k5, you might want to
look into table partitioning. There is definitely a performance gained by
partitioning them 'cuz the data for each will be smaller. Though, it would
require a union/join to look for data in both entities - a minor drawback in
this case.
-oj
"Jeff" <A@.B.COM> wrote in message
news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
>I am evaluating an existing MS Access database for a new client - with the
>intent to migrate it to SQL Server and possibly change/improve the
>relational database design in the process.
> The client is a medical specialty office in which patients show up with
> lab results taken by their primary care physician (PCP) prior to the
> patient showing up at my client's specialty office. My client's office
> then proceeds to take additional lab measurements over time. The data
> collected by the PCP and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the
> most recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have
> similar (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the
> other entity is "labs measured in-house; over time"). I understand all
> these lab results as one entity ("lab results"); and therefore we
> can/should move all this data into one table when we migrate the data to
> the new SQL Server database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
>|||Jeff wrote:
> [...] Do these lab results represent one entity or two?
I would use separate tables only if there is some information that
should be handled differently, for example if:
- the in-house lab results should be linked to a table about payments
(whereas the PCP resuls should not);
- the in-house lab results should have a MeasurerID column that refers
to our Employees table;
- etc.
Think about the such differences between the two types of lab results;
of course, if there are very few differences, they can be handled by
using a null-able column in the unified table (along some check
constraints, to enforce that for a particular type that column should
not be null). However, if there are more differences, having a lot of
null-able columns would not be "a Good Thing", so a two-tables approach
may be better in such a case.
Razvan|||If one table only has the most recent results, and the other tracks history,
you may be better off keeping them seperate. It could be a nightmare
cleaning up the "old" rows from the PCP if they are in the same table with
the history data. Also, if the foreign keys behave differently in these two
tables then you would want to keep them seperate.
"Jeff" <A@.B.COM> wrote in message
news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
> I am evaluating an existing MS Access database for a new client - with the
> intent to migrate it to SQL Server and possibly change/improve the
> relational database design in the process.
> The client is a medical specialty office in which patients show up with
lab
> results taken by their primary care physician (PCP) prior to the patient
> showing up at my client's specialty office. My client's office then
proceeds
> to take additional lab measurements over time. The data collected by the
PCP
> and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the
most
> recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have
similar
> (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the
other
> entity is "labs measured in-house; over time"). I understand all these lab
> results as one entity ("lab results"); and therefore we can/should move
all
> this data into one table when we migrate the data to the new SQL Server
> database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
>|||Jeff wrote:
> I am evaluating an existing MS Access database for a new client - with the
> intent to migrate it to SQL Server and possibly change/improve the
> relational database design in the process.
> The client is a medical specialty office in which patients show up with la
b
> results taken by their primary care physician (PCP) prior to the patient
> showing up at my client's specialty office. My client's office then procee
ds
> to take additional lab measurements over time. The data collected by the P
CP
> and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the mos
t
> recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have simila
r
> (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the othe
r
> entity is "labs measured in-house; over time"). I understand all these lab
> results as one entity ("lab results"); and therefore we can/should move al
l
> this data into one table when we migrate the data to the new SQL Server
> database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
In principle if two potential entities have the same attributes then
they are only one entity.
In fact you can go further and say that entities that overlap (sharing
a common key and a common subset of non-key attributes) should be
represented as a single entity.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the feedback Jim. Can you please clarify "if the foreign keys
behave differently"?
What do you mean by "behave differently?
Thanks!
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eKkGd9ljGHA.3588@.TK2MSFTNGP02.phx.gbl...
> If one table only has the most recent results, and the other tracks
> history,
> you may be better off keeping them seperate. It could be a nightmare
> cleaning up the "old" rows from the PCP if they are in the same table with
> the history data. Also, if the foreign keys behave differently in these
> two
> tables then you would want to keep them seperate.
> "Jeff" <A@.B.COM> wrote in message
> news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
> lab
> proceeds
> PCP
> most
> similar
> other
> all
>|||If the two tables have different constraints, or foreign keys that point to
different tables, with different validation, then two tables is probably a
good idea.
If all of the columns in the tables share the same validation, against the
same tables, then one makes sense.
Here is one consideration, however...
If you did put all the data in one table, how do you enforce the business
rule that the PCP data have only one row and the internal data be allowed
history? Also, you need to discern between the two in the app. If you can
accomplish both of those (I'm sure there are many ways) then you should be
fine with one table.
OK, that was two considerations...
If I am being too vague I apologize. Without truly understanding the
business reason for keeping the data separate to begin with it is hard to
say exactly what should be done. From your own description one table seems
to make sense, but if their in house DBA were to explain it my opinion might
change.
"Jordan Richard" <A@.B.COM> wrote in message
news:eUY2mKqjGHA.3496@.TK2MSFTNGP02.phx.gbl...
> Thanks for the feedback Jim. Can you please clarify "if the foreign keys
> behave differently"?
> What do you mean by "behave differently?
> Thanks!
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eKkGd9ljGHA.3588@.TK2MSFTNGP02.phx.gbl...
with
patient
the
entities,
of
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:O6Ve%238ujGHA.4660@.TK2MSFTNGP03.phx.gbl...
> If you did put all the data in one table, how do you enforce the business
> rule that the PCP data have only one row and the internal data be allowed
> history? Also, you need to discern between the two in the app. If you
> can
> accomplish both of those (I'm sure there are many ways) then you should be
> fine with one table.
You can address issue #1 via trigger. #2 can be addressed by adding a
column that specifies where the data came from (i.e., CHAR(1), 'L' = Local
source, 'P' = Partner source, etc.) Of course existing apps would have to
be modified.
Another consideration here might be how often you currently have to combine
the data from the two tables in a query. If you combine them often for
reporting or other purposes, combining them on the server makes sense. If
you don't ever combine them when querying, combining them might not be a
high priority.|||>> What do you think? Do these lab results represent one entity or two? ..
FWIW: The business managers do not differentiate between the two types of l
abs (measured by PCP vs in-house). <<
What is the LOGICAL difference between them? Apparently, none. Ergo,
you use one table and column for the lab_type. Leave this encoding
open enough that you can extend it lately, when add other sources.

data migration from sybase 8.0 to sqlserver 2005

Hi all,

Here i had a task to migrate sybase 8.0 database to mssqlserver 2005 .how can i migrate this using INTEGRATED SERVICES (SSIS) or any other options. .Please try to provide some basical info because i am new to sybase versions.

JSR2005 wrote:

Hi all,

Here i had a task to migrate sybase 8.0 database to mssqlserver 2005 .how can i migrate this using INTEGRATED SERVICES (SSIS) or any other options. .Please try to provide some basical info because i am new to sybase versions.

Data migration from MSAccess to SQL Express 2005

Hi ,

I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?

Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access. The constraint here is that the data needs to be migrated to a completely new schema. I just need to migrate data only and no other objects.

Thanks

Mahesh

Hello,

I am not replying here with any solution as such.

I would like to do same thing.

I have built complete application using MS Access 2003. Some of the highlights of this application are:

Customized login for each user without using User Level Workgroup Security features.

Each user is assigned 1 of 10 different roles. One of the roles is Admin role

Only Admin role has access to database window and all objects like tables, queries, forms, macros, modules etc.

Shift key is disabled so no one can access database window.

Admin can enabled shift key and get temporary access to database window. Shift key gets disabled on exit again.

Application has data capture front-end forms, one-click reports, quick query tool using front-end forms without query grid etc.

Only certain role can add new data, only certain role can edit data, data gets locked after certain time or status of data etc. Only ceratin role can upload/downlaod data etc.

Application is also password protected. Regular user can open application without knowing password as password is integrated in vba code. This password is essential as no one can export data from other database.

Currently all tables are stored in seperate database and linked in main application.

I would like to move all tables to SQL Server Express. I am assuming that by doing this I will be able to secure all tables better and it will also help me increasing size of application beyond 2 GB.

Please let me know step by step process to move Access Tables to SQL server express.

Thanks

|||

Hi Mahesh,

refer http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1056639&SiteID=1 which is answered.

Welcome on a board Adukio,

using SSMA you may migrate your Access DB to SQL 2005 Refer the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1056639&SiteID=1

I would suggest to refer this thread too http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1033679&SiteID=1

Hemantgiri S. Goswami

|||

Hemant,

Thanks for the reply. In fact i had downloaded the SSMA and trying a few things. I don't see a option where in some kind of a column mapping can be done in this tool. What i mean is that :

Table1 (Access Tale) Table2 (SQL Express Table)

Column1 Column1

Column2 Column2

Column3

If we assume a scenerio like the one above where i need to migrate table from a access table to SQL table , if the number of columns do not match (this is very much possible as my target schema has been completely redesigned) , SSMA fails to migrate the data. So i need to know is there a provision for handling a scenerio like this in SSMA?

Thanks

Mahesh

|||

Hi Mahesha,

SSMA does not handle data transformation, which is what you're wanting to do. (The Access Upsizing Wizard doesn't do this either.) The only SQL tool I know of that can do this is SSIS, wich is not included in SQL Express. If you have another version of SQL Server 2005 available, say SQL Dev, you can use SSIS to create a data transformation.

If you don't have another edition of SQL available, you will need to do this manually. I would suggest migrating the data from Access to a new database on your SQL Server, and then use queries to transform the data into your new tables. If this is a process you have to do regularly you can probably work out a process of pulling data into temporary tables and then appending them to your new schema all using Stored Procedures.

Mike

|||

Thanks Mike, I think that answers my query. The data migration is a one time activity here , so i think i don't really need to use temp tables here. May be i'll go with queries and stored procedures.

Thanks!

Mahesh

Data migration from Access to SQL server 2005

Hi,

I am trying to migrate the data from the Access database to Microsoft SQL server on my machine using some scripts. I have enabled the OPENROWSET and OPENDATASOURCE support through the surface area configuration. The script that accesses the excel file works fine but when i run the script that trys to open the .mdb file it gives me following error.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.".

Msg 7303, Level 16, State 1, Line 35

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Any help would be greatly appreciated. Thanks.

-padu

Make sure that the startup account for sqlserver is not a service account (i.e. localsystem/service/etc.). Just use a domain acct.|||

Hi oj,

How do you check the startup account is not a service account? I am pretty new to SQL server.

Thanks.

-padu

|||You can run SQL Server Configuration Manager. It allows you to change service account there (if desired).

Data Migration Error - SQL TYPE Variant data

I am trying to migrate our Portals database from SQL2000 to SQL2005, but I received "SQL Type Variant Data" error during the data migration with some database. Can anyone help me with this?

Thanks,

Jay

If you explain how you are trying to do the migration and what the error message is then maybe someone will be able to help.

-Jamie

Data Migration

I have 2 Tables

Table 1 has

Id

Type

Table 2 has

Id

Type

PartNumber

I need to migrate data from Table 1 to Table 2 based on Type.

Type can be 1 or 2.

Example of Source.

Table 1 has

Id Type

1 1

2 1

3 1

3 2

Table 2 should have 2 records for each Id and based on the Type have different Part Numbers. Part numbers will be the same if in the Source table I had just one record with type 1 and if I had 2 records with type 1 and 2 - Part number will be different.

Example of Destination.

Id Type PartNumber

1 1 10

1 2 10

2 1 11

2 2 11

3 1 12

3 2 13

================================

As you see above

1. I need to duplicate records which had just one type in the Source table.

1 1 10

1 2 10

I will have 2 (1 and 2) types now, but everything else the same for the same id.

In my case I will have Part Numbers as guid. I would create them on my own. They will be the same here.

2. I need to have 2 records as it was before in Source table for id's with 2 types, but with different Part Numbers.

3 1 12

3 2 13

In my case I will have Part Numbers as guid. I would create them on my own.They will be different here.

How do I create 2 records for each Id and certain rules for them? How do I find if there is one or there are two records in Source and how do I apply my rules in looping through records? What control in SSIS tool can I use?

Thanks.

This looks like a job for T-SQL, not SSIS. If you can elaborate on the "Table 2 should have 2 records for each Id and based on the Type have different Part Numbers. Part numbers will be the same if in the Source table I had just one record with type 1 and if I had 2 records with type 1 and 2 - Part number will be different" statement, I may be able to help more. The logic you describe here isn't precise enough to implement (or perhaps I'm just not following it correctly) but this looks like a simple INSERT .. SELECT to me.|||

Can you give me more details? I think you understand correctly.

Look at my examples. It describes what I need.

Table 2 should have 2 records for each Id. Part Number will be the same if Source has just one type = 1. Part Number will be different if the Source has type 1 and 2.

In my case Part Number will be guid (uniqueidentifier) which I need to create on the fly. If Part Number will be the same for 2 records - I would need to use the same guid.

Please let me know if you have more questions.

|||

Vita wrote:

Can you give me more details? I think you understand correctly.

Look at my examples. It describes what I need.

Table 2 should have 2 records for each Id. Part Number will be the same if Source has just one type = 1. Part Number will be different if the Source has type 1 and 2.

In my case Part Number will be guid (uniqueidentifier) which I need to create on the fly. If Part Number will be the same for 2 records - I would need to use the same guid.

Please let me know if you have more questions.

"Part Number will be different" is what was unclear to me. Different in what way? In each case, what should it be?

I think your most recent post provides the needed clarity - let me play with it for a while...

|||

Ok... that was more annoying than I figured - it was the NEWID() function that was giving me trouble, so I needed to use a temp table to get the desired results. Here's the deal:

Code Snippet

USE tempdb

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U'))

DROP TABLE [dbo].[Table1]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table2]') AND type in (N'U'))

DROP TABLE [dbo].[Table2]

GO

CREATE TABLE Table1

(

Id INT NOT NULL,

Type INT NOT NULL

)

go

CREATE TABLE Table2

(

Id INT NOT NULL,

Type INT NOT NULL,

PartNumber UNIQUEIDENTIFIER NULL

)

GO

INSERT INTO Table1 VALUES (1, 1)

INSERT INTO Table1 VALUES (2, 1)

INSERT INTO Table1 VALUES (3, 1)

INSERT INTO Table1 VALUES (3, 2)

GO

WITH IdExistsOnceCTE

AS

(

SELECT Id

,MIN (Type) AS Type

,NEWID() AS PartNumber

FROM Table1

GROUP BY Id

HAVING COUNT (Type) = 1

)

SELECT IdExistsOnceCTE.*

INTO #ExistsOnce -- This will fail if run more than once in a row, as the temp table will already exist on subsequent executions!

FROM IdExistsOnceCTE;

WITH IdExistsTwiceCTE

AS

(

SELECT Id

,Type

,NEWID() AS PartNumber

FROM Table1

WHERE Id NOT IN ( SELECT Id FROM #ExistsOnce )

),

DummyDuplicationCTE

AS

(

SELECT 1 AS Dup

UNION ALL

SELECT 2

)

INSERT INTO Table2

SELECT #ExistsOnce.*

FROM #ExistsOnce

CROSS JOIN DummyDuplicationCTE

UNION ALL

SELECT *

FROM IdExistsTwiceCTE

ORDER BY 1, 2;

SELECT *

FROM Table2;

The final SELECT statement is a verification of the contents of Table2, and based on the inputs you provided, produces this output:

Code Snippet

Id Type PartNumber

-- --

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

3 1 38070FA6-0805-45C3-8503-58F8A9EDF473

3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB

Does this give you what you need?

|||

Thank you for your hard work.

The only thing I see wrong is the type.

You got

Id Type PartNumber

-- --

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

3 1 38070FA6-0805-45C3-8503-58F8A9EDF473

3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB

and I need

Id Type PartNumber

-- --

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

1 2 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

2 2 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

3 1 38070FA6-0805-45C3-8503-58F8A9EDF473

3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB

Type always needs to be 1 and 2.

Everything else looks great.

|||

Here you go:

Code Snippet

WITH IdExistsOnceCTE

AS

(

SELECT Id

,MIN (Type) AS Type

,NEWID() AS PartNumber

FROM Table1

GROUP BY Id

HAVING COUNT (Type) = 1

)

SELECT IdExistsOnceCTE.*

INTO #ExistsOnce -- This will fail if run more than once in a row, as the temp table will already exist on subsequent executions!

FROM IdExistsOnceCTE;

WITH IdExistsTwiceCTE

AS

(

SELECT Id

,Type

,NEWID() AS PartNumber

FROM Table1

WHERE Id NOT IN ( SELECT Id FROM #ExistsOnce )

),

DummyDuplicationCTE

AS

(

SELECT 1 AS Dup

UNION ALL

SELECT 2

)

INSERT INTO Table2

SELECT Id

,Dup

,PartNumber

FROM #ExistsOnce

CROSS JOIN DummyDuplicationCTE

UNION ALL

SELECT *

FROM IdExistsTwiceCTE

ORDER BY 1, 2;

SELECT *

FROM Table2;

This yields:

Code Snippet

Id Type PartNumber

-- --

1 1 7578AB32-6F38-4D23-887B-D81DD9D71DBC

1 2 7578AB32-6F38-4D23-887B-D81DD9D71DBC

2 1 F554FFA0-B0DB-4C53-9D3F-181678617A9B

2 2 F554FFA0-B0DB-4C53-9D3F-181678617A9B

3 1 165DEB39-D6DA-4DD3-B577-DD295BAF7455

3 2 55389A91-EAF6-4360-BFAA-10032EA1AB67

|||Thank you very much!

Data from Access to SQL 2005

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.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 s
eed 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@.TK2MS
FTNGP03.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 SQ
L.
>
> 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.|||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 SQ
L.
> 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:[vbcol=seagreen]
> 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...
> to SQL.
> value?

Data from Access to SQL 2005

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.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" 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--|||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.