name of database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

name of database

Author

Topic

wided
Posting Yak Master

218 Posts

Posted - 2010-05-25 : 10:24:10

hello

I have 2 database (DB1 and DB2)
i have to insert records in DB2 from DB1 with trigger
it is ok if i write this:

DB1 --> table1
DB2 --> table2

in trigger of insert in table1, i write this:

insert into [server].DB2.dbo.table2
select col1, col2 from inserted

my server can change and my database too
i want to stock the name of server and then name of database in table (X)
can i use the variable name of server and name of database
and replace [servername].DB2.dbo. in then query with variable
How can i declare it if table X (col1 =servername) and (col2 = databasename)

Exuse me if it is error in my text because i can read english but not whrite it well (french)

Kristen
Test

22859 Posts

Posted - 2010-05-25 : 13:56:36

My French is terrible, so I reckon your English is just fine

Your idea of a trigger is fine. Although NOT required I think it is better to write it as:


insert into [server].DB2.dbo.table2
(
    col1, col2
)
select col1, col2 from inserted

However, I don't think there is a way to create the action as dynamic SQL - i.e. incorporating the Server name(s) from a configuration table because I don't think it is possible to access [inserted] table from dynamic SQL.

But you might be able to do this in a trigger:


SELECT Col1, Col2
INTO ##MyTempTable
FROM inserted

DECLARE @strSQL varchar(MAX)

SELECT @strSQL = 'INSERT INTO [' + @MyTargetServer + '].DB2.dbo.table2
select col1, col2 from ##MyTempTable'

EXEC (@strSQL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources