If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Pervasive.SQL > Rename or Copy Existing Table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jun 2006
Posts: 11
Question Rename or Copy Existing Table

I am new to SQL and am trying to either change the extension of an existing table in a pervasive database or copy the table to a new table.

When I added the table, I assumed Pervasive would use the same extension as the rest of the tables, '.DAT'. It defaulted to '.MKD'. For consistency, I want to end up with my new table having the '.DAT' extension.

I tried the following, unsuccessfully, to make a duplicate table with the correct extension:
sql = "SELECT * INTO TableName USING 'TableName.dat' FROM TableName"
sql = "CREATE TABLE TableName USING 'TableName.dat' SELECT * FROM TableName"
(I doubt it would have worked even if Pervasive had recognized the USING keyword because of the redundant table names.)

Is there a way to just rename the existing table and update the DDF and INDEX tables?

Otherwise my next plan is to create the new empty table properly with the USING keyword, and then copying all the records to the new table.

Thanks
Paul
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Dec 2001
Posts: 1,067
You need to use an ALTER TABLE statement.
What version of PSQL are you using?
The following works in PSQL v9 (what I'm using):
ALTER TABLE TableName IN DICTIONARY USING 'TableName.dat'
The statement above will rename the underlying data file to "TableName.dat".
For full documentation of ALTER TABLE, see: http://www.pervasive.com/library/doc...ref-04-07.html
__________________
Mirtheil Software
Certified Pervasive Developer
Certified Pervasive Technician
Custom Btrieve/VB development
http://www.mirtheil.com
I do not answer questions by email. Please post on the forum.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2001
Posts: 1,067
At Create time, you can specify the existing filename by using the "USING" clause. You can also specify the "IN DICTIONARY" clause to create just the DDF entry without affecting the data file.
__________________
Mirtheil Software
Certified Pervasive Developer
Certified Pervasive Technician
Custom Btrieve/VB development
http://www.mirtheil.com
I do not answer questions by email. Please post on the forum.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jun 2006
Posts: 11
Thumbs up

Thanks for the quick answer.
Your SQL statement did it!

I've been wanting to do this for 6 months!
As usual, I was trying to fix a clock with a pocketwatch with a jack hammer.
Thanks for your help.
Paul
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On