|
 |
asp_web_howto thread: Help with a query
Message #1 by "O'Hara, Elliott M" <EMOHARA@k...> on Mon, 19 Nov 2001 06:59:28 -0500
|
|
OK... I know there has to be a way to do this..
But its early and I can't think of it...
I've got 2 separate Employee Databases and I only want one. I've altered the
tables on the one I want to keep to contain the columns that the db to be
discarded has.
now... I want to make a update query that first pulls the BEMSID column from
DB 1 (gunna die) and inserts it into the charBEMSID column in the DB 2 (not
gunna die) WHERE the DB1.Lastname = DB2.Lastname and
DB1.FirstName=DB2.FirstName
something like
UPDATE tblEmployees
SET charBEMSID = (SELECT Server.Database.dbo.table.BEMSID FROM
Server.Database.dbo.table WHERE LastName = I AM LOST HERE)
WHERE LastName = LOST HERE TOO
any ideas???
Does that make any since?
Thanks,
Elliott
Message #2 by "Serge Wagemakers" <swagemakers@d...> on Mon, 19 Nov 2001 13:54:57 +0100
|
|
Depends on the database server you're using to figure out the query.
The way to go is to use a distributed query to get info from a table at db1
and
insert it into a table in db2.
In informix it's something like:
SELECT O.order_num, C.fname, C.lname
FROM masterdb@c...:customer C, sales@b...:orders O
WHERE C.customer_num = O.Customer_num
In Access I believe you have to have MS Transaction Server for using
distributed
queries.
HTH,
Serge
----- Original Message -----
From: "O'Hara, Elliott M" <EMOHARA@k...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Monday, November 19, 2001 12:59 PM
Subject: [asp_web_howto] Help with a query
> OK... I know there has to be a way to do this..
> But its early and I can't think of it...
>
> I've got 2 separate Employee Databases and I only want one. I've altered
the
> tables on the one I want to keep to contain the columns that the db to be
> discarded has.
>
> now... I want to make a update query that first pulls the BEMSID column
from
> DB 1 (gunna die) and inserts it into the charBEMSID column in the DB 2
(not
> gunna die) WHERE the DB1.Lastname = DB2.Lastname and
> DB1.FirstName=DB2.FirstName
>
> something like
>
> UPDATE tblEmployees
> SET charBEMSID = (SELECT Server.Database.dbo.table.BEMSID FROM
> Server.Database.dbo.table WHERE LastName = I AM LOST HERE)
> WHERE LastName = LOST HERE TOO
>
> any ideas???
>
> Does that make any since?
>
> Thanks,
> Elliott
>
$subst('Email.Unsub')
>
Message #3 by "Michiel van Otegem \(lists ONLY\)" <michiel_lists@a...> on Mon, 19 Nov 2001 15:29:11 +0100
|
|
In Access you can't do distributed queries, unless you have a link in DBx to
the tables in DBy. Transaction server won't help one bit, because it enlists
the Distributed Transaction Coordinator for database stuff... only one tiny
little problem... DTC can't talk properly to Access.
---
Michiel van Otegem
ASPNL.com - ASP/ASP.NET/XML Teacher
ASPFriends.com - Moderator
http://www.aspnl.com
http://www.aspalliance.com/michiel
off-list email: michiel@a...
---
----- Original Message -----
From: "Serge Wagemakers" <swagemakers@d...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Monday, November 19, 2001 1:54 PM
Subject: [asp_web_howto] Re: Help with a query
> Depends on the database server you're using to figure out the query.
> The way to go is to use a distributed query to get info from a table at
db1
> and
> insert it into a table in db2.
>
> In informix it's something like:
>
> SELECT O.order_num, C.fname, C.lname
> FROM masterdb@c...:customer C, sales@b...:orders O
> WHERE C.customer_num = O.Customer_num
>
> In Access I believe you have to have MS Transaction Server for using
> distributed
> queries.
>
> HTH,
>
> Serge
> ----- Original Message -----
> From: "O'Hara, Elliott M" <EMOHARA@k...>
> To: "ASP Web HowTo" <asp_web_howto@p...>
> Sent: Monday, November 19, 2001 12:59 PM
> Subject: [asp_web_howto] Help with a query
>
>
> > OK... I know there has to be a way to do this..
> > But its early and I can't think of it...
> >
> > I've got 2 separate Employee Databases and I only want one. I've altered
> the
> > tables on the one I want to keep to contain the columns that the db to
be
> > discarded has.
> >
> > now... I want to make a update query that first pulls the BEMSID column
> from
> > DB 1 (gunna die) and inserts it into the charBEMSID column in the DB 2
> (not
> > gunna die) WHERE the DB1.Lastname = DB2.Lastname and
> > DB1.FirstName=DB2.FirstName
> >
> > something like
> >
> > UPDATE tblEmployees
> > SET charBEMSID = (SELECT Server.Database.dbo.table.BEMSID FROM
> > Server.Database.dbo.table WHERE LastName = I AM LOST HERE)
> > WHERE LastName = LOST HERE TOO
> >
> > any ideas???
> >
> > Does that make any since?
> >
> > Thanks,
> > Elliott
> >
> $subst('Email.Unsub')
> >
>
>
$subst('Email.Unsub')
>
>
Message #4 by "Serge Wagemakers" <swagemakers@d...> on Mon, 19 Nov 2001 15:55:12 +0100
|
|
Thanks for correcting me... After I posted it, it occured to me as well
Access is not the
server to do distributed queries with... Sorry about that!
----- Original Message -----
From: "Michiel van Otegem (lists ONLY)" <michiel_lists@a...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Monday, November 19, 2001 3:29 PM
Subject: [asp_web_howto] Re: Help with a query
> In Access you can't do distributed queries, unless you have a link in DBx
to
> the tables in DBy. Transaction server won't help one bit, because it
enlists
> the Distributed Transaction Coordinator for database stuff... only one
tiny
> little problem... DTC can't talk properly to Access.
>
> ---
> Michiel van Otegem
> ASPNL.com - ASP/ASP.NET/XML Teacher
> ASPFriends.com - Moderator
>
> http://www.aspnl.com
> http://www.aspalliance.com/michiel
> off-list email: michiel@a...
> ---
> ----- Original Message -----
> From: "Serge Wagemakers" <swagemakers@d...>
> To: "ASP Web HowTo" <asp_web_howto@p...>
> Sent: Monday, November 19, 2001 1:54 PM
> Subject: [asp_web_howto] Re: Help with a query
>
>
> > Depends on the database server you're using to figure out the query.
> > The way to go is to use a distributed query to get info from a table at
> db1
> > and
> > insert it into a table in db2.
> >
> > In informix it's something like:
> >
> > SELECT O.order_num, C.fname, C.lname
> > FROM masterdb@c...:customer C, sales@b...:orders O
> > WHERE C.customer_num = O.Customer_num
> >
> > In Access I believe you have to have MS Transaction Server for using
> > distributed
> > queries.
> >
> > HTH,
> >
> > Serge
> > ----- Original Message -----
> > From: "O'Hara, Elliott M" <EMOHARA@k...>
> > To: "ASP Web HowTo" <asp_web_howto@p...>
> > Sent: Monday, November 19, 2001 12:59 PM
> > Subject: [asp_web_howto] Help with a query
> >
> >
> > > OK... I know there has to be a way to do this..
> > > But its early and I can't think of it...
> > >
> > > I've got 2 separate Employee Databases and I only want one. I've
altered
> > the
> > > tables on the one I want to keep to contain the columns that the db to
> be
> > > discarded has.
> > >
> > > now... I want to make a update query that first pulls the BEMSID
column
> > from
> > > DB 1 (gunna die) and inserts it into the charBEMSID column in the DB 2
> > (not
> > > gunna die) WHERE the DB1.Lastname = DB2.Lastname and
> > > DB1.FirstName=DB2.FirstName
> > >
> > > something like
> > >
> > > UPDATE tblEmployees
> > > SET charBEMSID = (SELECT Server.Database.dbo.table.BEMSID FROM
> > > Server.Database.dbo.table WHERE LastName = I AM LOST HERE)
> > > WHERE LastName = LOST HERE TOO
> > >
> > > any ideas???
> > >
> > > Does that make any since?
> > >
> > > Thanks,
> > > Elliott
> > >
> > $subst('Email.Unsub')
> > >
> >
> >
michiel_lists@a...
> $subst('Email.Unsub')
> >
> >
>
>
$subst('Email.Unsub')
>
Message #5 by "O'Hara, Elliott M" <EMOHARA@k...> on Mon, 19 Nov 2001 10:10:14 -0500
|
|
Its SQL 7.0
-----Original Message-----
From: Michiel van Otegem (lists ONLY) [mailto:michiel_lists@a...]
Sent: Monday, November 19, 2001 9:29 AM
To: ASP Web HowTo
Subject: [asp_web_howto] Re: Help with a query
In Access you can't do distributed queries, unless you have a link in DBx to
the tables in DBy. Transaction server won't help one bit, because it enlists
the Distributed Transaction Coordinator for database stuff... only one tiny
little problem... DTC can't talk properly to Access.
---
Michiel van Otegem
ASPNL.com - ASP/ASP.NET/XML Teacher
ASPFriends.com - Moderator
http://www.aspnl.com
http://www.aspalliance.com/michiel
off-list email: michiel@a...
---
----- Original Message -----
From: "Serge Wagemakers" <swagemakers@d...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Monday, November 19, 2001 1:54 PM
Subject: [asp_web_howto] Re: Help with a query
> Depends on the database server you're using to figure out the query.
> The way to go is to use a distributed query to get info from a table at
db1
> and
> insert it into a table in db2.
>
> In informix it's something like:
>
> SELECT O.order_num, C.fname, C.lname
> FROM masterdb@c...:customer C, sales@b...:orders O
> WHERE C.customer_num = O.Customer_num
>
> In Access I believe you have to have MS Transaction Server for using
> distributed
> queries.
>
> HTH,
>
> Serge
> ----- Original Message -----
> From: "O'Hara, Elliott M" <EMOHARA@k...>
> To: "ASP Web HowTo" <asp_web_howto@p...>
> Sent: Monday, November 19, 2001 12:59 PM
> Subject: [asp_web_howto] Help with a query
>
>
> > OK... I know there has to be a way to do this..
> > But its early and I can't think of it...
> >
> > I've got 2 separate Employee Databases and I only want one. I've altered
> the
> > tables on the one I want to keep to contain the columns that the db to
be
> > discarded has.
> >
> > now... I want to make a update query that first pulls the BEMSID column
> from
> > DB 1 (gunna die) and inserts it into the charBEMSID column in the DB 2
> (not
> > gunna die) WHERE the DB1.Lastname = DB2.Lastname and
> > DB1.FirstName=DB2.FirstName
> >
> > something like
> >
> > UPDATE tblEmployees
> > SET charBEMSID = (SELECT Server.Database.dbo.table.BEMSID FROM
> > Server.Database.dbo.table WHERE LastName = I AM LOST HERE)
> > WHERE LastName = LOST HERE TOO
> >
> > any ideas???
> >
> > Does that make any since?
> >
> > Thanks,
> > Elliott
> >
> $subst('Email.Unsub')
> >
>
>
$subst('Email.Unsub')
>
>
emohara@k...
$subst('Email.Unsub')
Message #6 by "Serge Wagemakers" <swagemakers@d...> on Mon, 19 Nov 2001 16:35:22 +0100
|
|
A helpful article would be:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4829
Another very detailed and helpful article is:
http://www.elementkjournals.com/sql/0004/sql0041.htm
HTH,
Serge
----- Original Message -----
From: "O'Hara, Elliott M" <EMOHARA@k...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Monday, November 19, 2001 4:10 PM
Subject: [asp_web_howto] Re: Help with a query
> Its SQL 7.0
>
> -----Original Message-----
> From: Michiel van Otegem (lists ONLY) [mailto:michiel_lists@a...]
> Sent: Monday, November 19, 2001 9:29 AM
> To: ASP Web HowTo
> Subject: [asp_web_howto] Re: Help with a query
>
>
> In Access you can't do distributed queries, unless you have a link in DBx
to
> the tables in DBy. Transaction server won't help one bit, because it
enlists
> the Distributed Transaction Coordinator for database stuff... only one
tiny
> little problem... DTC can't talk properly to Access.
>
> ---
> Michiel van Otegem
> ASPNL.com - ASP/ASP.NET/XML Teacher
> ASPFriends.com - Moderator
>
> http://www.aspnl.com
> http://www.aspalliance.com/michiel
> off-list email: michiel@a...
> ---
> ----- Original Message -----
> From: "Serge Wagemakers" <swagemakers@d...>
> To: "ASP Web HowTo" <asp_web_howto@p...>
> Sent: Monday, November 19, 2001 1:54 PM
> Subject: [asp_web_howto] Re: Help with a query
>
>
> > Depends on the database server you're using to figure out the query.
> > The way to go is to use a distributed query to get info from a table at
> db1
> > and
> > insert it into a table in db2.
> >
> > In informix it's something like:
> >
> > SELECT O.order_num, C.fname, C.lname
> > FROM masterdb@c...:customer C, sales@b...:orders O
> > WHERE C.customer_num = O.Customer_num
> >
> > In Access I believe you have to have MS Transaction Server for using
> > distributed
> > queries.
> >
> > HTH,
> >
> > Serge
> > ----- Original Message -----
> > From: "O'Hara, Elliott M" <EMOHARA@k...>
> > To: "ASP Web HowTo" <asp_web_howto@p...>
> > Sent: Monday, November 19, 2001 12:59 PM
> > Subject: [asp_web_howto] Help with a query
> >
> >
> > > OK... I know there has to be a way to do this..
> > > But its early and I can't think of it...
> > >
> > > I've got 2 separate Employee Databases and I only want one. I've
altered
> > the
> > > tables on the one I want to keep to contain the columns that the db to
> be
> > > discarded has.
> > >
> > > now... I want to make a update query that first pulls the BEMSID
column
> > from
> > > DB 1 (gunna die) and inserts it into the charBEMSID column in the DB 2
> > (not
> > > gunna die) WHERE the DB1.Lastname = DB2.Lastname and
> > > DB1.FirstName=DB2.FirstName
> > >
> > > something like
> > >
> > > UPDATE tblEmployees
> > > SET charBEMSID = (SELECT Server.Database.dbo.table.BEMSID FROM
> > > Server.Database.dbo.table WHERE LastName = I AM LOST HERE)
> > > WHERE LastName = LOST HERE TOO
> > >
> > > any ideas???
> > >
> > > Does that make any since?
> > >
> > > Thanks,
> > > Elliott
> > >
> > $subst('Email.Unsub')
> > >
> >
> >
michiel_lists@a...
> $subst('Email.Unsub')
> >
> >
>
>
> emohara@k...
> $subst('Email.Unsub')
>
$subst('Email.Unsub')
>
|
|
|
View Cart
