Importing within SQL Server Express without Data Transformation Services

I'm sorry to repost this from microsoft.public.sqlserver.msde where I
had recently submitted this, if anyone would have noted, but I still
can't resolve the seemingly simple task of importing from one database
to another within even the same instance of SQL Server Express. I
would be so grateful if someone could help me:
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.
The same error occurs with different techniques (joins, subqueries,
etc.)John Hackert wrote:
> I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> had recently submitted this, if anyone would have noted, but I still
> can't resolve the seemingly simple task of importing from one database
> to another within even the same instance of SQL Server Express. I
> would be so grateful if someone could help me:
> 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.
> The same error occurs with different techniques (joins, subqueries,
> etc.)
try this
USE NewDatabase
UPDATE [NewDatabase].[dbo].[Table]
SET [Field] = Old.[Field]
from [NewDatabase].[dbo].[Table] New inner join
[OldDatabase].[dbo].[Table] Old
on
New.[PK] =Old.[PK]
Regards
Amish Shah|||Amish,
Thank you so much for your help. I set up two test databases,
"TestNew" and "TestOld" with parallel tables and test data. I found
that the syntax you proposed worked:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
[TestOld].[dbo].[tblProcedure] TestOld
on
TestNew.[procedureID]=TestOld.[procedureID]
Whereas syntax such as this did not work:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure]
WHERE [Testnew].[dbo].[tblProcedure].procedureID
=[TestOld].[dbo].[tblProcedure].procedureID
The following error was generated:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
not be bound.
SQL Server is relatively new to me, and the syntax/manipulations often
seem cryptic. May I ask you to help me understand the asterisked parts
of the construction:
FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
[TestOld].[dbo].[tblProcedure] *TestOld*
on
TestNew.[procedureID]=TestOld.[procedureID]
Thanks,
John
amish wrote:
> John Hackert wrote:
> > I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> > had recently submitted this, if anyone would have noted, but I still
> > can't resolve the seemingly simple task of importing from one database
> > to another within even the same instance of SQL Server Express. I
> > would be so grateful if someone could help me:
> >
> > 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.
> >
> > The same error occurs with different techniques (joins, subqueries,
> > etc.)
> try this
> USE NewDatabase
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = Old.[Field]
> from [NewDatabase].[dbo].[Table] New inner join
> [OldDatabase].[dbo].[Table] Old
> on
> New.[PK] => Old.[PK]
>
> Regards
> Amish Shah|||On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:
> Amish,
> Thank you so much for your help. I set up two test databases,
> "TestNew" and "TestOld" with parallel tables and test data. I found
> that the syntax you proposed worked:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
> [TestOld].[dbo].[tblProcedure] TestOld
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Whereas syntax such as this did not work:
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure]
> WHERE [Testnew].[dbo].[tblProcedure].procedureID
> =[TestOld].[dbo].[tblProcedure].procedureID
> The following error was generated:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
> not be bound.
> SQL Server is relatively new to me, and the syntax/manipulations often
> seem cryptic. May I ask you to help me understand the asterisked parts
> of the construction:
> FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
> [TestOld].[dbo].[tblProcedure] *TestOld*
> on
> TestNew.[procedureID]=TestOld.[procedureID]
> Thanks,
> John
> amish wrote:
> > John Hackert wrote:
> > > I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> > > had recently submitted this, if anyone would have noted, but I still
> > > can't resolve the seemingly simple task of importing from one database
> > > to another within even the same instance of SQL Server Express. I
> > > would be so grateful if someone could help me:
> > > 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.
> > > The same error occurs with different techniques (joins, subqueries,
> > > etc.)
> > try this
> > USE NewDatabase
> > UPDATE [NewDatabase].[dbo].[Table]
> > SET [Field] = Old.[Field]
> > from [NewDatabase].[dbo].[Table] New inner join
> > [OldDatabase].[dbo].[Table] Old
> > on
> > New.[PK] => > Old.[PK]
> > Regards
> > Amish Shah
John
Asterisk part is just an alias for the table.
Once you specify alias for the table in form clause of the query you
can refer it in other part of query instead of table name.
You have to add second table also in form clause.
You should change your query to
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] testnew,
[TestOld].[dbo].[tblProcedure] testold
WHERE [testnew].procedureID
=TestOld.procedureID
Regards
Amish Shah|||Amish,
I've used aliases in other contexts, but always with the keyword "AS"
- So the "AS" is an optional part of the syntax?
I see now from your example that the failure of the previous queries
related to not specifying the second table in the from clause.
Once again I'm grateful for your help
John
amish wrote:
> On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:
> > Amish,
> >
> > Thank you so much for your help. I set up two test databases,
> > "TestNew" and "TestOld" with parallel tables and test data. I found
> > that the syntax you proposed worked:
> >
> > UPDATE [TestNew].[dbo].[tblProcedure]
> > SET [CPT] = TestOld.[CPT]
> > FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
> > [TestOld].[dbo].[tblProcedure] TestOld
> > on
> > TestNew.[procedureID]=TestOld.[procedureID]
> >
> > Whereas syntax such as this did not work:
> >
> > UPDATE [TestNew].[dbo].[tblProcedure]
> > SET [CPT] = TestOld.[CPT]
> > FROM [TestNew].[dbo].[tblProcedure]
> > WHERE [Testnew].[dbo].[tblProcedure].procedureID
> > =[TestOld].[dbo].[tblProcedure].procedureID
> >
> > The following error was generated:
> > Msg 4104, Level 16, State 1, Line 1
> > The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
> > not be bound.
> >
> > SQL Server is relatively new to me, and the syntax/manipulations often
> > seem cryptic. May I ask you to help me understand the asterisked parts
> > of the construction:
> >
> > FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
> > [TestOld].[dbo].[tblProcedure] *TestOld*
> > on
> > TestNew.[procedureID]=TestOld.[procedureID]
> >
> > Thanks,
> >
> > John
> >
> > amish wrote:
> > > John Hackert wrote:
> >
> > > > I'm sorry to repost this from microsoft.public.sqlserver.msde where I
> > > > had recently submitted this, if anyone would have noted, but I still
> > > > can't resolve the seemingly simple task of importing from one database
> > > > to another within even the same instance of SQL Server Express. I
> > > > would be so grateful if someone could help me:
> >
> > > > 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.
> >
> > > > The same error occurs with different techniques (joins, subqueries,
> > > > etc.)
> >
> > > try this
> >
> > > USE NewDatabase
> >
> > > UPDATE [NewDatabase].[dbo].[Table]
> > > SET [Field] = Old.[Field]
> > > from [NewDatabase].[dbo].[Table] New inner join
> > > [OldDatabase].[dbo].[Table] Old
> > > on
> > > New.[PK] => > > Old.[PK]
> >
> > > Regards
> > > Amish Shah
> John
> Asterisk part is just an alias for the table.
> Once you specify alias for the table in form clause of the query you
> can refer it in other part of query instead of table name.
> You have to add second table also in form clause.
> You should change your query to
> UPDATE [TestNew].[dbo].[tblProcedure]
> SET [CPT] = TestOld.[CPT]
> FROM [TestNew].[dbo].[tblProcedure] testnew,
> [TestOld].[dbo].[tblProcedure] testold
> WHERE [testnew].procedureID
> =TestOld.procedureID
>
> Regards
> Amish Shah|||You're correct, the use of 'AS' when identifying an alias is indeed
optional.
--
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@.yahoo.com> wrote in message
news:1162861737.778956.88230@.b28g2000cwb.googlegroups.com...
> Amish,
> I've used aliases in other contexts, but always with the keyword "AS"
> - So the "AS" is an optional part of the syntax?
> I see now from your example that the failure of the previous queries
> related to not specifying the second table in the from clause.
> Once again I'm grateful for your help
> John
>
> amish wrote:
>> On Nov 5, 2:26 am, "John Hackert" <hackertjo...@.yahoo.com> wrote:
>> > Amish,
>> >
>> > Thank you so much for your help. I set up two test databases,
>> > "TestNew" and "TestOld" with parallel tables and test data. I found
>> > that the syntax you proposed worked:
>> >
>> > UPDATE [TestNew].[dbo].[tblProcedure]
>> > SET [CPT] = TestOld.[CPT]
>> > FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
>> > [TestOld].[dbo].[tblProcedure] TestOld
>> > on
>> > TestNew.[procedureID]=TestOld.[procedureID]
>> >
>> > Whereas syntax such as this did not work:
>> >
>> > UPDATE [TestNew].[dbo].[tblProcedure]
>> > SET [CPT] = TestOld.[CPT]
>> > FROM [TestNew].[dbo].[tblProcedure]
>> > WHERE [Testnew].[dbo].[tblProcedure].procedureID
>> > =[TestOld].[dbo].[tblProcedure].procedureID
>> >
>> > The following error was generated:
>> > Msg 4104, Level 16, State 1, Line 1
>> > The multi-part identifier "TestOld.dbo.tblProcedure.procedureID" could
>> > not be bound.
>> >
>> > SQL Server is relatively new to me, and the syntax/manipulations often
>> > seem cryptic. May I ask you to help me understand the asterisked parts
>> > of the construction:
>> >
>> > FROM [TestNew].[dbo].[tblProcedure] *TestNew* inner join
>> > [TestOld].[dbo].[tblProcedure] *TestOld*
>> > on
>> > TestNew.[procedureID]=TestOld.[procedureID]
>> >
>> > Thanks,
>> >
>> > John
>> >
>> > amish wrote:
>> > > John Hackert wrote:
>> >
>> > > > I'm sorry to repost this from microsoft.public.sqlserver.msde where
>> > > > I
>> > > > had recently submitted this, if anyone would have noted, but I
>> > > > still
>> > > > can't resolve the seemingly simple task of importing from one
>> > > > database
>> > > > to another within even the same instance of SQL Server Express. I
>> > > > would be so grateful if someone could help me:
>> >
>> > > > 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.
>> >
>> > > > The same error occurs with different techniques (joins, subqueries,
>> > > > etc.)
>> >
>> > > try this
>> >
>> > > USE NewDatabase
>> >
>> > > UPDATE [NewDatabase].[dbo].[Table]
>> > > SET [Field] = Old.[Field]
>> > > from [NewDatabase].[dbo].[Table] New inner join
>> > > [OldDatabase].[dbo].[Table] Old
>> > > on
>> > > New.[PK] =>> > > Old.[PK]
>> >
>> > > Regards
>> > > Amish Shah
>> John
>> Asterisk part is just an alias for the table.
>> Once you specify alias for the table in form clause of the query you
>> can refer it in other part of query instead of table name.
>> You have to add second table also in form clause.
>> You should change your query to
>> UPDATE [TestNew].[dbo].[tblProcedure]
>> SET [CPT] = TestOld.[CPT]
>> FROM [TestNew].[dbo].[tblProcedure] testnew,
>> [TestOld].[dbo].[tblProcedure] testold
>> WHERE [testnew].procedureID
>> =TestOld.procedureID
>>
>> Regards
>> Amish Shah
>