Split a single sql file into multiple files-CodePudding

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 ?

CodePudding user response：

Hi you can use something like:

awk 'BEGIN{RS=";"} /CREATE TABLE/{fn = $3 ".sql"; print $0 ";" > fn }' master.sql

the BEGIN block will split the input into sql statements (rather than lines) by using the ; character as a record separator.

then you can print the statement contents if the line matches CREATE TABLE to a file name based on the the third field (the table name)

Note: this may not work so well if there are any sql comments containing ;

CodePudding user response：

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
) ;