Solved

Duplicate an Access table entirely through code

Posted on 2004-03-23
8
190 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why addon domain redirects to the original domain? 8 66
Firewall Speed Issue 6 68
UPLOAD FILE TO Web API USING POST 5 116
Website URL redirection 10 69
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…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

778 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