Issue

I have a file master.sql containing many create table ddl's.

master.sql

CREATE TABLE customers (
    customer_id numeric(38) GENERATED BY DEFAULT AS IDENTITY,
    email_address varchar(255) NOT NULL,
    full_name varchar(255) NOT NULL
) ;

CREATE TABLE inventory (
    inventory_id numeric(38) GENERATED BY DEFAULT AS IDENTITY,
    store_id numeric(38) NOT NULL,
    product_id numeric(38) NOT NULL,
    product_inventory numeric(38) NOT NULL
) ;

I want to split this file into separate files-one each for a table. For that i'm using rubin's solution here.

Here is the awk command i used.

awk '/CREATE TABLE/{f=0 ;n++; print >(file=n); close(n-1)} f{ print > file}; /CREATE TABLE/{f=1}'  master.sql

While Executing awk command generate files with table count without any extension. tried to contact using this article

When creating each sql file i want to change file name name for table name.

Eg.

customers.sql
inventory.sql

I'm trying use awk command for get table name form master.sql. Is that possible to getting table name while iteration master.sql.

Is there a way around this ?

Solution

Here is a simple 2 step process:

# Split the files when the string CREATE TABLE is found
csplit master.sql '/CREATE TABLE/'

# Read the first line, extract table name and rename the file
for f in $(ls xx*); 
do 
    table_name=`head -1 $f | awk '{ sub(/.*CREATE TABLE /, ""); sub(/ .*/, ""); print }'`
    mv $f "$table_name.sql"
    echo "Renaming $f to $table_name.sql"; 
done;

Renaming xx00 to customers.sql
Renaming xx01 to inventory.sql

$ ls
customers.sql inventory.sql master.sql

$ cat customers.sql
  CREATE TABLE customers (
    customer_id numeric(38) GENERATED BY DEFAULT AS IDENTITY,
    email_address varchar(255) NOT NULL,
    full_name varchar(255) NOT NULL
) ;

$ cat inventory.sql
CREATE TABLE inventory (
    inventory_id numeric(38) GENERATED BY DEFAULT AS IDENTITY,
    store_id numeric(38) NOT NULL,
    product_id numeric(38) NOT NULL,
    product_inventory numeric(38) NOT NULL
) ;

Answered By - Gab

Answer Checked By - Gilberto Lyons (WPSolving Admin)

Thursday, October 27, 2022

[SOLVED] Split a single sql file into multiple files

Issue

Solution