Create Table with structure of another existing table

Create Table with structure of another existing table

Ok, this has many valid uses such as if you need to create backups or the ability to undo changes, you would want to create new tables with the same structure as another. Here is how you do it.
[i:7ed9581493]
Also remember Indexes such as PRIMARY KEYS [b:7ed9581493]will not be preserved[/b:7ed9581493] so you will have to readd them manually.[/i:7ed9581493]

[code:1:7ed9581493]CREATE TABLE NewName AS SELECT * FROM TableStructureYouWantToCopy;[/code:1:7ed9581493]


Another way to do the same thing (although in MySQL 3.23 it doesn't work - probably version 4+ will support it) is:

[code:1:7ed9581493]CREATE TABLE NewName LIKE TableStructureYouWantToCopy[/code:1:7ed9581493]


[b:7ed9581493]Restore PRIMARY KEY[/b:7ed9581493]

As I mentioned above you will need to recreate any Indexes/PRIMARY KEYS

Here is an example of readding a PRIMARY KEY with an AUTO_INCREMENT field:

[b:7ed9581493]First make the field NOT NULL[/b:7ed9581493]

[code:1:7ed9581493]
ALTER TABLE tableName MODIFY FieldNAME INT(18) NOT NULL;
[/code:1:7ed9581493]

[b:7ed9581493]Create The Primary Key[/b:7ed9581493]
[code:1:7ed9581493]ALTER TABLE tableName ADD CONSTRAINT PRIMARY KEY(FieldName);[/code:1:7ed9581493]

[b:7ed9581493]ADD AUTO_INCREMENT[/b:7ed9581493]
[quote:7ed9581493]ALTER TABLE tableName MODIFY FieldNAME INT(18) NOT NULL AUTO_INCREMENT;[/quote:7ed9581493]

If there is an easier way then this then please let me know :)



Tags:

existing, tablecreate, tableok, valid, backups, undo, indexes, primary, preserved, readd, manually, newname, select, tablestructureyouwanttocopy, mysql, doesn, restore, recreate, readding, auto_increment, null, alter, tablename, modify, fieldname, int, constraint,

Latest Articles

  • How to allow SSH root user access in Linux/Debian/Mint/RHEL/Ubuntu/CentOS
  • Ansible Tutorial - Playbook How To Install From Scratch and Deploy LAMP + Wordpress on Remote Server
  • Ceph Install Errors on Proxmox / How To Fix Solution
  • Proxmox Update Error https://enterprise.proxmox.com/debian/pve bullseye InRelease 401 Unauthorized [IP: 144.217.225.162 443]
  • QEMU/KVM How to Hot-add A Virtual Disk .raw/.qcow2 via QEMU Monitor Commands
  • Proxmox How To Enable Ceph Distributed Storage Cluster with OSD and Pools
  • pulseaudio issue on QEMU/KVM guest VM when microphone is replugged/unplugged pulseaudio: pa_threaded_mainloop_lock failed pulseaudio: Reason: Invalid argument
  • Ubuntu Linux Mint - Volume Control Stopped Working
  • Proxmox Services Won't Start Failed to start The Proxmox VE cluster filesystem. Proxmox VE firewall. PVE Status Daemon. Proxmox VE scheduler. PVE Cluster HA Resource Manager Daemon. PVE Local HA Resource Manager Daemon.
  • Proxmox Guide FAQ / Errors / Howto
  • Virtualbox Vbox Issue Cannot Enable Nested Virtualization Button is Grayed/Greyed Out and Unclickable HowTo Solution
  • Virtualbox VBOX Howto Port Forward To Guests
  • Linux Ubuntu Debian Centos Mint - How To Check if Intel VT-x or AMD-V Hardware Virtualization is Enabled?
  • Linux Howto Zip Multiple Files and Directories
  • Windows Cannot Format USB drive Device Media is Write Protected Error Solution
  • Linux Mint 20 cannot install snapd missing solution
  • Virtualbox VBOX How To Install Guest-Utils/GuestUtils so drag and drop and clipboard works Ubuntu Mint Debian Linux
  • How to install Kubernetes with microk8s and deploy apps on Debian/Mint/Ubuntu Linux
  • vi how to delete everything to the end of the line or the rest of the line from the cursor
  • Cisco Howto Configure Console Port/Terminal/Comm Server with Async Cable Setup