Table of Contents

概要

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

ソースコード

MSSQL_ImpExp.zip

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

#!/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( "\t", @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( "\t", @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

#!/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\t]+$//;
				$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

#!/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

Link

MS SQL Server
Perl/EscapeSlash
Perl/DBIx-NamedParams
Perl/キーワード置換
SQL Server - 情報スキーマビュー
- TABLES
- COLUMNS
sys.columns
データ型
MDB生成／テーブル定義取得ツール
「IDENTITY属性を持つ列を探すには」をお題にやったことをBlogる @ CAMUS’s BLACKBOARD
```
EXEC sp_MShelpcolumns [テーブル名]
```

MSSQL_ImpExp

概要

ソースコード

DB_Connect.yaml

getSchema.pl

getTbl.pl

setTbl.pl

makeTable.sql

Link

Perl