locked
Modify database1 from database2 RRS feed

  • Question

  • Hello,

    How to use DDL/DML statement to modify database2.tbl siting on database1.tbl?
    or
    I am creating stored procedure/trigger in database1.tbl to modify database2.tbl particularly trigger.
    Thanks.
    NL
    Tuesday, May 19, 2009 9:16 AM

Answers

  • This would be allowed EXEC('Use someDB')

    -Jens
    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    • Proposed as answer by Paul White (NZ)MVP Wednesday, May 20, 2009 8:38 AM
    • Marked as answer by nl1234 Sunday, May 24, 2009 7:17 AM
    Wednesday, May 20, 2009 7:33 AM

All replies

  • DDL statements are for local databases. The statements can be executed by using an dynamic execution string with 'USE SomeDB; CREATE ...'. DML statements can be executed with using the three part naming syntax like in UPDATE SomeDatabase.OwnerName.ObjectName SET SomeCOLUMN = SomeValue

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Tuesday, May 19, 2009 9:35 AM
  • But the problem is "USE somedb" statement is not allowed in triggers.
    NL
    Wednesday, May 20, 2009 6:02 AM
  • It is unusual to issue DDL statements from a trigger.  Can you give more details on what you are looking to do?  Someone may be able to propose a better approach.

    If you really really really want to do it, you could use dynamic SQL - assign the statement to a variable an EXECUTE that.  Not recommended.

    Paul
    Wednesday, May 20, 2009 7:31 AM
  • This would be allowed EXEC('Use someDB')

    -Jens
    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    • Proposed as answer by Paul White (NZ)MVP Wednesday, May 20, 2009 8:38 AM
    • Marked as answer by nl1234 Sunday, May 24, 2009 7:17 AM
    Wednesday, May 20, 2009 7:33 AM
  • Curently data is inserted into tbl1. Now when no of data becomes greater then some value say 1 million, I need to create new table which is same as tbl1 but with different name say tbl2 and start inserting data into new table. That is what I want to do. I dont want to have a table with very very larze  no of data because analysing the data's present in the table becomes very hard and running analysis script on that table is very slow.
    So any ideas.

    Thank-you. I really appreciate your concern. 


    NL
    Wednesday, May 20, 2009 7:47 AM
  • In SQL Server 2005 Enterprise/Developer/Trial consider partitioning instead.  This way data is stored in different partitions (tables in all but name) but appears to be one large table.

    Otherwise, check out partitioned views (2000 and later).  This would allow you to create sufficient tables ahead of time and add rows through the partitioning view.

    Those are my initial thoughts.

    Paul
    Wednesday, May 20, 2009 8:25 AM
  • Will look at it. Thanks to all.
    NL
    Wednesday, May 20, 2009 8:35 AM
  • Don't forget to mark Jens' posts as the answer.

    Paul
    Wednesday, May 20, 2009 8:39 AM