| Groups | Blog | Home |
sql server msde : Importing from another database on same server using SQL Server Express
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!
Try one of these two choices, one using a JOIN, one using a Sub-SELECT:
UPDATE n SET n.Field =3D o.Field FROM NewDatabase.dbo.Table n JOIN OldDatabase.dbo.Table o ON n.PK =3D o.PK (OR) UPDATE NewDatabase.dbo.Table n SET n.Field =3D (SELECT o.FIELD FROM OldDatabase.dbo.Table o WHERE o.Field =3D n.Field ) --=20 Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience.=20 Most experience comes from bad judgment.=20 - Anonymous You can't help someone get up a hill without getting a little closer to = the top yourself. - H. Norman Schwarzkopf [quoted text, click to view] "John Hackert" <hackertjohnb@yahoo.com> wrote in message =
news:1162174049.649530.85940@f16g2000cwb.googlegroups.com... >I haven't been able to solve this likely syntax problem despite > referring to these groups, the Books On Line, and reference texts: >=20 > 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? >=20 > 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. >=20 > 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 >=20 > I tried a few different query techniques as suggested in historical > posts, but to no avail. Here is such an example that I tried: >=20 > In MSE I right clicked on the target table in question and chose: > "Script table as," then > "UPDATE to," then > "New Query Editor Window" >=20 > I removed all the fields except that in question and added this Where > clause: > UPDATE [NewDatabase].[dbo].[Table] > SET [Field] =3D [OldDatabase].[dbo].[Table].[Field] > WHERE [NewDatabase].[dbo].[Table].[PK] =3D > [OldDatabase].[dbo].[Table].[PK] >=20 > in which the primary key (an auto-increment integer) is identical > between the new and old tables. >=20 > This produces the error: > Msg 4104, Level 16, State 1, Line 1 > The multi-part identifier "OldDatabase.dbo.Table.PK" could not be > bound. >=20 > 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. >=20 > I would be grateful if someone could explain the proper syntax to = refer > to another database within the same server instance or otherwise. >=20 > 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 [quoted text, click to view] 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.googlegroups.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! > > > ------=_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> </DIV> > <DIV><FONT face="Courier New" size=2>UPDATE n</FONT></DIV> > <DIV><FONT face="Courier New" size=2> 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> JOIN OldDatabase.dbo.Table > o</FONT></DIV> > <DIV><FONT face="Courier New" size=2> 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> </DIV> > <DIV><FONT face="Courier New" size=2>UPDATE NewDatabase.dbo.Table n</FONT></DIV> > <DIV><FONT face="Courier New" size=2> SET n.Field = (SELECT > o.FIELD</FONT></DIV> > <DIV><FONT face="Courier New" > size=2> > FROM OldDatabase.dbo.Table o</FONT></DIV> > <DIV><FONT face="Courier New" > size=2> > WHERE o.Field = n.Field</FONT></DIV> > <DIV><FONT size=2><FONT > face="Courier New"> > )</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> </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> </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> </DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>"John Hackert" <</FONT><A > href="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="news:1162174049.649530.85940@f16g2000cwb.googlegroups.com"><FONT > face=Arial > size=2>news:1162174049.649530.85940@f16g2000cwb.googlegroups.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. Ultimately I<BR>> just plugged the small data series in manually
[quoted text, click to view]
On 30 Oct 2006 11:19:51 -0800, John Hackert wrote: >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? 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. --
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] [quoted text, click to view] Hugo Kornelis wrote:
> On 30 Oct 2006 11:19:51 -0800, John Hackert wrote: > > >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? > > 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
[quoted text, click to view]
On 4 Nov 2006 13:03:43 -0800, John Hackert wrote: >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: Hi John, I'm glad to hear that you have managed to get this sorted out! --
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |