Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Duplicate an Access table entirely through code

Posted on 2004-03-23
8
Medium Priority
?
196 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
Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

 
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 2000 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

Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

Question has a verified solution.

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

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 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