Solved

Duplicate an Access table entirely through code

Posted on 2004-03-23
8
193 Views
Last Modified: 2013-12-24
Is there an easy way with Cold Fusion/SQL to copy a table's structure and datatypes, but not the information, to a new table with a new name?

Thank you!

Jim Vosika
http://www.wallclocksrus.com
0
Comment
Question by:jvosika
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 4

Expert Comment

by:sandy12879
ID: 10657748
select *
into <other table name>
from <tablename>
where  condition ......

make sure that th option 'select into/bulkinsert is enabled.  Which you can do like this:

exec sp_dboption <databasename>, 'select into/bulkcopy', true
0
 
LVL 4

Expert Comment

by:sandy12879
ID: 10657822
Or may be u can try this also

create table New_table  as select * from Old_Table where  condition.....

Please let me know


Regards
Sandy
0
 

Author Comment

by:jvosika
ID: 10657907
I tried both options and neither did it. For the second one here is my code:

<cfquery datasource="dbTest" name="createtable">
create table 12345 as select * from blank where id=1
</cfquery>

...and here is the error I get:

Error Occurred While Processing Request  
Error Executing Database Query.  
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.  
 
1 : <cfinclude template="header.cfm">
2 : <cfoutput>
3 : <cfquery datasource="dbTest" name="createtable">
4 : create table 12345 as (select * from blank where id=1)
5 : </cfquery>

 
 
0
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 
LVL 17

Expert Comment

by:anandkp
ID: 10657998
try DTS in SQL - it shld do the job for u !
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10658745
One thing you need to verify is in the CFAdmin make sure that you have the permissions set in your datasource to allow the creation of tables otherwise it will never work.
0
 
LVL 5

Accepted Solution

by:
jayeshshah earned 500 total points
ID: 10660140
try this sql

<cfquery datasource="dbTest" name="createtable">
    select * into 12345 from blank  where 1<>1
</cfquery>


Regards

JBS
0
 
LVL 5

Expert Comment

by:jayeshshah
ID: 10660151
this shall just clone the table without any data. :-)
0
 

Author Comment

by:jvosika
ID: 10668960
Perfect! That did the trick. Thank you all for your help.
0

Featured Post

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question