[dba-SQLServer] Update Data From Same Table In Different Database

paul.hartland at fsmail.net paul.hartland at fsmail.net
Thu Jul 22 04:51:45 CDT 2004


Thanks for help, I tried the following stored procedure:
CREATE PROCEDURE [genesis_update_PersonnelRightToWork]
AS 
SET NOCOUNT ON
UPDATE [GenesisTest].[dbo].[tblPersonnel] 
SET   
  [RightToWorkID] =  [Genesis].[dbo].[tblPersonnel].[RightToWorkID]
WHERE 
 (  [PayrollNo]  =  [Genesis].[dbo].[tblPersonnel].[PayrollNo] )
SET NOCOUNT OFF
Something like the column prefix [Genesis].[dbo].[tblPersonnel] does not match with a table name or alias
Any help anyone ?
Thanks in advance
Paul Hartland





Message date : Jul 21 2004, 04:42 PM
>From : "Mackin, Christopher" 
To : dba-sqlserver at databaseadvisors.com
Copy to : 
Subject : RE: [dba-SQLServer] Update Data From Same Table In Different Database
To reference any SQL table the syntax is ServerName.DatabaseName.CreatedByName.TableName

The majority of times you'll see just the last one or two, so dbo.tblPersonnel, but to reference a table in another database you just use:

GenesisTest.dbo.tblPersonnel

So you scan set up a stright update query using the two tables:
GenesisTest.dbo.tblPersonnel and Genesis.dbo.tblPersonnel

-Chris Mackin

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
paul.hartland at fsmail.net
Sent: Wednesday, July 21, 2004 9:35 AM
To: dba-sqlserver
Subject: [dba-SQLServer] Update Data From Same Table In Different
Database


To all,

I have two database's in SQL Server 7.0, Genesis and GenesisTest. Genesis contains all tables that have been directly exported from a live Access database, and GenesisTest contains some of the tables from Genesis which I am modifying (field names, lengths, datatypes etc). I have a table called tblPersonnel in both SQL databases, what I want is a query to link the two tables by PayrollNo and update data in certain fields in the GenesisTest database. 

Whats the best way to do this, I am such a novice at SQL that I first thought about linking them both into an Access database and doing the query in there, but I'm sure there must be a better way.

Thanks for any help in advance.

Paul Hartland

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at: http://www.wanadoo.co.uk/help/id/7098.htm
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at: http://www.wanadoo.co.uk/help/id/7098.htm


More information about the dba-SQLServer mailing list