Database : Get SQL from a created table with/without data insertion

Kawshik Kumar Paul
3 min readNov 18, 2020

--

Suppose you have some tables in Database (Oracle, MySQL etc) . Sometimes you need the SQL queries of that tables. These SQL queries will be able to make exactly similar tables like these. You can even get the data of these tables by running sql queries. You can also get it without data (only table structure), it’s up to yours. But first of all, we need the SQL of the already generated tables. To do this, follow the steps:

Step1

Open a schema of Database using your SQL editor (PopSQL, DataGrip, Navicat etc).

Step2

Go to Tables. Then you can see the tables of your schema.

After following Step 1 and 2 (in Navicat)

Step3

Select the table / tables of which you want to get the SQL code. To select multiple table, press Ctrl and then click on the tables with your mouse. If you want to select all tables at a time, press Ctrl+A.

After selecting desired tables

Step4

After selecting, click right button of your mouse on any of the selected table/tables. You will get an option Dump SQL File. Click on this.

Step5

You will be provided two options — 1. ‘Structure and Data’ , 2. ‘Structure Only’.

If you select 1, then your SQL will be generated with structure of the selected table/tables and data insertion.

If you select 2, then your SQL will be generated only to get the structure of the selected table/tables.

Choose your option.

Step6

After choosing your option, your SQL file will be generated. You can give it a name and then you can save it to your desired directory.

Step7

Click on Close. Don’t click on Start because I don’t know what will happen after clicking on Start. I’m too lazy to make experiments.

Step8

Go to that directory where you saved the SQL file. The file type should be .sql and your can open it using any SQL Editor or Notepad or Notepad++ or something else…

You can run it to anywhere to make the tables. But a concern here. Look at the SQL commands, it’s like something DOT something. Wait, look at this

Here, it is HR.REGIONS , have you noticed??? Actually if you run this SQL anywhere (I repeat anywhere), the tables will be created there from where you collected the SQL. But we don’t want that, right? So what to do?

Edit the names. Like, use CREATE TABLE REGIONS instead of CREATE TABLE HR.REGIONS.

Step9

Your are done. You sometimes have to make some trial and errors to do these types of things. Don’t worry.

Thank your for reading this article…

--

--

No responses yet