none
T-SQL - How to change the name of the Server or the Database name when insert or updating RRS feed

  • Question

  • Good Day Everyone

    I would like to know if it's possible to change the name of the Server or the Database name when insert or updating.

    example I have a 2 SQL Server with 2 databases identical, and I have also 2 users who have access on different databases,

    SQLSERVER1 has EmployeeDB_A1 and GroupDB_A1

    while SQLSERVER2 has EmployeeDB_A2 and GroupDB_A2

    now I have one database that can access both of this servers, the 2 users data are separated in two databases, if user1's data has to be updated, this will use the SQLServer1 and the database EmployeeDB_A1 and the GroupDB_A2,then for User2 is the EmployeeDB_A2 and GroupDB_A2

    I tried to use dynamic sql string like:

    if @GetUser = User1
    begin
     declare @GetSQLSTr nvarchar(max)
     set @GetSQLStr = 'update [''' + @GetServerName + '''].[''' + @GetDatabaseName + '''].[dbo].[tblEmployeeDetails]
     set UserEmail = ''' + @GetUserEmail. +''' '
    end

    But If I will update to many data on users using the two database on the server 1 using this code, it will take a while and I might miss or set a wrong syntax, is there a way where I don't have to use this method or should I continue code using this?

    Thanks everyone

    Saturday, September 26, 2020 12:13 PM

All replies