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

Amfiamfi1 (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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Insert trigger calls .NET application

I have an appication that feeds a SQL Server 2005 database with records. I
have another application that should treat the records inserted by this firs
t
application. I know you can achieve this by incorporating .NET code in SQL
Server 2005.
However is there another possibility that SQL Server tiggers my second
application after inserting records from the first application?
thanks.Hello Guy,

> I have an appication that feeds a SQL Server 2005 database with
> records. I
> have another application that should treat the records inserted by
> this first
> application. I know you can achieve this by incorporating .NET code in
> SQL
> Server 2005.
> However is there another possibility that SQL Server tiggers my second
> application after inserting records from the first application?
There's a couple of ways of doing that:
a. Use service broker to do the inserts and send a copy of the data to a
queue that your second program processes
b. Use a SQLDependency in your second application to watch the table in ques
tion,
get the new records and process them.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql

insert simultaneously in 2 tables

Dear All,

I am not an expert on T-SQL and I am trying out a small project to learn.

I

have encountered a problem, whereby I have 2 tables, 1 containing the

header (header_id, header_file, admin_menu_id and admin_submenu_id) and

then I have another table called header_details, where I am storing the

language details for this header, with the fields being

(header_details_id, fk_header_id, header_alt, header_caption and

fk_language_id)

Now I want to create a stored proc, first to

insert the header and then the header details. I also want that if the

header or header details already exist, I just do an update on these

tables.

I tried the following code but its not working:-

ALTER Procedure [dbo].[INSERT_Header]
(
@.admin_menu_idint,
@.admin_submenu_idint,
@.header_filevarchar(150),
@.header_altvarchar(150),
@.header_captionvarchar(200),
@.language_idint
@.outIDint OUTPUT
)
AS
BEGIN

-- First do a select on the header table to see if this header already exists

DECLARE @.count integer = 0

SELECT COUNT(*) as @.count
FROM headers
WHERE[fk_admin_menu_id] = @.admin_menu_id
AND[fk_admin_submenu_id] = @.admin_submenu_id

--if it exists, then update this header
IF @.count > 0
BEGIN
UPDATE [headers]
SET [header_file]= @.header_file

WHERE [fk_admin_menu_id] = @.admin_menu_id
[fk_admin_submenu_id] = @.admin_submenu_id
ELSE
-- Insert header in header table --
BEGIN
INSERT INTO [headers]
([header_file]
,[fk_admin_menu_id]
,[fk_admin_submenu_id])
VALUES
(@.header_file
,@.admin_menu_id
,@.admin_submenu_id)
-- Get the Inserted Header ID --
SET @.outID = SCOPE_IDENTITY()
END

-- Now do a select on the header_details table to see if this header with this language already exists
DECLARE @.count_details integer = 0

SELECT COUNT(*) as @.count_details
FROM header_detail
WHERE[fk_admin_menu_id] = @.admin_menu_id
AND[fk_admin_submenu_id] = @.admin_submenu_id
AND[fk_language_id] = @.language_id

--if it exists, then update this header
IF @.count_details > 0
BEGIN
UPDATE [header_detail]
SET[header_alt]= @.header_file
,[header_caption] = @.header_caption

WHERE [fk_header_id] = @.header_id
[fk_language_id] = @.language_id
ELSE
-- Insert header in header table --
BEGIN
INSERT INTO [header_detail]
([fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id])
VALUES
(@.@.out_ID
,@.header_alt
,@.header_caption
,@.language_id)
END

END

Can you help me out please?

Thanks a lot for your help and time

Johannhave you done a syntax check... your T-SQL is full of errors

1
DECLARE @.count integer = 0 is not valid, you cannot assign a value on a declaration

2
SELECT COUNT(*) as @.count
FROM headers
WHERE[fk_admin_menu_id] = @.admin_menu_id
AND[fk_admin_submenu_id] = @.admin_submenu_id
does not assign a value to @.count, to do do it you must write as follows:
SELECT @.count=COUNT(*)
FROM headers

.3

WHERE [fk_admin_menu_id] = @.admin_menu_id
[fk_admin_submenu_id] = @.admin_submenu_id
ELSE
is also invalid you must put END berfore else and maybe an AND on the where clause

Are you sure you've tried the statement in query analyzer or management studio ?

|||

Hi,

you should use If Exists construct instead o using Count(*) method as

If Exists (SELECT fk_admin_menu_id FROM headers
WHERE [fk_admin_menu_id] = @.admin_menu_id
AND [fk_admin_submenu_id] = @.admin_submenu_id
)

BEGIN

UPDATE [headers]
SET [header_file] = @.header_file
WHERE [fk_admin_menu_id] = @.admin_menu_id
[fk_admin_submenu_id] = @.admin_submenu_id

END
ELSE

-- Insert header in header table --
BEGIN
INSERT INTO [headers]
([header_file]
,[fk_admin_menu_id]
,[fk_admin_submenu_id])
VALUES
(@.header_file
,@.admin_menu_id
,@.admin_submenu_id)
-- Get the Inserted Header ID --
SET @.outID = SCOPE_IDENTITY()

END

-- Now do a select on the header_details table to see if this header with this language already exists
DECLARE @.count_details integer = 0

IF EXISTS (SELECT fk_admin_menu_id FROM header_detail
WHERE [fk_admin_menu_id] = @.admin_menu_id
AND [fk_admin_submenu_id] = @.admin_submenu_id
AND [fk_language_id] = @.language_id
)

BEGIN

UPDATE [header_detail]
SET [header_alt] = @.header_file
,[header_caption] = @.header_caption
WHERE [fk_header_id] = @.header_id
[fk_language_id] = @.language_id

END

ELSE
-- Insert header in header table --
BEGIN

INSERT INTO [header_detail]
([fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id])
VALUES
(@.@.out_ID
,@.header_alt
,@.header_caption
,@.language_id)

END