473,322 Members | 1,734 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Insert/Update with dynamic database name

Hi

My problem is as follows:

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)

Thanks in advance

Amfi
Jan 25 '06 #1
1 4345
amfi1 (am***@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 25 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: jason | last post by:
How does one loop through the contents of a form complicated by dynamic construction of checkboxes which are assigned a 'model' and 'listingID' to the NAME field on the fly in this syntax:...
14
by: serge | last post by:
I have a scenario where two tables are in a One-to-Many relationship and I need to move the data from the Many table to the One table so that it becomes a One-to-One relationship. I need to...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
1
by: Abareblue | last post by:
I have no clue on how to insert a record into access. here is the whole thing using System; using System.Drawing; using System.Collections; using System.ComponentModel;
3
by: Shapper | last post by:
Hello, I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine. The update is not. I checked and my database has all...
1
by: Tim Nelson | last post by:
I am coming from a ESQL/C environment and I have the need to create an application that builds an insert and an update statement dynamically by querying a the schema for a database table. With...
17
by: NuB | last post by:
I have a sql query that is doing an update of records, how can I add NULL to the field in the database if the field on my screen is blank? example: I have 5 textboxes, and a user can leave some...
46
by: dunleav1 | last post by:
I have a process that does inserts that runs 50% slower that Oracle and Mssql. Queries normally take 50% slower than normal. DB2, Oracle, Mssql all are configured on same os, same disk array...
4
by: =?Utf-8?B?RXJpYyBGYWxza2Vu?= | last post by:
We’re storing our main entity in an insert only table which stores the history of past revisions, but we’re facing problems with storing this history as LINQ will only update the entity, and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.