Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Location
    Elbonia with Dilbert and Wally
    Posts
    2

    Unanswered: UPDATE a Table in another Database in the same MSSQL Server

    Hi all,

    does anyone here know how to update another table in another database on the same MSSQL server ?

    I cant use the "USE" statment in my stored procedures or triggers. Any suggestions ?

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    Atlanta, GA USA
    Posts
    19

    Re: UPDATE a Table in another Database in the same MSSQL Server

    Sure... there are three (sometimes four) parts to an object name...

    [database].[owner].[tablename]

    So, to update a table (lets say Invoices) in database (ARsystem), you could say:

    UPDATE ARsystem..Invoices

    Note: not putting in the [owner] is the same as putting in [dbo].

    You could even update a table in another database on a different server using linked servers (see BOL) using the four-part name:

    [linked server].[database].[owner].[tablename]


    Originally posted by Softwaremaker
    Hi all,

    does anyone here know how to update another table in another database on the same MSSQL server ?

    I cant use the "USE" statment in my stored procedures or triggers. Any suggestions ?

    Thanks.
    HueyStLoui

  3. #3
    Join Date
    Sep 2002
    Location
    Elbonia with Dilbert and Wally
    Posts
    2
    Thanks HueyStLoui for the very helpful tip. I appreciate it lots.

  4. #4
    Join Date
    Sep 2002
    Location
    NYC US
    Posts
    2

    Unhappy Re: UPDATE a Table in another Database in the same MSSQL Server

    I got an error by using
    "INSERT INTO [linked server].[database].[owner].[tablename]" within a StoredProcedure

    "The object name 'secap118.comdev.dbo.' contains more than the maximum number of prefixes. The maximum is 2."

    but don't get this error for select and delete. Anybody knows WHY?




    Originally posted by HueyStLoui
    Sure... there are three (sometimes four) parts to an object name...

    [database].[owner].[tablename]

    So, to update a table (lets say Invoices) in database (ARsystem), you could say:

    UPDATE ARsystem..Invoices

    Note: not putting in the [owner] is the same as putting in [dbo].

    You could even update a table in another database on a different server using linked servers (see BOL) using the four-part name:

    [linked server].[database].[owner].[tablename]

  5. #5
    Join Date
    Sep 2002
    Location
    Atlanta, GA USA
    Posts
    19

    Arrow Re: UPDATE a Table in another Database in the same MSSQL Server

    If you typed the correct error message, it seems like you are missing the tablename (or did you leave it off on purpose)? I am assuming this is SQL Error 7985. Can we see the actual INSERT statement?

    Originally posted by goya
    I got an error by using
    "INSERT INTO [linked server].[database].[owner].[tablename]" within a StoredProcedure

    "The object name 'secap118.comdev.dbo.' contains more than the maximum number of prefixes. The maximum is 2."

    but don't get this error for select and delete. Anybody knows WHY?
    HueyStLoui

  6. #6
    Join Date
    Sep 2002
    Location
    NYC US
    Posts
    2

    Re: UPDATE a Table in another Database in the same MSSQL Server

    I think the problem came from "SET IDENTITY_INSERT '+@TargetTableName"

    see the complete SQL below:

    "
    SET @TargetTableName = 'secap118.comdev.dbo.tTradeData'
    SET @SourceTableName = 'tTradeData'

    exec ('
    SET IDENTITY_INSERT '+@TargetTableName+' ON
    insert into '+@TargetTableName+'
    ([rowID], [cmdID], [yr], [rtCode], [ptCode], [rgCode], [htCode], [estCode], [qtCode], [NetWeight], [TradeQuantity], [TradeValue])
    select rowID, cmdID, yr, rtCode, ptCode, rgCode, htCode, estCode,
    qtCode,NetWeight ,TradeQuantity, TradeValue from '+@SourceTableName+'
    left join #tmpcount1 on '+@SourceTableName+'.rowID= #tmpcount1.ID
    where #tmpcount1.fg=1

    SET IDENTITY_INSERT '+@TargetTableName+' OFF
    '
    )

    "

    Originally posted by HueyStLoui
    If you typed the correct error message, it seems like you are missing the tablename (or did you leave it off on purpose)? I am assuming this is SQL Error 7985. Can we see the actual INSERT statement?

  7. #7
    Join Date
    Sep 2002
    Location
    Atlanta, GA USA
    Posts
    19

    Arrow Re: UPDATE a Table in another Database in the same MSSQL Server

    Yes, you are correct. You cannot use SET IDENTITY_INSERT with a linked server. It only accepts [database].[owner].[table]


    Originally posted by goya
    I think the problem came from "SET IDENTITY_INSERT '+@TargetTableName"

    see the complete SQL below:

    "
    SET @TargetTableName = 'secap118.comdev.dbo.tTradeData'
    SET @SourceTableName = 'tTradeData'

    exec ('
    SET IDENTITY_INSERT '+@TargetTableName+' ON
    insert into '+@TargetTableName+'
    ([rowID], [cmdID], [yr], [rtCode], [ptCode], [rgCode], [htCode], [estCode], [qtCode], [NetWeight], [TradeQuantity], [TradeValue])
    select rowID, cmdID, yr, rtCode, ptCode, rgCode, htCode, estCode,
    qtCode,NetWeight ,TradeQuantity, TradeValue from '+@SourceTableName+'
    left join #tmpcount1 on '+@SourceTableName+'.rowID= #tmpcount1.ID
    where #tmpcount1.fg=1

    SET IDENTITY_INSERT '+@TargetTableName+' OFF
    '
    )

    "
    HueyStLoui

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •