[ale] SQL insert from another database

[james.sumners at gmail.com (James Sumners)]

You are close:

INSERT INTO A.users (name) VALUES( (SELECT busers.name FROM B.users busers) );

I believe that should work, provided the user executing the query has
SELECT access to the B database and INSERT access to the A database.

On Fri, Jul 23, 2010 at 11:04 AM, Chris Fowler
<cfowler at outpostsentinel.com> wrote:
> I've got an employee that is starting in SQL but does not know perl. ?He
> wants to copy rows from one database to another. ?I would do this in SQL
> or even SQL + Perl but I want to figure out how we could do this in just
> SQL.
>
> Here is a sample table.
>
> users
> ?user_id ?int,Primary,auto increment
> ?name
>
>
> In scenerio one we want to delete all users in A and copy B to A.
>
> delete from A.users;
> insert into A.users select * B.users;
>
> That works very well especially when there is no data in A.users.
>
> Here is the scnerio I want to do in SQL.
>
> A.users:
> ?1, Joe
> ?2, Susan
>
> B.users
> ?1, Scott
> ?2, Jack
>
> I want to copy B.users.* into A.users. ?But they users has a primary
> key. ?1,Scott would not be allowed to be copied?
>
> insert into A.users (name) select B.users.name
>
> Does that seem like it is the right way?
>
> Chris
>
>
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://mail.ale.org/mailman/listinfo/ale
> See JOBS, ANNOUNCE and SCHOOLS lists at
> http://mail.ale.org/mailman/listinfo
>



-- 
James Sumners
http://james.roomfullofmirrors.com/

"All governments suffer a recurring problem: Power attracts
pathological personalities. It is not that power corrupts but that it
is magnetic to the corruptible. Such people have a tendency to become
drunk on violence, a condition to which they are quickly addicted."

Missionaria Protectiva, Text QIV (decto)
CH:D 59