Professional Web Applications Themes

Copy tables without using checkbox......how?? - Microsoft SQL / MS SQL Server

How do I created a DTS or TSQL package that automatically copy tables starting with [DG*], there will be 100 tables starting with DG. I don't want to manually select the tables with their checkboxes. What I want is a script that will copy the tables based on the first 2letters of their name (DG*) into a new database. How do I do it? Thanks.......

  1. #1

    Default Copy tables without using checkbox......how??

    How do I created a DTS or TSQL package that automatically copy tables
    starting with [DG*], there will be 100 tables starting with DG. I don't
    want to manually select the tables with their checkboxes. What I want is a
    script that will copy the tables based on the first 2letters of their name
    (DG*) into a new database. How do I do it? Thanks....


    Joe Guest

  2. #2

    Default Re: Copy tables without using checkbox......how??

    Thanks Uri,
    I pretty much a beginner, can you be more simpler than
    this. Where do I put the values............... DG* in this script?
    Thanks.


    "Uri Dimant" <co.il> wrote in message
    news:phx.gbl... [/ref]
    a [/ref]
    name 
    >
    >[/ref]


    Joe Guest

  3. #3

    Default Re: Copy tables without using checkbox......how??

    You can do this by using the object model with DTS. You will need to loop
    through the tables in your Source DB and use thhe

    AddObjectsForTransfer method of the TransferObjectsTask

    You will also need to grab the owner so

    SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE LEFT(TABLE_NAME, 2) = 'DG' AND TABLE_TYPE = 'BASE TABLE'

    Loop through this applying each and off you go


    --

    ----------------------------
    Allan Mitchell (Microsoft SQL Server MVP)
    MCSE,MCDBA
    www.SQLDTS.com
    I support PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org



    "Joe M" <com> wrote in message
    news:#phx.gbl... 


    Allan Guest

  4. #4

    Default Re: Copy tables without using checkbox......how??

    Joe
    SELECT
    sysobjects.name TableName,
    (SELECT rows FROM sysindexes
    WHERE id = sysobjects.id
    AND indid = 0) Rows
    FROM
    sysobjects
    WHERE
    type = 'U'
    ---AND OBJECTPROPERTY(sysobjects.id , 'TableHasIndex' ) = 0
    AND sysobjects.name LIKE 'dddddddd%'



    "Joe M" <com> wrote in message
    news:#phx.gbl... 


    Uri Guest

  5. #5

    Default Re: Copy tables without using checkbox......how??

    Thanks Allan,
    My Source database is [MeatCrow] and tables [DG*] and
    destination database is [MeatBires] .
    Where do I put these values in this script?? Can you give a sample script
    using this method to copy the DG* tables from [MeatCrow] database into
    [MeatBires] database.


    "Allan Mitchell" <sqldts.com> wrote in message
    news:phx.gbl... 
    like [/ref]
    , [/ref][/ref]
    tables [/ref]
    > don't [/ref][/ref]
    want [/ref][/ref]
    their 
    > >
    > >[/ref]
    >
    >[/ref]


    Joe Guest

  6. #6

    Default Re: Copy tables without using checkbox......how??

    To run the rename procedure simply use

    sp_rename

    Be careful though as you may break procs etc

    --

    ----------------------------
    Allan Mitchell (Microsoft SQL Server MVP)
    MCSE,MCDBA
    www.SQLDTS.com
    I support PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org



    "Joe M" <com> wrote in message
    news:phx.gbl... 
    > like [/ref]
    > , [/ref][/ref]
    than [/ref]
    > tables 
    > > don't [/ref]
    > want [/ref]
    > their 
    > >
    > >[/ref]
    >
    >[/ref]


    Allan Guest

Similar Threads

  1. how I copy a column betwen tables mysql?
    By fhelik in forum MySQL
    Replies: 4
    Last Post: October 3rd, 06:07 AM
  2. Replies: 4
    Last Post: June 13th, 02:29 AM
  3. Tables...Copy/Paste from Word
    By Gunjani in forum Web Design
    Replies: 0
    Last Post: December 4th, 12:45 PM
  4. Replies: 2
    Last Post: August 17th, 01:34 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139