Reply to Re: Insert/Update with dynamic database name

Your name:

Reply:


Posted by Erland Sommarskog on 01/26/06 00:13

amfi1 (amfi1@poczta.fm) writes:
> I need to transmit data between two databases on the same server, but I
> have to use dynamic database names (they must be configurable). For
> example I need to achive sth like that:
>
> insert into [database1].[dbo].[table1]
> (select columns from [table2])
>
> when database1 is not known at implementation stage.
>
> I know I can use EXEC @t_sql_code, but I wonder if there is any other
> way? (OPENROWSET doesn't seem to suit my needs)

Well, one thing you can do is to use stored procedures:

SELECT @spname = @srcdb + '.dbo.getmydata'
INSERT table1 (...)
EXEC @spname

Using a dynamic SP name is not as messy as have all the code in dynamic
SQL.

Now, your example indicates that it is the target database that is
unknown to you, in which case my suggestion does not work.

A faint possibility is to set up a linked server that loops back to
your own server. The target database would then be in the connection
string. You could thus say:

INSERT MYSERVER..dbo.table (...)
SELECT...

and you would set up the linked server as you need it. But there is
an overhead for the loopback. And I must that I have not tested if
it actually works.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация