Professional Web Applications Themes

Copying entire table - Microsoft SQL / MS SQL Server

Hi, I need to copy an entire table on MS SQL DB, along with all indexes and primary key. And I need to do this from an VC++ application that uses ADO to connect to the Server. Table structure may vary. I'm restricted to use ADO only so it'll have to be a T-SQL. any suggestions? Thanks. -- Sudaraka Wijesinghe. iDial Networks Inc. [sudarakaidnw.com]...

  1. #1

    Default Copying entire table

    Hi,

    I need to copy an entire table on MS SQL DB, along with all indexes and
    primary key. And I need to do this from an VC++ application that uses ADO to
    connect to the Server. Table structure may vary. I'm restricted to use ADO
    only so it'll have to be a T-SQL.

    any suggestions?

    Thanks.

    --

    Sudaraka Wijesinghe.
    iDial Networks Inc.
    [sudarakaidnw.com]


    Sudaraka Wijesinghe Guest

  2. #2

    Default Re: Copying entire table

    SriSamp,

    Thanks for the reply.
    But you I can't apply your solutions to my problem because

    [A] - INSERT INTO SELECT can only used after creating the new table. I need
    to create a new table with the same structure too (on the fly) and that's
    where I got stuck.

    [B] - I can't depend on SQL DMO or SQL DBLib because my application should
    run with minimum system requirements. I'm even targeting XP,Me and 98
    platforms.

    Let me know if you have any more suggestions.

    Thanks.

    Sudaraka.



    "SriSamp" <ssampathsct.co.in> wrote in message
    news:Obhy%23GSQDHA.1552TK2MSFTNGP10.phx.gbl...
    > There are two options you can try:
    > [A] You can copy over a table very easily by using a statement like:
    SELECT
    > * INT new_authors FROM authors WHERE 1 = 0. To copy over the indexes, you
    > can play around with the sysindexes table and issue ALTER TABLE commands.
    > [B] Use SQLDMO to script out the table and then re-run the script using a
    > new table name. SQLDMO is a COM library that you can access.
    > --
    > HTH,
    > SriSamp
    > Please reply to the whole group only!
    >
    > "Sudaraka Wijesinghe" <sudarakaidnw.com> wrote in message
    > news:uU6S5CSQDHA.704tk2msftngp13.phx.gbl...
    > > Hi,
    > >
    > > I need to copy an entire table on MS SQL DB, along with all indexes and
    > > primary key. And I need to do this from an VC++ application that uses
    ADO
    > to
    > > connect to the Server. Table structure may vary. I'm restricted to use
    ADO
    > > only so it'll have to be a T-SQL.
    > >
    > > any suggestions?
    > >
    > > Thanks.
    > >
    > > --
    > >
    > > Sudaraka Wijesinghe.
    > > iDial Networks Inc.
    > > [sudarakaidnw.com]
    > >
    > >
    >
    >

    Sudaraka Wijesinghe Guest

  3. #3

    Default Re: Copying entire table

    Comments:
    [A] SELECT * INTO newTable FROM yourTable WHERE 1=0 is a standard T-SQL
    block for "creating" a new table using the same structure as an existing
    table. "newTable" need not exist, it will be created.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!

    "Sudaraka Wijesinghe" <sudarakaidnw.com> wrote in message
    news:uEIi1ETQDHA.3236TK2MSFTNGP10.phx.gbl...
    > SriSamp,
    >
    > Thanks for the reply.
    > But you I can't apply your solutions to my problem because
    >
    > [A] - INSERT INTO SELECT can only used after creating the new table. I
    need
    > to create a new table with the same structure too (on the fly) and that's
    > where I got stuck.
    >
    > [B] - I can't depend on SQL DMO or SQL DBLib because my application should
    > run with minimum system requirements. I'm even targeting XP,Me and 98
    > platforms.
    >
    > Let me know if you have any more suggestions.
    >
    > Thanks.
    >
    > Sudaraka.
    >
    >
    >
    > "SriSamp" <ssampathsct.co.in> wrote in message
    > news:Obhy%23GSQDHA.1552TK2MSFTNGP10.phx.gbl...
    > > There are two options you can try:
    > > [A] You can copy over a table very easily by using a statement like:
    > SELECT
    > > * INT new_authors FROM authors WHERE 1 = 0. To copy over the indexes,
    you
    > > can play around with the sysindexes table and issue ALTER TABLE
    commands.
    > > [B] Use SQLDMO to script out the table and then re-run the script using
    a
    > > new table name. SQLDMO is a COM library that you can access.
    > > --
    > > HTH,
    > > SriSamp
    > > Please reply to the whole group only!
    > >
    > > "Sudaraka Wijesinghe" <sudarakaidnw.com> wrote in message
    > > news:uU6S5CSQDHA.704tk2msftngp13.phx.gbl...
    > > > Hi,
    > > >
    > > > I need to copy an entire table on MS SQL DB, along with all indexes
    and
    > > > primary key. And I need to do this from an VC++ application that uses
    > ADO
    > > to
    > > > connect to the Server. Table structure may vary. I'm restricted to use
    > ADO
    > > > only so it'll have to be a T-SQL.
    > > >
    > > > any suggestions?
    > > >
    > > > Thanks.
    > > >
    > > > --
    > > >
    > > > Sudaraka Wijesinghe.
    > > > iDial Networks Inc.
    > > > [sudarakaidnw.com]
    > > >
    > > >
    > >
    > >
    >
    >

    SriSamp Guest

  4. #4

    Default Re: Copying entire table

    Thanks,

    Method [A] worked!



    "SriSamp" <ssampathsct.co.in> wrote in message
    news:um$InqTQDHA.1552TK2MSFTNGP10.phx.gbl...
    Comments:
    [A] SELECT * INTO newTable FROM yourTable WHERE 1=0 is a standard T-SQL
    block for "creating" a new table using the same structure as an existing
    table. "newTable" need not exist, it will be created.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!

    "Sudaraka Wijesinghe" <sudarakaidnw.com> wrote in message
    news:uEIi1ETQDHA.3236TK2MSFTNGP10.phx.gbl...
    > SriSamp,
    >
    > Thanks for the reply.
    > But you I can't apply your solutions to my problem because
    >
    > [A] - INSERT INTO SELECT can only used after creating the new table. I
    need
    > to create a new table with the same structure too (on the fly) and that's
    > where I got stuck.
    >
    > [B] - I can't depend on SQL DMO or SQL DBLib because my application should
    > run with minimum system requirements. I'm even targeting XP,Me and 98
    > platforms.
    >
    > Let me know if you have any more suggestions.
    >
    > Thanks.
    >
    > Sudaraka.
    >
    >
    >
    > "SriSamp" <ssampathsct.co.in> wrote in message
    > news:Obhy%23GSQDHA.1552TK2MSFTNGP10.phx.gbl...
    > > There are two options you can try:
    > > [A] You can copy over a table very easily by using a statement like:
    > SELECT
    > > * INT new_authors FROM authors WHERE 1 = 0. To copy over the indexes,
    you
    > > can play around with the sysindexes table and issue ALTER TABLE
    commands.
    > > [B] Use SQLDMO to script out the table and then re-run the script using
    a
    > > new table name. SQLDMO is a COM library that you can access.
    > > --
    > > HTH,
    > > SriSamp
    > > Please reply to the whole group only!
    > >
    > > "Sudaraka Wijesinghe" <sudarakaidnw.com> wrote in message
    > > news:uU6S5CSQDHA.704tk2msftngp13.phx.gbl...
    > > > Hi,
    > > >
    > > > I need to copy an entire table on MS SQL DB, along with all indexes
    and
    > > > primary key. And I need to do this from an VC++ application that uses
    > ADO
    > > to
    > > > connect to the Server. Table structure may vary. I'm restricted to use
    > ADO
    > > > only so it'll have to be a T-SQL.
    > > >
    > > > any suggestions?
    > > >
    > > > Thanks.
    > > >
    > > > --
    > > >
    > > > Sudaraka Wijesinghe.
    > > > iDial Networks Inc.
    > > > [sudarakaidnw.com]
    > > >
    > > >
    > >
    > >
    >
    >


    Sudaraka Wijesinghe Guest

Similar Threads

  1. copying a row from one table to another?
    By Jim Michaels in forum MySQL
    Replies: 5
    Last Post: March 8th, 12:23 AM
  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
  3. Copying an entire page
    By Ben in forum Web Design
    Replies: 1
    Last Post: February 23rd, 01:59 PM
  4. Forms...Copying data from one table to another
    By Paul G. in forum Microsoft Access
    Replies: 2
    Last Post: July 16th, 05:21 PM
  5. Beginner - copying data from one table to another
    By Mark Muller in forum Oracle Server
    Replies: 2
    Last Post: December 6th, 09:28 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