importing XML/ASCII files to SQl database using VB express

Hi everyone,

I have to write a program in VB to receive the read data from a RFID reader for my graduation project.The problem is I am not a computer science student so I have only general info on programming.

I created my DB in VB express but I couldn't find out how to send the read data (that will be either in XML or ASCII format) to my database...The read data will be transferred to my computer by the RFID reader's software but after that I don'T know how to transfer it to my DB.As I know I have to use commands like read.xml etc,but no idea how write the complete program.

I checked the forum and couldn't find the answer,sorry if someone already answered my question and I missed it.

Thanks...

Can

i suspect that you will find greater success if you post your question here: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=38&SiteID=1|||

ok,thanks,now I 'll do it...

Can

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 tables to Microsoft SQL Server Management Studio Express

Hello,

I'm trying to import some tables from another sql server to my laptop. I just downloaded Microsoft SQL Server Management Studio Express and i'm not sure how to import things. Could anyone help me with that?

Thanks a bunch

j

Imports rely on SSIS which isn't supported in the express edition. However you can use the bcp utility or backup and restore the database.

|||

Thanks,

I'll look into the bcp utility, i'm not really looking to move the whole database (it's really big) just a few tables for a demo.

thanks again.

|||Anytime

Importing tables to Microsoft SQL Server Management Studio Express

Hello,

I'm trying to import some tables from another sql server to my laptop. I just downloaded Microsoft SQL Server Management Studio Express and i'm not sure how to import things. Could anyone help me with that?

Thanks a bunch

j

Imports rely on SSIS which isn't supported in the express edition. However you can use the bcp utility or backup and restore the database.

|||

Thanks,

I'll look into the bcp utility, i'm not really looking to move the whole database (it's really big) just a few tables for a demo.

thanks again.

|||Anytime

Importing stored procedures from 1 db to another?

Hi there,

Can anyone help?

I just recently imported my Sql server express 2005 db into the full version of sql server 2005... All went according to plan (i used import in studio management 2005 vers not 2005 express) and the data was imported but no stored procedures went with it...

it seemed to only include tables and views...

Is there another way round this?

Unfortunatley i don't have FULL access to the SQL Server 2005 as this is at my hosts... but i have full access to the sql server 2005 express which is locally on my machine

Any advice would be really appreciated

Thanks

ian

Several of the easiest ways:

1. backup the database and restore it to your new server

2. In Object Explorer, you can script out the stored procedures and apply them to the new server

3. Use snapshot replication, but I'd stick with #1 or #2 first.

|||

The previous post is correct - the easiest way to make sure you get all objects from one DB to another is to back it up and restore it to another.

If you don't own the other box that might not be possible. You've already got the data, so to get the other objects you can right-click each and script them out. You can also use the scripting model in DMO to do the same thing, but get all of the objects at once. I have a series of tutorials on that starting here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=110

Buck Woody

Carpe Datum

Importing Null Date Fields

I'm using SQL Server Express and am trying to import a CVS file. The CVS file contains a string field (named DAS) that represents a Date. This field can be null.

I've tried using the DTS Wizard to import this CVS file and convert the DAS field to a Date, which works great until it hits a record with a NULL DAS field. It then throws a convertion error.

Still using the DTS Wizard, I've changed the DataType of the DAS field in the Source file to [DT_DATE], it works fine but all the null dates are converted to 12/30/1899.

Is there a way (DTS Wizard or something else) that will allow me to import these CVS files with null Date fields and keep them as null in SQL Server table.

Thanks for any help,

Jon

Hi,

I read your post and I can advice you the next:

1. Once you have date 12/30/1899 instead all NULL values in SQL Server => you may easy to sort that records, to mark them and place NULL value with “copy” and “paste”. It is easy and quickly.

2. You may write a program instead DTS Wizard /I don’t know that wizard/. That program will read from your CVS file and write to SQL server. From the program you will have full control on all fields. I personally prefer to write a program when I have some unusual case.

I hope that advices will solve the problem. But if you still can’t make NULL values – let me know.

Regards,

Hristo Markov

|||Off the top of my head, I'm thinking there is an option to "Keep Nulls" inside the wizard. I haven't run it and I'm not on my machine w/ SSIS installed so I can't verify it. If you do see such an option, that is how you tell SSIS to preserve the NULLs.

If this isn't an option, then you'll have to go one step deeper and either do custom transformations or write an EXECUTE SQL TASK that will go through and update any record of 12/30/1899 to be NULL.

importing from oracle to an adp database

hi,

this is my current set up:

sql server 2005 express edition,
sql server management studio express,
oracle tables (original source data currently linked through odbc)
adp database file (new db where i'm trying to import the above oracle tables)

i need to import (or make copies) on a weekly/daily basis, of several oracle tables into a new adp database.

what is the fastest option? some of these tables hold over 1m records.

i have also heard of "linked servers" and "dts", but would like some experts advice before starting looking into this.

many thanks
luzippu

Hi luzippu,

Since you're going to be doing this on a regular basis I would recomend using the following:

Linked Server to the Oracle database.|||

Mike,

thank you for your feedback, it was nice to see a reply after many views...

Since I've posted this query I've had chance to find out more about the different versions.

In addition I have now the option to use MS SQL Server 7.0, thus DTS and the import scheduler facility.

Do you think this is the best option? Or shall I go with your solution above?

If the latter, could you give me an idea on what a Stored Procedure would look like to copy let's say:
ora_table1 to sql_table1 where create_date >= "01/01/2000"

Thank you
luzippu

sql

Importing from Excel to SQL Server 2005 Express

I'm trying to import a database. It seems my most likely route is via Excel, so I've moved my tables in there.

Then I tried setting up Excel as a linked server as described in http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321686

My linked server gets created apparently with no errors, but I can't see any contents when I click on it in the object explorer.

Anyone with more clues than me?

thanks

Just setting up the linked server doesn't do anything for the data - you have to issue a query against it. Linked servers are useful if you want to continue working with the data in Excel, but also see it in SQL Server.

If you're just looking to import the data from Excel into SQL Server, you can use Integration Services to do that much quicker, or you can save the Excel file as a comma-separated file (CSV) and use the command line tool called bcp to copy it in. More on both here:

http://support.microsoft.com/kb/321686

Buck Woody

Importing from Excel to SQL Server 2005 Express

I'm trying to import a database. It seems my most likely route is via Excel, so I've moved my tables in there.

Then I tried setting up Excel as a linked server as described in http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321686

My linked server gets created apparently with no errors, but I can't see any contents when I click on it in the object explorer.

Anyone with more clues than me?

thanks

Just setting up the linked server doesn't do anything for the data - you have to issue a query against it. Linked servers are useful if you want to continue working with the data in Excel, but also see it in SQL Server.

If you're just looking to import the data from Excel into SQL Server, you can use Integration Services to do that much quicker, or you can save the Excel file as a comma-separated file (CSV) and use the command line tool called bcp to copy it in. More on both here:

http://support.microsoft.com/kb/321686

Buck Woody

sql

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@.yahoo.com> wrote in message news:1162174049.649530.85940@.f16g2000cwb.googlegro ups.com...
>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!
>
Thank you for replying -
Even with these other techniques, I receive the same "multi-part
identifier...could not be bound" error. So, perhaps the root of the
problem is not the syntax of the query but something else. Any other
ideas?
John H
Arnie Rowland wrote:
> 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@.yahoo.com> wrote in message news:1162174049.649530.85940@.f16g2000cwb.googlegro ups.com...
> --=_NextPart_000_01FC_01C6FB8D.01E3E1B0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 5469
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.5296.0" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=Arial size=2>Try one of these two choices, one using a JOIN, one
> using a Sub-SELECT:</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
> <DIV><FONT face="Courier New" size=2>UPDATE&nbsp;n</FONT></DIV>
> <DIV><FONT face="Courier New" size=2>&nbsp;&nbsp; SET n.Field =
> o.Field</FONT></DIV>
> <DIV><FONT face="Courier New" size=2>FROM NewDatabase.dbo.Table n</FONT></DIV>
> <DIV><FONT face="Courier New" size=2>&nbsp;&nbsp; JOIN OldDatabase.dbo.Table
> o</FONT></DIV>
> <DIV><FONT face="Courier New" size=2>&nbsp;&nbsp;&nbsp;&nbsp;&am p;nbsp; ON n.PK =
> o.PK<BR></FONT></DIV>
> <DIV><FONT face="Courier New" size=2>(OR)</FONT></DIV>
> <DIV><FONT face="Courier New" size=2></FONT>&nbsp;</DIV>
> <DIV><FONT face="Courier New" size=2>UPDATE NewDatabase.dbo.Table n</FONT></DIV>
> <DIV><FONT face="Courier New" size=2>&nbsp;&nbsp; SET n.Field = (SELECT
> o.FIELD</FONT></DIV>
> <DIV><FONT face="Courier New"
> size=2>&nbsp;&nbsp;&nbsp;&nbsp;&am p;nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&am p;nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&am p;nbsp;&nbsp;&nbsp;
> FROM OldDatabase.dbo.Table o</FONT></DIV>
> <DIV><FONT face="Courier New"
> size=2>&nbsp;&nbsp;&nbsp;&nbsp;&am p;nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&am p;nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&am p;nbsp;&nbsp;&nbsp;
> WHERE o.Field = n.Field</FONT></DIV>
> <DIV><FONT size=2><FONT
> face="Courier New">&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;
> )</FONT></DIV></FONT>
> <DIV><FONT face=Arial size=2></FONT><BR><FONT face=Arial size=2>-- <BR>Arnie
> Rowland, Ph.D.<BR>Westwood Consulting, Inc</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
> <DIV><FONT face=Arial size=2>Most good judgment comes from experience. <BR>Most
> experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
> <DIV><FONT face=Arial size=2>You can't help someone get up a hill without
> getting a little closer to the top yourself.<BR>- H. Norman
> Schwarzkopf</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
> <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
> <DIV><FONT face=Arial size=2>"John Hackert" <</FONT><A
> href="http://links.10026.com/?link=mailto:hackertjohnb@.yahoo.com"><FONT face=Arial
> size=2>hackertjohnb@.yahoo.com</FONT></A><FONT face=Arial size=2>> wrote in
> message </FONT><A
> href="http://links.10026.com/?link=news:1162174049.649530.85940@.f16g2000cwb.goo glegroups.com"><FONT
> face=Arial
> size=2>news:1162174049.649530.85940@.f16g2000cwb.go oglegroups.com</FONT></A><FONT
> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>>I haven't been
> able to solve this likely syntax problem despite<BR>> referring to these
> groups, the Books On Line, and reference texts:<BR>> <BR>> Without the use
> of Data Transformation Services in SQL Server Express,<BR>> what techniques
> work to import/export data between databases under the<BR>> same server
> instance?<BR>> <BR>> The specific example I was working on involved an
> attempt to run an<BR>> update query to place data from a specific field in an
> older copy of an<BR>> otherwise identical database into a more recent
> copy.&nbsp; Ultimately I<BR>> just plugged the small data series in manually
> using side-by-side views<BR>> because I could not overcome the syntax errors
> generated in the effort<BR>> to refer to the "external" database.<BR>>
> <BR>> This is the setup in question:<BR>> - An instance in the format of
> "MyComputerName\SQLExpress," and both an<BR>> - "OldDatabase" and<BR>> -
> "NewDatabase" attached and viewable from the Management Studio<BR>> Express,
> with identical fields<BR>> <BR>> I tried a few different query techniques
> as suggested in historical<BR>> posts, but to no avail.&nbsp; Here is such an
> example that I tried:<BR>> <BR>> In MSE I right clicked on the target
> table in question and chose:<BR>> "Script table as," then<BR>> "UPDATE
> to," then<BR>> "New Query Editor Window"<BR>> <BR>> I removed all the
> fields except that in question and added this Where<BR>> clause:<BR>>
> UPDATE [NewDatabase].[dbo].[Table]<BR>>&nbsp;&nbsp; SET [Field] =
> [OldDatabase].[dbo].[Table].[Field]<BR>> WHERE
> [NewDatabase].[dbo].[Table].[PK] =<BR>>
> [OldDatabase].[dbo].[Table].[PK]<BR>> <BR>> in which the primary key (an
> auto-increment integer) is identical<BR>> between the new and old
> tables.<BR>> <BR>> This produces the error:<BR>> Msg 4104, Level 16,
> State 1, Line 1<BR>> The multi-part identifier "OldDatabase.dbo.Table.PK"
> could not be<BR>> bound.<BR>> <BR>> If I understood from prior posts,
> using joins is less desirable in<BR>> update queries, but even so likewise
> produced errors when I tried that<BR>> approach.<BR>> <BR>> I would be
> grateful if someone could explain the proper syntax to refer<BR>> to another
> database within the same server instance or otherwise.<BR>> <BR>> Many
> thanks!<BR>></FONT></BODY></HTML>
> --=_NextPart_000_01FC_01C6FB8D.01E3E1B0--
|||Thank you for replying
I thought this post was considered dead, so I elected to repost the
question on microsoft.public.sqlserver.server. The syntax proposed by
"amish" on that group proved to work:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
[TestOld].[dbo].[tblProcedure] TestOld
on
TestNew.[procedureID]=TestOld.[procedureID]
Hugo Kornelis wrote:
> On 30 Oct 2006 11:19:51 -0800, John Hackert wrote:
>
> Hi John,
> Please post the COMPLETE query, and the COMPLETE error message. Use copy
> and paste to prevent errors. That information can help us pinpoint the
> problem.
> --
> Hugo Kornelis, SQL Server MVP
sql

Importing excel data into new table in MS SQL Server

I have installed SQL Server Managemert Server Express .... I am wondering how to import data from an excel spreadsheet?

.. Silent Running

hi,

you "traditionally" use a linked server to perform that kind of operation, but, if you install the DTSWizard from the Microsoft SQL Server 2005 Express Edtion Toolkit, you have some kind of user interface for that as well..

regards

|||

I download the toolkit but i didn't see any thing about a dtswizard

|||

hi,

no shortcut is created for it... but have a look in \Program Files\Microsoft SQL Server\90\DTS\Binn\dtswizard.exe ..

regards

Importing excel data into new table in MS SQL Server

I have installed SQL Server Managemert Server Express .... I am wondering how to import data from an excel spreadsheet?

.. Silent Running

hi,

you "traditionally" use a linked server to perform that kind of operation, but, if you install the DTSWizard from the Microsoft SQL Server 2005 Express Edtion Toolkit, you have some kind of user interface for that as well..

regards

|||

I download the toolkit but i didn't see any thing about a dtswizard

|||

hi,

no shortcut is created for it... but have a look in \Program Files\Microsoft SQL Server\90\DTS\Binn\dtswizard.exe ..

regards

Importing database from Access to SQL Express

How do I import tables from an Access database to a SQL Express database?
reidarT
I think the best choice is to use the Access Upsizing Wizard. Look for it in
Tools, Database Utilities, Upsizing Wizard.
Ben Nevarez, MCDBA, OCP
Database Administrator
"reidarT" wrote:

> How do I import tables from an Access database to a SQL Express database?
> reidarT
>
>

Importing database from Access to SQL Express

How do I import tables from an Access database to a SQL Express database?
reidarTI think the best choice is to use the Access Upsizing Wizard. Look for it in
Tools, Database Utilities, Upsizing Wizard.
Ben Nevarez, MCDBA, OCP
Database Administrator
"reidarT" wrote:
> How do I import tables from an Access database to a SQL Express database?
> reidarT
>
>

Importing database from Access to SQL Express

How do I import tables from an Access database to a SQL Express database?
reidarTI think the best choice is to use the Access Upsizing Wizard. Look for it in
Tools, Database Utilities, Upsizing Wizard.
Ben Nevarez, MCDBA, OCP
Database Administrator
"reidarT" wrote:

> How do I import tables from an Access database to a SQL Express database?
> reidarT
>
>

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.