Post reply

Manipulating Data on Table that exists on Different Databases

  • September 22, 2008 at 1:40 am

    #127539

    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

  • September 22, 2008 at 1:42 am

    #874821

    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

  • September 22, 2008 at 2:00 am

    #874823

    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

  • September 22, 2008 at 2:18 am

    #874826

    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

  • September 22, 2008 at 4:30 am

    #874865

    the db names doesnt change.

  • September 22, 2008 at 5:49 am

    #874901

    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

  • September 22, 2008 at 6:56 pm

    #875357

    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