Professional Web Applications Themes

Copying a table definition and populating it - Microsoft SQL / MS SQL Server

Hi, Probably a simple question but say I have a table. I wish to copy the structure of that table into a new table (in the same database). From there I wish to move certain records from the original table, for example all records before a certain date into this new table. Can anyone point me in the right direction? I'm unsure as to how to do this? Is there a T_SQL command? As regards the second I assume that I would use an INSERT .... SELECT statement to copy the relevent values into the new table, followed by a ...

  1. #1

    Default Copying a table definition and populating it

    Hi,

    Probably a simple question but say I have a table. I wish to copy the
    structure of that table into a new table (in the same database).

    From there I wish to move certain records from the original table, for
    example all records before a certain date into this new table.

    Can anyone point me in the right direction? I'm unsure as to how to do this?
    Is there a T_SQL command?

    As regards the second I assume that I would use an INSERT .... SELECT
    statement to copy the relevent values into the new table, followed by a
    DELETE statement to remove them from the original table? Is this right?

    ANy advice is gratefully received.

    Kind thanks

    Chris Strug


    Chris Guest

  2. #2

    Default Re: Copying a table definition and populating it

    Hi Chris,

    You can copy the structure from a table and the data into a new table at the
    same time with:
    SELECT <column list> INTO new_table FROM old_table WHERE date_column <
    '<some date>'

    You than can separately delete the data from the old table:
    DELETE FROM old_table WHERE date_column < '<some date>'


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Chris Strug" <com> wrote in message
    news:%23MgM8O%phx.gbl... 
    this? 


    Jacco Guest

  3. #3

    Default Re: Copying a table definition and populating it

    Try like this

    Select *
    into NewTable
    from OldTable
    where Date_Col < '01/01/2001'

    HTH,
    Srinivas Sampangi

    "Chris Strug" <com> wrote in message
    news:#MgM8O#phx.gbl... 
    this? 


    sampangi Guest

  4. #4

    Default Re: Copying a table definition and populating it

    You can use SELECT ... INTO to create the new table with data. For
    example:

    SELECT *
    INTO NewTable
    FROM OldTable
    WHERE MyDate < '20030701'

    DELETE FROM OldTable
    WHERE MyDate < '20030701'

    Note that SELECT ... INTO does not copy constraints and indexes.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Chris Strug" <com> wrote in message
    news:%23MgM8O%phx.gbl... 
    this? 

    right? 


    Dan Guest

Similar Threads

  1. Populating Dynamic Table
    By HMOKeefe in forum Coldfusion - Getting Started
    Replies: 10
    Last Post: October 14th, 10:22 PM
  2. Trouble copying data from old table to new table
    By RelentlessMike in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 19th, 05:01 PM

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