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:
>
> 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:[vbcol=seagreen]
> 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
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.googlegro ups.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:
>

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:
>
> 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:[vbcol=seagreen]
> 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
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:
> 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:
>sql

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
>

Importing to SQL Express using SSMSE

When I used Enterprise Manager it was very easy to import required tables from another server into my local MSDE server.

Now I am using SQL Express and SSMSE I cannot seem to find any other way of importing data other than creating the insert scripts manually which is a very painful and tediuos operation!

Can anyone advise if I have missed something and there is a way to import easily using SSMSE?

Thanks.......in hope

hi,

SSMSE does not provide the wizards included in SSIS or the like, so you have to do it "your way"...

this usually means BCP data in, use INSERT INTO scripts, connect to linked servers (ifa available) and INSERT..SELECT data in...

regards|||

Bums!

Oh well at least I know now.....

Thanks for the info ;-)

|||

I cannot seem to find any other way of importing data

You can import data (but not table definitions) using the DTSWizard. It is a simplied version of the Import/Export Wizard from Enterprise Manager.

It is located in: {installdirectory}\Microsoft SQL Server\90\DTS\Binn\DTSWizrd.exe

|||When I look for the DTS Wizard in the location you specify, it isn't there!!

Man, it's frustrating not to have it...
|||

Download from here:

DTSWizard.exe
http://go.microsoft.com/fwlink/?LinkId=65111

Importing to SQL Express using SSMSE

When I used Enterprise Manager it was very easy to import required tables from another server into my local MSDE server.

Now I am using SQL Express and SSMSE I cannot seem to find any other way of importing data other than creating the insert scripts manually which is a very painful and tediuos operation!

Can anyone advise if I have missed something and there is a way to import easily using SSMSE?

Thanks.......in hope

hi,

SSMSE does not provide the wizards included in SSIS or the like, so you have to do it "your way"...

this usually means BCP data in, use INSERT INTO scripts, connect to linked servers (ifa available) and INSERT..SELECT data in...

regards|||

Bums!

Oh well at least I know now.....

Thanks for the info ;-)

|||

I cannot seem to find any other way of importing data

You can import data (but not table definitions) using the DTSWizard. It is a simplied version of the Import/Export Wizard from Enterprise Manager.

It is located in: {installdirectory}\Microsoft SQL Server\90\DTS\Binn\DTSWizrd.exe

|||When I look for the DTS Wizard in the location you specify, it isn't there!!

Man, it's frustrating not to have it...
|||

Download from here:

DTSWizard.exe
http://go.microsoft.com/fwlink/?LinkId=65111

sql

Importing DBase tables into MSDE using ADO

What is the syntax for a SQL import from DBase IV into MSDE 2000 using ADO
in VB6
I am using ...
Set cn1 = New ADODB.Connection
cn1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog='Tramcars';Data Source=PM" (Note PM is
my SQL server name and Catalog tramcars exists)
sql = "Select * Into [base] from [dbase
IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
cn1.Execute sql
I get error message 'Invalid Object Name dbase
IV;DATABASE=c:\tramcars\Bakery\.base.dbf'
---
Using...
cn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
App.path & "\Data\Tramcars.mdb;Persist Security Info=False"
sql = "Select * Into [base] from [dbase
IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
cn1.Execute sql
works fine and imports DB table to Access 2000
Regards
steve
Try:
select * from OPENROWSET('MSDASQL',
'Driver={Microsoft dBase Driver};SourceDB=c:\tramcars\Bakery\;SourceType=db f',
'select * from base')
or better yet, create a linked server to dbase:
EXEC sp_addlinkedserver
'DBF',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\tramcars\Bakery\',
NULL,
'dBase IV'
exec sp_addlinkedsrvlogin
@.rmtsrvname = 'DBF',
@.useself = false,
@.locallogin = NULL,
@.rmtuser = NULL,
@.rmtpassword = NULL
select * from openquery(DBF,'select * from base')x
-oj
http://www.rac4sql.net
"steve" <sfrancis@.bigpond.net.au> wrote in message
news:OmzOkbmNEHA.3812@.TK2MSFTNGP12.phx.gbl...
> What is the syntax for a SQL import from DBase IV into MSDE 2000 using ADO
> in VB6
> I am using ...
> Set cn1 = New ADODB.Connection
> cn1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
> Security Info=False;Initial Catalog='Tramcars';Data Source=PM" (Note PM is
> my SQL server name and Catalog tramcars exists)
> sql = "Select * Into [base] from [dbase
> IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
> cn1.Execute sql
> I get error message 'Invalid Object Name dbase
> IV;DATABASE=c:\tramcars\Bakery\.base.dbf'
> Using...
> cn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> App.path & "\Data\Tramcars.mdb;Persist Security Info=False"
> sql = "Select * Into [base] from [dbase
> IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
> cn1.Execute sql
> works fine and imports DB table to Access 2000
> ----
> Regards
> steve
>
|||oj
Thanks
Worked a treat
Steve
"oj" <nospam_ojngo@.home.com> wrote in message
news:uTBcBx$NEHA.3016@.tk2msftngp13.phx.gbl...
> Try:
> select * from OPENROWSET('MSDASQL',
> 'Driver={Microsoft dBase
Driver};SourceDB=c:\tramcars\Bakery\;SourceType=db f',[vbcol=seagreen]
> 'select * from base')
> or better yet, create a linked server to dbase:
> EXEC sp_addlinkedserver
> 'DBF',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'c:\tramcars\Bakery\',
> NULL,
> 'dBase IV'
> exec sp_addlinkedsrvlogin
> @.rmtsrvname = 'DBF',
> @.useself = false,
> @.locallogin = NULL,
> @.rmtuser = NULL,
> @.rmtpassword = NULL
> select * from openquery(DBF,'select * from base')x
> --
> -oj
> http://www.rac4sql.net
>
> "steve" <sfrancis@.bigpond.net.au> wrote in message
> news:OmzOkbmNEHA.3812@.TK2MSFTNGP12.phx.gbl...
ADO[vbcol=seagreen]
Security=SSPI;Persist[vbcol=seagreen]
PM is
>

importing data to sqlserver express

I would like to import data to a sqlserver express database. The database I am interested in importing was created in MSDE. I tried backing it up and using the restore function in sqlserver express but with no success. I have not been able to find an import function in the Management Studio Express interface. Is it possible to import this information, or should I get busy with my data entry?Restoring the MSDE Database should be fine, which error do you get ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks for the reply. The error tells me the restore failed for the following reason;

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'TestData' database.

In considering this, I realized that I failed to create all of the tables located in the original database. Could that be the problem?

|||I solved my own problem. In thinking about my options, I remembered this from a long ago project. I moved the .mdf and .ldf files into the MSSql\Data folder on the computer with Sqlserver Express. Then I Attached the database by pointing at the .mdf file. I now have my complete database on SqlServer Express. Thanks.

importing data to MSDE

Hello,
What is the best way to import data to a client's MSDE
database from our website? We want our client to log onto
our website so we can update a table on the client's PC. I
need a little direction on this, not sure which is the best
way to go.
Thank you,
Randers
hi Randers,
Randers wrote:
> Hello,
> What is the best way to import data to a client's MSDE
> database from our website? We want our client to log onto
> our website so we can update a table on the client's PC. I
> need a little direction on this, not sure which is the best
> way to go.
> Thank you,
> Randers
perhaps you could provide an ftp based solution to download new data from
your website and the MSDE remote client proceed with a scheduled job to bulk
insert it..
you can even set the job to notify via mail of the success/failure of it...
(just use an SMTP solution like http://www.sqldev.net/xp/xpsmtp.htm )
or mail them, but this become more manual as the final user has perhaps to
read the mail, save the attached file in some defined file system position
and let the scheduled task process the file..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.msde:20144
send them an sql file as they will not be able to login to msde remotely
"Randers" <rmonroe@.aulcorp.com> wrote in message
news:06cc01c52e3e$1e481e20$a501280a@.phx.gbl...
> Hello,
> What is the best way to import data to a client's MSDE
> database from our website? We want our client to log onto
> our website so we can update a table on the client's PC. I
> need a little direction on this, not sure which is the best
> way to go.
> Thank you,
> Randers