Copy tables between databases using variables

[Guest]

I am trying to find the best way to copy specific tables from one database
to another when the source and target database names are not always the
same. Can you use variables to specify (or prompt the user) to provide
source and target databases? The target database will exist with the the
same tables as the source. The tables to copy will always be the same.

Example:
UserA wants to copy 10 tables from Data1 to Data2
UserB wants to copy 10 tables from Data4 to Data5

I'm sure a script can do this in Query Analyzer but is there a more user
friendly method when the user has ony standard SQL tools?

Thanks in advance.

[John Bell]

Re: Copy tables between databases using variables

Hi

You could use DTS and change the source/destination tables:

http://www.sqldts.com/default.aspx?213

You could use dynamic SQL if you don't open yourself to SQL injection:

http://www.sommarskog.se/dynamic_sql.html

John

<rdraider@sbcgl obal.net> wrote in message
news:5GRWb.2304 2$V57.1004@news svr27.news.prod igy.com...[color=blue]
> I am trying to find the best way to copy specific tables from one database
> to another when the source and target database names are not always the
> same. Can you use variables to specify (or prompt the user) to provide
> source and target databases? The target database will exist with the the
> same tables as the source. The tables to copy will always be the same.
>
> Example:
> UserA wants to copy 10 tables from Data1 to Data2
> UserB wants to copy 10 tables from Data4 to Data5
>
> I'm sure a script can do this in Query Analyzer but is there a more user
> friendly method when the user has ony standard SQL tools?
>
> Thanks in advance.
>
>[/color]