| Author |
Split a file into repeating record groups (with filename = part of header)
|
|
| rogerjismalm@hotmail.com 2006-09-14, 7:56 am |
| Can you show how to use gawk to split a DDL file into seperate parts?
The number of repeating groups (of ALTER TABLE statements varies for
each table name).
Each filename of the files generated should have <table name> as part
of it. For example alert-FKs.sql
1. A sample of the input file looks like:
ALTER TABLE [dbo].[alert] ADD
CONSTRAINT [alert_fk01]
FOREIGN KEY ([alert_type_id])
REFERENCES [dbo].[alert_type]([alert_type_id])
go
ALTER TABLE [dbo].[alert] ADD
CONSTRAINT [alert_fk02]
FOREIGN KEY ([business_area_id])
REFERENCES [dbo].[business_area]([business_area_id])
go
ALTER TABLE [dbo].[location] ADD
CONSTRAINT [location_fk01]
FOREIGN KEY ([country_id])
REFERENCES [dbo].[country]([country_id])
go
2a. The alert_type-FKs.sql output file should look like:
ALTER TABLE [dbo].[alert_type] ADD
CONSTRAINT [alert_type_fk01]
FOREIGN KEY ([alert_display_category_id])
REFERENCES
[dbo]. [alert_display_category]([alert_display_
category_id])
go
ALTER TABLE [dbo].[alert_type] ADD
CONSTRAINT [alert_type_fk02]
FOREIGN KEY ([business_area_id])
REFERENCES [dbo].[business_area]([business_area_id])
go
2b. The location_FKs.sql output file should look like:
ALTER TABLE [dbo].[location] ADD
CONSTRAINT [location_fk01]
FOREIGN KEY ([country_id])
REFERENCES [dbo].[country]([country_id])
go
Thanks in advance, Roger
PS. I don't have access to a Linux machine and have to run this under
Windows cmd line with GNU utilities
| |
| rogerjismalm@hotmail.com 2006-09-15, 3:56 am |
| Thanks, this solves the first task of naming the output file to be the
<table name>.
However the output only creates one file called 'alert_FKs.sql'. It has
all the contents of the input file (including ALTER TABLE
[dbo].[location] ... lines).
Do you also know how to create (and append to) a new file when it
encounters a different <table name>? Assume I have to match /^ALTER
TABLE$/ and for those lines store $3 in a variable and compare it to
the new $3. IF <> then create/append to the new file ELSE append.
With the sample data a second file should be generated as
'location_FKs.sql' and contain the ALTER TABLE statement for the
location table.
/Roger
| |
| rogerjismalm@hotmail.com 2006-09-25, 7:56 am |
| > Check what you're doing and post again if necessary, but first please
> read http://cfaj.freeshell.org/google/ to learn how to quote properly
> when posting to netnews from google groups.
>
> Ed.
Thanks,
I added a carriage return to end of the sample input file and it now
works great for "create table" and "alter table " statements.
The "create index" statements however have the name of the index as the
second 'word' (from memory) on the second line . Can this test be
incorporated into the same awk script? Or is it easier with another
script?
CREATE NONCLUSTERED
INDEX [dbo].[au_id_ind] ON [dbo].[authors] (au_id)
GO
Can you please describe the awk reserved words/special characters and
logic in the solutions provided? That way I will hopefully be able to
construct my own scripts in the future.
The real input file had preceeding (if not exists) lines which I first
deleted with consecutive sed "/<string>/d". But there might be some
smarter ways with gawk?
IF NOT EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
CREATE NONCLUSTERED
INDEX [dbo].[au_id_ind] ON [dbo].[authors] (au_id)
GO
|
|
|
|