Asp Forum - Copying from one DB to another - microsoft.public.sqlserver.programming

Ant

3/22/2007 5:33:00 AM

Hi,

I'm trying to copy from one DB to another but I'm getting an error when
doing so.

The error I get is:
The column prefix 'DB1.dbo.MenuView' does not match with a table name or
alias name used in the query.

This is the query:

insert DB1.dbo.MenuView
select * from DB2.dbo.MenuView
where DB1.dbo.MenuView.MenuViewID <> DB2.dbo.MenuView.MenuViewID

The error seems to be in the Where statement; but why?

Thanks for any help on this.

8 Answers

xyb

3/22/2007 6:26:00 AM

On 3?22?, ??1?33?, Ant <A...@discussions.microsoft.com> wrote:
> Hi,
>
> I'm trying to copy from one DB to another but I'm getting an error when
> doing so.
>
> The error I get is:
> The column prefix 'DB1.dbo.MenuView' does not match with a table name or
> alias name used in the query.
>
> This is the query:
>
> insert DB1.dbo.MenuView
> select * from DB2.dbo.MenuView
> where DB1.dbo.MenuView.MenuViewID <> DB2.dbo.MenuView.MenuViewID
>
> The error seems to be in the Where statement; but why?
>
> Thanks for any help on this.

you are right the error comes from where clause,since DB1.dbo.MenuView
is not a valid object name of this select statement
select * from DB2.dbo.MenuView
where DB1.dbo.MenuView.MenuViewID <> DB2.dbo.MenuView.MenuViewID

you can first use select code to detect rows which you want to insert
like this:
--insert DB1.dbo.MenuView
select * from DB2.dbo.MenuView
where DB1.dbo.MenuView.MenuViewID <> DB2.dbo.MenuView.MenuViewID

here you will get the same error.this is not similar to update clause
like
update tablea
from tableb where tablea.columnname op tableb.column

to your issue,i think what you really want is this:

insert DB1.dbo.MenuView
select * from DB2.dbo.MenuView as A
where A.MenuViewID NOT IN (select MenuViewID from DB1.dbo.MenuView)

hope this help you :)

Uri Dimant

3/22/2007 6:58:00 AM

Ant
> insert DB1.dbo.MenuView
> select * from DB2.dbo.MenuView
> where DB1.dbo.MenuView.MenuViewID <> DB2.dbo.MenuView.MenuViewID

You middes JOIN between tweo tables and also specify columns for
INSERT/SELECT

insert into DB1.dbo.MenuView (col1,col2)
select DB2.dbo.MenuView.col1,DB2.dbo.MenuView.col2
from DB2.dbo.MenuView JOIN DB1.dbo.MenuView ON
DB1.dbo.MenuView.pk=DB2.dbo.MenuView.pk
where DB1.dbo.MenuView.MenuViewID <> DB2.dbo.MenuView.MenuViewID

It is not so readable ,so you can create an alias lile that

insert into DB1.dbo.MenuView (col1,col2)
select t1.col1,t1.col2
from DB2.dbo.MenuView t1 JOIN DB1.dbo.MenuView t2 ON t1.pk=t2.pk
where t2.MenuViewID <>t1.MenuViewID

"Ant" <Ant@discussions.microsoft.com> wrote in message
news:EB35B656-B84B-4C2F-8C0B-EAB3786AF78A@microsoft.com...
> Hi,
>
> I'm trying to copy from one DB to another but I'm getting an error when
> doing so.
>
>
> The error I get is:
> The column prefix 'DB1.dbo.MenuView' does not match with a table name or
> alias name used in the query.
>
> This is the query:
>
> insert DB1.dbo.MenuView
> select * from DB2.dbo.MenuView
> where DB1.dbo.MenuView.MenuViewID <> DB2.dbo.MenuView.MenuViewID
>
>
> The error seems to be in the Where statement; but why?
>
>
> Thanks for any help on this.
>
>
>
>
>

Roy Harvey

3/22/2007 12:35:00 PM

On 21 Mar 2007 23:26:05 -0700, "xyb" <xiangyuanbo@gmail.com> wrote:

>to your issue,i think what you really want is this:
>
>insert DB1.dbo.MenuView
>select * from DB2.dbo.MenuView as A
>where A.MenuViewID NOT IN (select MenuViewID from DB1.dbo.MenuView)

I agree that this is probably what was intended. An alternative:

INSERT DB1.dbo.MenuView
SELECT *
FROM DB2.dbo.MenuView as A
WHERE NOT EXISTS
(SELECT *
FROM DB1.dbo.MenuView as B
WHERE A.MenuViewID = B.MenuViewID)

Note that this approach works when there is a two-column key to join
on, while IN can not handle that situation.

Roy Harvey
Beacon Falls, CT

frank

3/31/2010 11:31:00 PM

Peter,

Can you tell me what OP stands for? Other Party?

"Peter T" wrote:

> Ryan, not sure how that advice will help the OP. From what he is saying his
> macro recorder works correctly in 2003 but not in 2007. That's particularly
> the case with shapes and charts though in this particular case it's not
> obvious why it's not working correctly. However, anyone wanting to write VBA
> is strongly advised to retain 2003 for the macro recorder alone.
>
> There may be other good reasons too. Several features have been removed in
> 2007, for example if you need to build a data cube you need to revert to
> 2003. Of course most developers need to cater for all versions.
>
> Rich, the code you posted does not look like anything the macro recorder
> should return, even in 2007.
>
> Regards,
> Peter T
>
> "ryguy7272" <ryguy7272@discussions.microsoft.com> wrote in message
> news:66D4D08C-E136-4A11-9F1A-5F662B44C42A@microsoft.com...
> >I used to have booth versions of Office too. Then, I had problems like you
> > describe and decided to get rid of 2003. Why do you need old technology?
> > 2007 will do everything that 2003 does, and a whole lot more too.
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> > "Rich" wrote:
> >
> >> I have Office 2003 AND Office 2007 installed on my PC.
> >>
> >> Now, when I record a keystroke macro to put some text into a cell and hit
> >> enter, then go to the VBA Editor, the resultant macro is not what I'm
> >> used to
> >> seeing. For instance to record putting some garbage in Cell a1, I get the
> >> following:
> >>
> >> ActiveCell FormulaR1C1 <= VB_VarUserMemIdasdfasdfVB_VarUserMemId
> >> Range& VB_VarUserMemIdA3VB_VarUserMemId '+Select
> >>
> >> When I attempt to run it, I receive a "Compile error: Invalid use of
> >> peropery".
> >>
> >> What do I need to change?
> >>
> >> Thanks for any help.
>
>
> .
>

Dave Peterson

4/1/2010 12:33:00 AM

Original post or original poster.

Frank wrote:
>
> Peter,
>
> Can you tell me what OP stands for? Other Party?
>
> "Peter T" wrote:
>
> > Ryan, not sure how that advice will help the OP. From what he is saying his
> > macro recorder works correctly in 2003 but not in 2007. That's particularly
> > the case with shapes and charts though in this particular case it's not
> > obvious why it's not working correctly. However, anyone wanting to write VBA
> > is strongly advised to retain 2003 for the macro recorder alone.
> >
> > There may be other good reasons too. Several features have been removed in
> > 2007, for example if you need to build a data cube you need to revert to
> > 2003. Of course most developers need to cater for all versions.
> >
> > Rich, the code you posted does not look like anything the macro recorder
> > should return, even in 2007.
> >
> > Regards,
> > Peter T
> >
> > "ryguy7272" <ryguy7272@discussions.microsoft.com> wrote in message
> > news:66D4D08C-E136-4A11-9F1A-5F662B44C42A@microsoft.com...
> > >I used to have booth versions of Office too. Then, I had problems like you
> > > describe and decided to get rid of 2003. Why do you need old technology?
> > > 2007 will do everything that 2003 does, and a whole lot more too.
> > >
> > > --
> > > Ryan---
> > > If this information was helpful, please indicate this by clicking ''Yes''.
> > >
> > >
> > > "Rich" wrote:
> > >
> > >> I have Office 2003 AND Office 2007 installed on my PC.
> > >>
> > >> Now, when I record a keystroke macro to put some text into a cell and hit
> > >> enter, then go to the VBA Editor, the resultant macro is not what I'm
> > >> used to
> > >> seeing. For instance to record putting some garbage in Cell a1, I get the
> > >> following:
> > >>
> > >> ActiveCell FormulaR1C1 <= VB_VarUserMemIdasdfasdfVB_VarUserMemId
> > >> Range& VB_VarUserMemIdA3VB_VarUserMemId '+Select
> > >>
> > >> When I attempt to run it, I receive a "Compile error: Invalid use of
> > >> peropery".
> > >>
> > >> What do I need to change?
> > >>
> > >> Thanks for any help.
> >
> >
> > .
> >

--

Dave Peterson

rich

4/1/2010 12:55:00 PM

Ugh! I agree with Ryan, however, I work in a support position and, earlier,
thought I could get away with JUST 2007.

I was wrong about that - so, a few days ago, I uninstalled Office2007,
installed 2003, then put 2007 back on.

That's when my macro recorder started doing the thing it's doing - which is
to say, putting "ugly" code in EITHER 2003 OR 2007 VBA.

When I uninstalled 2007, I ran CCleaner, but I did not thoroughly annhiliate
the previous installation of 2007. (As you know Uninstalls do not do exactly
that; sometimes that's a good thing, but mostly not!

If I go back and do uninstalls again, any advice on the best way to remove
the previous settings, which may be the cause of my displeasure!

As I write this reply, I now realize I did not uninstall my version of ASAP
Utilities. Hmmm . . .

Nonetheless, any advice . . .

"ryguy7272" wrote:

> I used to have booth versions of Office too. Then, I had problems like you
> describe and decided to get rid of 2003. Why do you need old technology?
> 2007 will do everything that 2003 does, and a whole lot more too.
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Rich" wrote:
>
> > I have Office 2003 AND Office 2007 installed on my PC.
> >
> > Now, when I record a keystroke macro to put some text into a cell and hit
> > enter, then go to the VBA Editor, the resultant macro is not what I'm used to
> > seeing. For instance to record putting some garbage in Cell a1, I get the
> > following:
> >
> > ActiveCell FormulaR1C1 <= VB_VarUserMemIdasdfasdfVB_VarUserMemId
> > Range& VB_VarUserMemIdA3VB_VarUserMemId '+Select
> >
> > When I attempt to run it, I receive a "Compile error: Invalid use of
> > peropery".
> >
> > What do I need to change?
> >
> > Thanks for any help.

Martin Brown

4/1/2010 1:54:00 PM

ryguy7272 wrote:
> I used to have booth versions of Office too. Then, I had problems like you
> describe and decided to get rid of 2003. Why do you need old technology?

Because it works.

> 2007 will do everything that 2003 does, and a whole lot more too.
>
I'd like to think this was an April Fools joke.

XL2003 was the last decently working copy of Excel. XL2007 may have
snazzy eye candy but graphs are glacially slow with moderate amounts of
date and macro recording is hopelessly broken.

It is amusing to see people struggling with XL2007 after having been
warned not to upgrade. It was even worse before SP2.

Regards,
Martin Brown

Peter T

4/1/2010 2:20:00 PM

Sounds like your system is in a real mess, that can happen if Excel versions
are not installed in the correct order. Best start again and uninstall
everything, see here too if necessary
http://support.microsoft.com...
(guess there's a 2003 page too if you search for it)

Install xl2003 first including SP3, then xl2007 with all the updates.

ASAP is only an addin and wouldn't have anything to do with your problems if
still installed. Normally though I think any relevant addin entries should
have been fully removed during the uninstall process (though make sure).

Regards,
Peter T

"Rich" <Rich@discussions.microsoft.com> wrote in message
news:BA7D2361-1F4C-4CC1-81DA-61997B826DD8@microsoft.com...
> Ugh! I agree with Ryan, however, I work in a support position and,
> earlier,
> thought I could get away with JUST 2007.
>
> I was wrong about that - so, a few days ago, I uninstalled Office2007,
> installed 2003, then put 2007 back on.
>
> That's when my macro recorder started doing the thing it's doing - which
> is
> to say, putting "ugly" code in EITHER 2003 OR 2007 VBA.
>
> When I uninstalled 2007, I ran CCleaner, but I did not thoroughly
> annhiliate
> the previous installation of 2007. (As you know Uninstalls do not do
> exactly
> that; sometimes that's a good thing, but mostly not!
>
> If I go back and do uninstalls again, any advice on the best way to remove
> the previous settings, which may be the cause of my displeasure!
>
> As I write this reply, I now realize I did not uninstall my version of
> ASAP
> Utilities. Hmmm . . .
>
> Nonetheless, any advice . . .
>
> "ryguy7272" wrote:
>
>> I used to have booth versions of Office too. Then, I had problems like
>> you
>> describe and decided to get rid of 2003. Why do you need old technology?
>> 2007 will do everything that 2003 does, and a whole lot more too.
>>
>> --
>> Ryan---
>> If this information was helpful, please indicate this by clicking
>> ''Yes''.
>>
>>
>> "Rich" wrote:
>>
>> > I have Office 2003 AND Office 2007 installed on my PC.
>> >
>> > Now, when I record a keystroke macro to put some text into a cell and
>> > hit
>> > enter, then go to the VBA Editor, the resultant macro is not what I'm
>> > used to
>> > seeing. For instance to record putting some garbage in Cell a1, I get
>> > the
>> > following:
>> >
>> > ActiveCell FormulaR1C1 <= VB_VarUserMemIdasdfasdfVB_VarUserMemId
>> > Range& VB_VarUserMemIdA3VB_VarUserMemId '+Select
>> >
>> > When I attempt to run it, I receive a "Compile error: Invalid use of
>> > peropery".
>> >
>> > What do I need to change?
>> >
>> > Thanks for any help.

microsoft.public.sqlserver.programming

Copying from one DB to another

Ant

xyb