split a single sql file into multiple files

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

Hi,I have a single sql file containing many create table ddl's.Example:
CREATE TABLE sec_afs
(
rpt_per_typ_c char(1) NOT NULL,
rpt_per_typ_t varchar(20) NULL,
LOCK ALLPAGES
go
EXEC sp_primarykey 'sec_afs', rpt_per_typ_c
go
GRANT SELECT ON sec_afs TO developer_read_only
go

CREATE TABLE dbo.sec_iccc
(
user_nt_id_c char(16) NOT NULL,
unit_id_c char(4) NOT NULL
)
LOCK ALLPAGES
go
GRANT SELECT ON sec_iccc TO developer_read_only
go

CREATE TABLE sac_recon(
rec_number int NOT NULL,
rec_grp_number int NOT NULL,
)
go

I want to split this file into separate files-one each for a table,i have a blank line before every "create table"statement.So may be I can create a new file once every "create table" is encountered.Please tell me how to do this in unix.-Thanks

2

If you have csplit try

csplit mynewfile myfile.sql '^$'
3

Hi Jim,Thanks for replying,
I tried the following:
$ csplit all_tables_Production '^$'
csplit: ^$: bad line number
$ csplit all_tables_Production %^$%
csplit: %^$ - out of range
$ csplit all_tables_Production `^$`
ksh: ^$: not found
but it doesn't work.

4

If the blank line between CREATE TABLE paragraphs is always present this should suffice:

awk '/CREATE TABLE/{ n++; print > (f="out_" n); close(f)}' RS=  sql_file

Otherwise use,

awk '/CREATE TABLE/{f=0 ;n++; print >(file="out_" n); close("out_" n-1)} f{ print > file}; /CREATE TABLE/{f=1}'  sql_file
5

Hi Rubin,
Thanks your solution works!
awk '/CREATE TABLE/{ n++; print > (f="out_" n); close(f)}' RS= alltables
but,i have close to 183 "create table" scripts in my file and the above crashes out with the error:
awk: too many output files 10
record number 11

The second solution GIVES:
awk '/CREATE TABLE/{f=0 ;n++; print >(file="out_" n); close("out_" n-1)} f{ print > file}; /CREATE TABLE/{f=1}' alltables
awk: syntax error near line 1
awk: bailing out near line 1

Please let me know -how can we increase the size from 10 to 200.
Thanks

6

I see ..., that's not an awk issue, but an OS one. I doubt though that the limit of files in one dir is only 10, it's got to be way more than that.
I tested the codes in Solaris, and they bombed out only after ~ 55000 files were created in one dir, they were all in good shape,and my test file had ~1,500,000 lines.

Well in this case I'd suggest to split the file in smaller chunks ( see man split pages of your OS ) to the size that the codes would not fail, create the same number of directories as the number of chunks created, move these chunks to the newly created dirs, and run the given codes separately in each of these directories created.

BTW if you're on Solaris use nawk.