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