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

  • Grandstream Phone Vulnerability Security Issue Remote Backdoor Connection to 207.246.119.209:3478
  • Linux How to Check Which NIC is Onboard eth0 or eth1 Ubuntu Centos Debian Mint
  • VboxManage VirtualBox NAT Network Issues Managment Troubleshooting
  • Dell PowerEdge Server iDRAC Remote KVM/IP Default Username, Password Reset and Login Information Solution
  • Nvidia Tesla GPUs K40/K80/M40/P40/P100/V100 at home/desktop hacking, cooling, powering, cable solutions Tutorial AIO Solutions
  • Stop ls in Linux Debian Mint CentOS Ubuntu from applying quotes around filenames and directory names
  • Thunderbird Attachment Download Error Corrupt Wrong filesize of 29 or 27 bytes Solution
  • Generic IP Camera LAN Default IP Settings DVR
  • Ubuntu Debian Mint Linux How To Update Initramfs Manually update-initramfs
  • Enable Turbo Mode for CPU Ubuntu Linux Mint Debian Redhat
  • docker / kubernetes breaks Proxmox QEMU KVM Bridge VMs
  • How To Change Storage Location in Docker.io
  • RTL8812BU and RTL8822BU Linux Driver Ubuntu Setup Archer T3U Plus
  • Kazam video blank/high size and not working when recording solution
  • Cisco UC CME How To Enable Licensed Features
  • from pip._internal.cli.main import main File "/usr/local/lib/python3.5/dist-packages/pip/_internal/cli/main.py", line 60 sys.stderr.write(f"ERROR: {exc}") from pip._internal.cli.main import main File "/usr/local/lib/python3.5/dist-packag
  • ModuleNotFoundError: No module named 'pip._internal' solution python
  • grub blank screen how to manually boot kernel and initrd Linux Ubuntu Debian Centos won't boot solution
  • Cisco Switch / Router How To Restore Factory Default Settings
  • Cisco 2900 3900 Router Password Reset How To Reset Enable Password