Re: Importing from another database on same server using SQL Server Express

Try one of these two choices, one using a JOIN, one using a Sub-SELECT:

UPDATE n
SET n.Field = o.Field
FROM NewDatabase.dbo.Table n
JOIN OldDatabase.dbo.Table o
ON n.PK = o.PK

(OR)

UPDATE NewDatabase.dbo.Table n
SET n.Field = (SELECT o.FIELD
FROM OldDatabase.dbo.Table o
WHERE o.Field = n.Field
)

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf


"John Hackert" <hackertjohnb@xxxxxxxxx> wrote in message news:1162174049.649530.85940@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I haven't been able to solve this likely syntax problem despite
referring to these groups, the Books On Line, and reference texts:

Without the use of Data Transformation Services in SQL Server Express,
what techniques work to import/export data between databases under the
same server instance?

The specific example I was working on involved an attempt to run an
update query to place data from a specific field in an older copy of an
otherwise identical database into a more recent copy. Ultimately I
just plugged the small data series in manually using side-by-side views
because I could not overcome the syntax errors generated in the effort
to refer to the "external" database.

This is the setup in question:
- An instance in the format of "MyComputerName\SQLExpress," and both an
- "OldDatabase" and
- "NewDatabase" attached and viewable from the Management Studio
Express, with identical fields

I tried a few different query techniques as suggested in historical
posts, but to no avail. Here is such an example that I tried:

In MSE I right clicked on the target table in question and chose:
"Script table as," then
"UPDATE to," then
"New Query Editor Window"

I removed all the fields except that in question and added this Where
clause:
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = [OldDatabase].[dbo].[Table].[Field]
WHERE [NewDatabase].[dbo].[Table].[PK] =
[OldDatabase].[dbo].[Table].[PK]

in which the primary key (an auto-increment integer) is identical
between the new and old tables.

This produces the error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
bound.

If I understood from prior posts, using joins is less desirable in
update queries, but even so likewise produced errors when I tried that
approach.

I would be grateful if someone could explain the proper syntax to refer
to another database within the same server instance or otherwise.

Many thanks!

Relevant Pages

  • Importing from another database on same server using SQL Server Express
    ... I haven't been able to solve this likely syntax problem despite ... otherwise identical database into a more recent copy. ... I tried a few different query techniques as suggested in historical ... The multi-part identifier "OldDatabase.dbo.Table.PK" could not be ...
    (microsoft.public.sqlserver.msde)
  • Re: Session Variables (again!)
    ... Identifier is an AutoNumber field. ... Server error: Unable to retrieve schema information from the query: ... The following error message comes from the database driver software; ...
    (microsoft.public.frontpage.client)
  • RE: SQL response query
    ... To check if the server instance is alive, ... each database (after filtering out the ones that are known to be offline -- ... Have the opportunity to query all datbase servers via an agent to determine ...
    (microsoft.public.sqlserver.server)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)