Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Duplicate an Access table entirely through code

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
jvosika
Asked:
jvosika
  • 2
  • 2
  • 2
  • +2
1 Solution
 
sandy12879Commented:
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
 
sandy12879Commented:
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
 
jvosikaAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
anandkpCommented:
try DTS in SQL - it shld do the job for u !
0
 
mrichmonCommented:
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
 
jayeshshahCommented:
try this sql

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


Regards

JBS
0
 
jayeshshahCommented:
this shall just clone the table without any data. :-)
0
 
jvosikaAuthor Commented:
Perfect! That did the trick. Thank you all for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now