Data tarsfer from one table to another with different schema on sqlserver 2000

Hi all
I am working on SqlServer 2000,
When row will be inserted/updated in one table that data neds to be
inserted/updated into the another DataBase on the same machine. Both
table has different schema, what is the Better solution? I know we can
do it with Trigger, But Is there any better way to do it?
Thanks
ABCL
> do it with Trigger, But Is there any better way to do it?
How do you insert/update the data ? By using SP? If so insert/update into
another table within the same SP.
I hope you don't have permmisions problems when you update/insert the
data into differnt schem/user owner object
"ABCL" <ohmp05@.hotmail.com> wrote in message
news:64f2096c-1e83-4e6a-8cec-2f0f818284bd@.n77g2000hse.googlegroups.com...
> Hi all
> I am working on SqlServer 2000,
> When row will be inserted/updated in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
|||On Mar 16, 8:01Xam, ABCL <ohm...@.hotmail.com> wrote:
> Hi all
> X I am working on SqlServer 2000,
> XWhen row will be inserted/updated Xin one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
Actually we want to SynchroniZe the Data from one DataBase to another
and vise versa on Insert/update and delete.
How Can we Perform Data Synchronization on both the DataBase with
different schema.
If I write trigger, I think There will be endless loop If both Tables
hava trigger on inster/update and Delete.
Do u have any solution for that? I am new to perform this kind of task
Is it possible with DTS( scheduled job)?
Is there any Performance Issue?
sql

Data tarsfer from one table to another with different schema on sql

Hi all
I am working on SqlServer 2000,
When row will be inserted/updated in one table that data neds to be
inserted/updated into the another DataBase on the same machine. Both
table has different schema, what is the Better solution? I know we can
do it with Trigger, But Is there any better way to do it?
ThanksABCL
> do it with Trigger, But Is there any better way to do it?
How do you insert/update the data ? By using SP? If so insert/update into
another table within the same SP.
I hope you don't have permmisions problems when you update/insert the
data into differnt schem/user owner object
"ABCL" <ohmp05@.hotmail.com> wrote in message
news:64f2096c-1e83-4e6a-8cec-2f0f818284bd@.n77g2000hse.googlegroups.com...
> Hi all
> I am working on SqlServer 2000,
> When row will be inserted/updated in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks|||On Mar 16, 8:01=A0am, ABCL <ohm...@.hotmail.com> wrote:
> Hi all
> =A0 I am working on SqlServer 2000,
> =A0When row will be inserted/updated =A0in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
Actually we want to SynchroniZe the Data from one DataBase to another
and vise versa on Insert/update and delete.
How Can we Perform Data Synchronization on both the DataBase with
different schema.
If I write trigger, I think There will be endless loop If both Tables
hava trigger on inster/update and Delete.
Do u have any solution for that? I am new to perform this kind of task
Is it possible with DTS( scheduled job)?
Is there any Performance Issue?

Data schema for storing arbitrary order

Currently this is purely hypothetical, though I'm semi-considering something
like this for part of a current project. I'm wondering how to apply a
pseudo-order to a set of data.
To give an example, if you were storing Service Ranks, which were
referenced by the service personnel you stored in the database :-
(... indicates other ranks in-between)
Rear Admiral
...
Admiral
...
Captain
...
Ensign
You store the data as a 2 column table, RankID (unique system-generated eg
Identity(1,1)) and RankName (varchar(50) perhaps).
So you now want to produce a report, that lists the personal in order from
highest rank to lowest rank. As far as the software user is concerned, if a
rank was missing, they would just enter it, tell the system what rank it was
below/above, and it would be stored. But how best to store the list in the
database, given that a new item could be inserted anywhere in the list? You
can't sort alphabetically, because the ranks aren't alphabetically ordered.
You can't trust an auto-generated ID, because that would put most recent
additions at the bottom, earliest at the top.
My current thought is to add a new column to the items - RankPos (integer) -
with an arbitrary (x) between the items (so first rank would be 10, fourth
rank down would be 40, the rank below it would be 50 etc)... if a rank is
inserted between the two, I'd give the new rank 45, and then run an update
once in a while (or if the numerical gap was used up) that would re-number
this column so there was an gap (x) of 10 again. But, is there a better way?
As I said, just wondering at the moment, but not the first time. I seem to
be stuck in the record groove, coming back to the above way each time I
think about it.
Yours,
Ann-Mariethat is one way....to add a "SortOrder" or "SortSequence" column
the other way is to implement this as a linked list or dual linked list.
Where each individual record keeps track of who is above and who is below
them.
just food for thought.
Greg Jackson
PDX, Oregon|||"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e7VFu98LFHA.2796@.tk2msftngp13.phx.gbl...
> that is one way....to add a "SortOrder" or "SortSequence" column
> the other way is to implement this as a linked list or dual linked list.
> Where each individual record keeps track of who is above and who is below
> them.
> just food for thought.
> Greg Jackson
> PDX, Oregon
>
From the software development background, yes, I would agree. But when it
comes to SQL, but wouldn't I then have a problem doing a simple query to get
the personnel returned 'ORDER BY {rank}'. Echoes of the adjacency tree
storage vs. nested-set tree storage problems and discussion come to mind.
That said, thanks for your reply,
Ann-Marie

Data Schema Comparison and Migration Tools

I've been testing a variety of database schema migration tools. Our
company purchased Embarcadero Change Manager a while ago but we've
been less than satisfied with the results.

We are looking for a tool that can compare a source/developer database
with a target/client database and then make the necessary changes.
The tool will need to update stored procedures, tables, indexes,
constraints, etc. The tool will also need to make the changes in the
correct order based on dependencies and relationships.

Anyone have any recommendations?snow@.steton.com (Roger Corman) wrote in message news:<4625da9f.0401230727.521295c7@.posting.google.com>...
> I've been testing a variety of database schema migration tools. Our
> company purchased Embarcadero Change Manager a while ago but we've
> been less than satisfied with the results.
> We are looking for a tool that can compare a source/developer database
> with a target/client database and then make the necessary changes.
> The tool will need to update stored procedures, tables, indexes,
> constraints, etc. The tool will also need to make the changes in the
> correct order based on dependencies and relationships.
> Anyone have any recommendations?

http://www.red-gate.com/ seems to be the best. We have tried a fewsql

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.