Microsoft SQL Server インポート/エクスポート

概要

  • MS SQL Server のテーブルを CSV ファイル(K3フォーマット)としてインポート/エクスポートする。
  • 第1フィールドをプライマリフィールドとして、レコードが未登録か登録済みかを判別し、INSERT または UPDATE を行なう。
  • NULLは「NULL」として書き出される。文字列としてのNULLは「"NULL"」になる。
  • INDEX(オートナンバー)制約があるフィールドには未対応(ハングアップする)。(2011/09/28)

ソースコード

DB_Connect.yaml

# DB接続情報
Driver:   ODBC
Server:   localhost\SQLEXPRESS,1433 # <サーバ名>\<インスタンス名>[,<ポート番号>]
User:     TestUser
Password: "TestPass"  # 記号を含む場合は""で括る。
DB:       TestDB
Options:
    LongTruncOk: 1
    LongReadLen: 8192
DSN:      _Driver_:Driver={SQL Server}; Server={_Server_}; Database=_DB_;

getSchema.pl

すべてを展開すべてを収束
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
-
!
!
|
|
|
|
|
|
|
|
|
|
|
|
|
-
 
 
 
 
 
!
|
|
|
-
-
|
|
!
!
|
|
|
|
|
|
|
|
-
 
 
 
 
!
|
|
|
-
 
 
 
 
-
-
|
|
-
|
!
|
!
!
 
!
|
|
|
|
|
|
|
|
|
|
|
#!/usr/bin/perl
# MSSQLサーバからスキーマを読み出す
 
use strict;
use warnings;
use utf8;
use Encode;
use YAML::Syck;
use FindBin::libs;
use DBIx::NamedParams;
 
$YAML::Syck::ImplicitUnicode = 1;
 
my $charsetConsole    = 'CP932';
my $charsetFile        = 'UTF-8';
 
binmode( STDIN,  ":encoding($charsetConsole)" );
binmode( STDOUT, ":encoding($charsetConsole)" );
binmode( STDERR, ":encoding($charsetConsole)" );
 
my $DB_Connect_yaml = './DB_Connect.yaml';
my $DB_Schema_yaml  = './DB_Schema.yaml';
 
my @SchemaColumns = qw( 
    COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH COLUMN_DEFAULT IS_NULLABLE
);
 
$| = 1;
 
my $Schemas = {};
 
my $DB_Connect = YAML::Syck::LoadFile( $DB_Connect_yaml ) 
    or die( "$DB_Connect_yaml: $!" );
foreach( keys( %{$DB_Connect} ) ){
    $DB_Connect->{'DSN'} =~ s/_${_}_/$DB_Connect->{$_}/;
}
#print Dump( $DB_Connect );
#exit;
 
my $dbh = DBI->connect( 
    "DBI:" . $DB_Connect->{'DSN'}, 
    $DB_Connect->{'User'}, 
    $DB_Connect->{'Password'}, 
    $DB_Connect->{'Options'} 
) or die( "$DBI::errstr\n" );
#print Dump( $dbh );
#exit;
 
my $DBName = $DB_Connect->{'DB'};
my $sql_getTables = qq{
    SELECT        [TABLE_NAME]
    FROM        [INFORMATION_SCHEMA].[TABLES]
    WHERE        [TABLE_CATALOG] = '$DBName' 
                and [TABLE_TYPE]='BASE TABLE' 
    ORDER BY    [TABLE_NAME];
};
my $sth = $dbh->prepare( $sql_getTables ) or die( "$DBI::errstr\n" );
$sth->execute() or die( "$DBI::errstr\n" );
 
do {
    while( my @a = $sth->fetchrow_array ){
        #printf( "%s\n", join( "    ", @a ) );
        $Schemas->{ $a[0] } = [];
    }
}while( $sth->{odbc_more_results} );
$sth->finish;
 
my @TableNames = sort( keys( %{ $Schemas } ) );
#print "Tables: " . scalar(@TableNames) ."\n";
#print join( "\n", DBIx::NamedParams::all_sql_types() ) . "\n";
#exit;
 
$_ = '[' . join( "], [", @SchemaColumns ) . ']';
my $sql_getColumns = qq{
    SELECT    $_
    FROM    [INFORMATION_SCHEMA].[COLUMNS]
    WHERE    [TABLE_CATALOG] = '$DBName' 
            and [TABLE_NAME] = :TableName-WVARCHAR
};
$sth = $dbh->prepare_ex( $sql_getColumns ) or die( "$DBI::errstr\n" );
 
my $count = 0;
foreach my $tbl ( @TableNames ){
    printf( "%d/%d\t%s\n", ++$count, scalar(@TableNames), $tbl );
    my @columns = ();
    $sth->bind_param_ex( { 'TableName' => $tbl } );
    $sth->execute() or die( "$DBI::errstr\n" );
    do {
        while( my @a = $sth->fetchrow_array ){
            #printf( "%s\n", join( "    ", @a ) );
            my $ColumnsInfo = {};
            for( my $i=0; $i<@SchemaColumns; ++$i ){
                $ColumnsInfo->{ $SchemaColumns[ $i ] } = $a[ $i ];
            }
            push( @columns, $ColumnsInfo );
        }
    }while( $sth->{odbc_more_results} );
    $Schemas->{ $tbl } = [ @columns ];
}
$sth->finish;
 
$dbh->disconnect;
 
open( my $fhout, ">:utf8", encode( $charsetConsole, $DB_Schema_yaml ) ) 
    or die( "$DB_Schema_yaml: $!\n" );
print $fhout YAML::Syck::Dump( $Schemas );
#print dump( $Schemas );
close( $fhout );
 
# EOF

getTbl.pl

すべてを展開すべてを収束
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
-
!
!
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-
 
!
!
|
|
|
|
|
|
|
|
|
|
-
-
-
|
|
|
|
-
|
|
-
|
|
!
!
 
!
!
|
|
|
!
|
|
|
|
#!/usr/bin/perl
# MSSQLサーバからデータ読み出し
 
use strict;
use warnings;
use utf8;
use Encode;
use YAML::Syck;
use FindBin::libs;
use DBIx::NamedParams;
use EscapeSlash;
 
$YAML::Syck::ImplicitUnicode = 1;
 
my $charsetConsole    = 'CP932';
my $charsetFile        = 'UTF-8';
 
binmode( STDIN,  ":encoding($charsetConsole)" );
binmode( STDOUT, ":encoding($charsetConsole)" );
binmode( STDERR, ":encoding($charsetConsole)" );
 
my $DB_Connect_yaml = './DB_Connect.yaml';
my $DB_Tables_yaml  = './DB_Schema.yaml';
 
# ""で括る文字列型
my $StrType = join( "|", qw( 
    CHAR GUID UNKNOWN_TYPE VARCHAR WCHAR WLONGVARCHAR WVARCHAR
) );
 
my $DB_Connect = YAML::Syck::LoadFile( $DB_Connect_yaml ) 
    or die( "$DB_Connect_yaml: $!\n" );
foreach( %{$DB_Connect} ){
    $DB_Connect->{'DSN'} =~ s/_${_}_/$DB_Connect->{$_}/;
}
#print dump( $DB_Connect );
 
my $DB_Tables = YAML::Syck::LoadFile( $DB_Tables_yaml ) 
    or die( "$DB_Tables_yaml: $!\n" );
#print dump( $DB_Tables );
my @DB_TableNames = sort( keys( %{$DB_Tables} ) );
 
my $dbh = DBI->connect( 
    "DBI:" . $DB_Connect->{'DSN'}, 
    $DB_Connect->{'User'}, 
    $DB_Connect->{'Password'}, 
    $DB_Connect->{'Options'} 
) or die( "$DBI::errstr\n" );
 
my %DrvTypeToSQLType = $dbh->driver_typename_map();
 
my $count=0;
my $TableNum = scalar(@DB_TableNames);
 
foreach my $tbl ( @DB_TableNames ){
    printf STDERR ( "%d/%d\t%s\n", ++$count, $TableNum, $tbl );
    my @FieldName = map { ${$_ }{'COLUMN_NAME'}; } @{ $DB_Tables->{ $tbl } };
    my @DataType = map { ${$_ }{'DATA_TYPE'}; } @{ $DB_Tables->{ $tbl } };
 
    my $sql = "SELECT [" . join( "],[", @FieldName ) . "] FROM [$tbl];";
    my $sth = $dbh->prepare( $sql ) or die( "$DBI::errstr\n" );
    $sth->execute() or die( "$DBI::errstr\n" );
 
    my $fout = './_DB/' . $tbl . '.txt';
    open( my $fhout, ">:encoding($charsetFile)", encode( $charsetConsole, $fout ) ) 
        or die( "$fout: $!\n" );
 
    printf $fhout ( "\"%s\"\n", join( "\"\t\"", @FieldName ) );
    do {
        while( my @a = $sth->fetchrow_array ){
            for( my $i=0; $i<@a; ++$i ){
                no warnings 'uninitialized';
                $a[$i] =~ s/\x00//g;
                #$a[$i] =~ s/[\r\n    ]+$//;
                $a[$i] = escapeslash( $a[$i] );
                if ( !defined($a[$i]) ){
                    # NULL だったら
                    $a[$i] = "NULL";
                } elsif ( $DrvTypeToSQLType{ $DataType[$i] } =~ /$StrType/ ){
                    # 文字列ならば
                    $a[$i] = '"' . $a[$i] . '"';
                }
            }
            printf $fhout ( "%s\n", join( "\t", @a ) );
        }
    }while( $sth->{odbc_more_results} );
    $sth->finish;
 
    close( $fhout );
}
 
$dbh->disconnect;
 
# EOF

setTbl.pl

すべてを展開すべてを収束
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
-
!
!
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-
 
 
 
-
!
!
|
|
|
!
|
|
!
|
|
|
|
|
|
|
|
|
|
-
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
!
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-
|
|
|
-
|
|
|
-
 
 
!
!
|
-
|
!
|
|
|
|
!
|
!
|
|
|
#!/usr/bin/perl
# MSSQLサーバへデータ書込み
# 2011/09/28	オートナンバー型非対応
 
use strict;
use warnings;
use utf8;
use Encode;
use YAML::Syck;
use FindBin::libs;
use DBIx::NamedParams;
use EscapeSlash;
 
$YAML::Syck::ImplicitUnicode = 1;
 
my $charsetConsole    = 'CP932';
my $charsetFile        = 'UTF-8';
 
binmode( STDIN,  ":encoding($charsetConsole)" );
binmode( STDOUT, ":encoding($charsetConsole)" );
binmode( STDERR, ":encoding($charsetConsole)" );
 
my $DB_Connect_yaml = './DB_Connect.yaml';
my $DB_Tables_yaml  = './DB_Schema.yaml';
 
my $DB_Connect = YAML::Syck::LoadFile( $DB_Connect_yaml ) 
    or die( "$DB_Connect_yaml: $!" );
foreach( %{$DB_Connect} ){
    $DB_Connect->{'DSN'} =~ s/_${_}_/$DB_Connect->{$_}/;
}
#print Dump( $DB_Connect );
 
my $DB_Tables = YAML::Syck::LoadFile( $DB_Tables_yaml ) 
    or die( "$DB_Tables_yaml: $!" );
#print Dump( $DB_Tables );
my @DB_TableNames = keys( %{$DB_Tables} );
 
my $dbh = DBI->connect( 
    "DBI:" . $DB_Connect->{'DSN'}, 
    $DB_Connect->{'User'}, 
    $DB_Connect->{'Password'}, 
    $DB_Connect->{'Options'} 
) or die( "$DBI::errstr\n" );
 
#DBIx::NamedParams::debug_log( 'MSSQL_ImpExp.log' );
 
my %DrvTypeToSQLType = $dbh->driver_typename_map();
 
my $count=0;
my $TableNum = scalar(@DB_TableNames);
my $fNoLocalize = 0;
 
foreach my $tbl ( @DB_TableNames ){
    ++$count;
    print "$count/$TableNum\t$tbl\n";
    my( @field_org, @field_len, @field_type, @field_name_type, @field_prm );
    foreach ( @{ $DB_Tables->{ $tbl } } ){
        my $column_name = ${ $_ }{'COLUMN_NAME'};
        my $data_type = ${ $_ }{'DATA_TYPE'};
        my $name_type = ":$column_name-" . $DrvTypeToSQLType{ $data_type };
        push( @field_org,    $column_name );
        push( @field_type,    $data_type );
        push( @field_len,    ${ $_ }{'CHARACTER_MAXIMUM_LENGTH'} );
        push( @field_name_type, $name_type );
        push( @field_prm, "[$column_name]=$name_type" );
    }
    my $i = 0;
    my %FN2I = map{ s/^"(.*)"$/$1/; $_ => $i++; } @field_org;
    #print Dump( \%FN2I );
 
    my $FieldList  = "[" . join( "],[", @field_org ) . "]";
    my $ValueList  = join( ",", @field_name_type );
    my $UpdateList = join( ",", @field_prm );
 
    my $PrimaryFieldName        = $field_org[0];
    my $PrimaryFieldNameType    = $field_name_type[0];
    my $sql_InsOrUpd = qq{
        IF NOT EXISTS (
            SELECT    $FieldList 
            FROM    [$tbl] 
            WHERE    [$PrimaryFieldName] = $PrimaryFieldNameType
        )
        BEGIN
            INSERT INTO [$tbl]
            ($FieldList)
            VALUES ($ValueList)
        END
        ELSE
        BEGIN
            UPDATE    [$tbl]
            SET        $UpdateList
            WHERE    [$PrimaryFieldName] = $PrimaryFieldNameType
        END;
    };
    #warn "$sql_InsOrUpd\n";
    #exit();
 
    my $sth = $dbh->prepare_ex( $sql_InsOrUpd ) 
        or die( "$DBI::errstr\n" );
 
    my $fin = './_DB/' . $tbl . '.txt';
    open( my $fhin, "<:encoding($charsetFile)", encode( $charsetConsole, $fin ) ) 
        or die( "$fin: $!\n" );
    <$fhin>;    # ヘッダ行を捨てる
    my @DB_Body = <$fhin>;
    close( $fhin );
    chomp( @DB_Body );
 
    foreach my $row ( @DB_Body ){
        #warn $row."\n";
        my @param = split( "\t", $row );
        map{ if( /^NULL$/i ){ undef($_) } else { s/^"(.*)"$/$1/; } } @param;
        for( my $i=0; $i<@param; ++$i ){
            if ( 
                $field_len[ $i ] && ( $field_len[ $i ] > 0 ) 
                && $param[ $i ] && ( length( $param[ $i ] ) > $field_len[ $i ] ) 
            ){
                print "Too long. ID:$param[0], $field_org[$i]: $param[$i]\n";
                $param[ $i ] = substr( $param[ $i ], 0, $field_len[ $i ] );
            }
        }
        my %bind = ();
        for( my $i=0; $i<@field_org; ++$i ){
            $bind{ $field_org[ $i ] } = unescapeslash( $param[ $i ] );
        }
        #print Dump( \%bind ) ."\n";
        $sth->bind_param_ex( \%bind );
        #exit();
        $sth->execute() or die( "$DBI::errstr\n" );
    }
    $sth->finish;
}
$dbh->disconnect;
 
# EOF

makeTable.sql

USE [TestDB]
GO

/****** Object:  Table [dbo].[会員Table]    Script Date: 08/15/2009 04:13:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[会員Table](
	[ID] [int] NOT NULL,
	[姓] [nvarchar](50) NOT NULL,
	[名] [nvarchar](50) NOT NULL,
	[誕生日] [date] NOT NULL,
	[性別] [int] NOT NULL,
	[Email] [nvarchar](50) NULL,
	[電話番号] [nvarchar](50) NULL,
	[郵便番号] [nvarchar](8) NULL,
	[住所1] [nvarchar](max) NULL,
	[住所2] [nvarchar](max) NULL,
	[クラスレベル] [int] NOT NULL,
	[前回ログイン] [datetime] NULL,
	[備考] [nvarchar](max) NULL,
 CONSTRAINT [PK_会員Table] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[性別Table]    Script Date: 08/15/2009 04:13:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[性別Table](
	[ID] [int] NOT NULL,
	[性別] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_性別Table] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[クラスレベルTable]    Script Date: 08/15/2009 04:14:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[クラスレベルTable](
	[ID] [int] NOT NULL,
	[クラスレベル] [nvarchar](30) NOT NULL,
 CONSTRAINT [PK_クラスレベルTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

添付ファイル: fileMSSQL_ImpExp.zip 390件 [詳細]

リロード   新規 下位ページ作成 編集 凍結 差分 添付 コピー 名前変更   ホーム 一覧 検索 最終更新 バックアップ リンク元   ヘルプ   最終更新のRSS
Last-modified: Sun, 04 Dec 2011 19:22:19 JST (2236d)