Windows Powershell:我可以将大型 SQL 文件拆分为包含一个 db 对象的小文件吗 [英] Windows Powershell: can I split a large SQL file into small files containing one db object

问题描述

我们使用 ERWin 进行数据建模，它为所有数据库对象(例如表和视图)生成大型 SQL DDL 文件.我想把这个大文件分成小文件，每个文件包含一张桌子，一个视图，例如.

We are using ERWin for data modeling and it generates a large SQL DDL files for all database objects, such as tables and views. I am thinking to break this big file up into small ones, each containing one table, one view, for instance.

目标是在部署 SQL DDL 时减少手动工作，将每个表复制/粘贴到执行窗口中，因为在开发中，通常只有少数表会发生更改.

The goal is to reduce the manual work when deploying SQL DDLs with copy/paste each table into an execution windows because in development, usually only a few tables get changed.

我看到每个 SQL 都以分号;"结尾所以希望PowerShell有办法找到这个分隔符，把大的SQL文件分成很多小文件.

I see that each SQL ends with a semi-colon, ";" so I hope there is a way in PowerShell to find this delimiter and separate the large SQL file into many small ones.

非常感谢您的帮助！

这是一个包含两个表的示例 SQL 代码.

Here is one sample SQL code with two tables.

CREATE SET  TABLE TABLE_1,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    NO FALLBACK,
    CHECKSUM = DEFAULT
(

    CAT_ID           INTEGER NOT NULL ,
    CAT_SECTION_ID   INTEGER NOT NULL ,
    CREATE_DTTM          TIMESTAMP(6) NOT NULL ,
    UPDATE_DTTM          TIMESTAMP(6) NULL 
)
     UNIQUE PRIMARY INDEX UPI_CAT_SECTION
     (
            CAT_ID,
            CAT_SECTION_ID
     );

CREATE SET  TABLE TABLE_2,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    NO FALLBACK,
    CHECKSUM = DEFAULT
(

    CH_NUM          VARCHAR(8) NOT NULL ,
    CH_YEAR   SMALLINT NOT NULL ,
    CREATE_DTTM          TIMESTAMP(6) NOT NULL ,
    LAST_UPDATE_DTTM     TIMESTAMP(6) NULL 
)
     UNIQUE PRIMARY INDEX UPI_TABLE_2
     (
            CH_NUM,
            CH_YEAR,
                 );

推荐答案

这里有一个脚本，它将把你的大文件分成小文件，在每行包含一个分号之后开始一个新文件.它根据分号后面的第一个非空行生成文件名(在您的示例中，两个文件名将是 CREATE_SET_TABLE_CATALOG.SQL 和 CREATE_SET_TABLE_CHASSIS.SQL.

Here's a script that will split your large file into smaller files, starting a new file after each line that contains a semi-colon. It generates the filename based on the first non-blank line following the semicolon (in your example, the two filenames would be CREATE_SET_TABLE_CATALOG.SQL and CREATE_SET_TABLE_CHASSIS.SQL.

$buffer = @()
$filename = $null
Get-Content '.\LARGEFILE.SQL' | ForEach-Object {
    $line = $_
    $buffer += @($line)

    if ([String]::IsNullOrWhiteSpace($filename)) {
        $filename = $line.Trim().Replace(',','') -replace '[^a-zA-Z0-9_.]+', '_'
    }

    if ($line.Contains(';')) {
        $buffer | Out-File "$filename.SQL" -Encoding utf8
        $buffer = @()
        $filename = $null
    }
}

这篇关于Windows Powershell:我可以将大型 SQL 文件拆分为包含一个 db 对象的小文件吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！