Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Copy table

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    We have some queries in Access 2007 linked to some tables in an SQL database, which we use with SQL 2005 Management Studio (MS). We need a new table with similar fields to an existing table. Is there is an easy way to copy an existing table (i.e., the structure, with or without the data), either in Access or in SQL MS? There does not seem to be a simple function for copying a table in SQL MS, and if we copy the table as a linked table in Access, it appears in Access like the other linked tables but does not appear in SQL MS.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Murgatroyd' post='764527' date='10-Mar-2009 05:58'][/quote]
    In SQL Server, execute an SQL statement

    SELECT * FROM OldTable INTO NewTable WHERE False

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    What does the WHERE False mean?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='patt' post='764546' date='10-Mar-2009 09:35']What does the WHERE False mean?[/quote]
    It is a make-table query that will copy all records satisfying the condition into a new table.
    But the condition is always false, so no records will be copied. The end result is a table with the same structure as the original but without records.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='764547' date='10-Mar-2009 19:49']It is a make-table query that will copy all records satisfying the condition into a new table.
    But the condition is always false, so no records will be copied. The end result is a table with the same structure as the original but without records.[/quote]
    Thank you, clever.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. When I executed the query "SELECT * FROM Client INTO Prospect WHERE False", I got an error message.
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'INTO'.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Murgatroyd' post='764709' date='10-Mar-2009 23:19']Thanks for your reply. When I executed the query "SELECT * FROM Client INTO Prospect WHERE False", I got an error message.
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'INTO'.[/quote]
    Sorry, it was air code. It should have been

    SELECT * INTO Prospect FROM Client WHERE False

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    When I tried "SELECT * INTO Prospect FROM Client WHERE False", I got another error message.
    Msg 4145, Level 15, State 1, Line 1
    An expression of non-boolean type specified in a context where a condition is expected, near 'False'.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Murgatroyd' post='764712' date='10-Mar-2009 23:26'][/quote]
    I can't test on SQL Server, I hope that someone who does can help you.

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    I tried replacing "WHERE False" with "WHERE 1=2" and that worked. However, the new table does not appear in Access; i.e., it is not listed in the Linked Table Manager.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Murgatroyd' post='764716' date='10-Mar-2009 23:41'][/quote]
    You'll have to create the link first using File | Get External Data | Link Tables...

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    OK the new table is now linked, thanks.

    1. The original table (Client) has the first field (ClientID) set as a primary key, and SQL MS lists two keys for it (PK_Client and IX_Client). In the new table (Prospect), I renamed the first field (ProspectID) and set it as a primary key, and SQL MS now lists one key (PK_Prospect). Should I have a second key (IX_Prospect) corresponding to the second key in the original table?

    2. When a record is added to the original table, the number in the ClientID key field is incremented automatically. Do I need to do anything to make the ProspectID field in the new table work the same way?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You should not have two indexes on the same field; it's superfluous. So remove the second index IX_Client.

    You should be able to see either in Access or in SQL Server whether ProspectID is an autoincrement field. As I already noted, I don't have SQL Server so I don't know the details.

  14. #14
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    OK thanks.

    PS: It seems that the second index is for another field, which is used for lookups and needs to be unique.

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='Murgatroyd' post='764527' date='09-Mar-2009 22:58']... There does not seem to be a simple function for copying a table in SQL MS, and if we copy the table as a linked table in Access, it appears in Access like the other linked tables but does not appear in SQL MS.[/quote]
    Sorry to be late to the party, but there is a pretty simple way to copy a table in SQL Server using the Management Studio. Right-click on the database where you want to copy the table, and then choose Tasks and Import Data or Export Data. Either will bring up the "SQL Server Import and Export Wizard" - the rest of it is fairly intuitive - you chose the kind of data source (SQL Server Native Client), the Server (from the dropdown) and the database (also a dropdown) and the data destination (it can be the same database), and then specify the table or tables you want, and any manipulation of the data you want to perform. It may take a few attempts to get it right, but it's well worth the trouble if you are working with SQL Server databases. Of course you still have to link to the table from Access once you have copied it.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

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