Manipulating Data on Table that exists on Different Databases

  • is it possible that i can manipulate (insert,modify,delete) data on a certain table that exists different databases?

    ex.

    (insert,modify,delete) users on

    user_table on database1

    user_table on database2

    user_table on database3

    user_table on database4

    user_table on database5

    thanks

  • Sure, providing your login has rights to the other databases. You just need to use the 3 part naming - database.schema.object, so

    insert into database1.dbo.user_table ...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • what i mean is at least a single script that will

    insert a user on user_table on all databases that have the user_table

  • Still possible. Do you know which DBs have that table (is it fixed), or does that change over time?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • the db names doesnt change.

  • Well if you know the databases, you could hardcode the names in the insert statements. If you don't want to do that, then this should work, providing the login that's running it has rights in all databases.

    exec sp_msforeachdb '

    use ?;

    if exists (select 1 from sys.tables where name = ''user_table'')

    begin

    insert into user_table (col1, col2, col3) values (1,''Me'', getdate())

    end;

    '

    You can add more into the if block if you need.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • thanks for the great help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply