Q9.14: ddl_insert.pl
In order to use this script you must have Sybperl installed -- see Q9.4 for more information.
<![CDATA[
#!/usr/local/bin/perl
# Author: Vincent Yin (umyin@mctrf.mb.ca) Aug 1994 Last Modified: May 1996
chomp($basename = `basename $0`);
$usage = <<EOF;
USAGE
$basename database userid passwd pattern [ pattern... ]
DESCRIPTION
Prints isql scripts that would insert records into the
tables whose names match any of the patterns in command line. In
other words, this program reverse engineers the data in a given
table(s). Roughly, it `select * from <table>', analyses the data
and table structure, then prints out a bunch of
insert <table> values ( ... )
statements that would re-populate the table. It's an alternative
to `bcp'. `bcp' has its limitations (e.g. one often needs to turn on
'select into/bulk copy' option in the database before running bcp.)
Table names are matched to <pattern> with Transact-SQL's LIKE clause.
When more than one pattern is specified on command line, the LIKE
clauses are OR'ed. In any case, the LIKE clause(s) is logged to
the beginning of the output as a comment, so that you'll see how this
program interprets the command line.
The SQL script is printed to stdout. Since it only prints out the SQL
but doesn't submit it to the SQL server, this procedure is safe to run.
It doesn't modify database in any way.
EXAMPLES
To print this usage page:
% $basename
To print SQL that populates the table master..sysobjects and systypes:
% $basename master userid passwd 'sysobjects' 'systypes'
To print SQL that populates all system tables in master db:
% $basename master userid passwd 'sys%'
BUGS
Embedded line breaks in strings are allowed in Sybase's isql, but not
allowed in SQLAnywhere's isql. So this script converts embedded line
breaks (both DOS styled and UNIX styled) to blank characters.
EOF
$batchsize = 10; # The number of INSERTs before a `go' is issued.
# This is to make the output compact.
# .................... No change needed below this line ........................
use Sybase::DBlib;
die $usage unless $#ARGV >= 3;
($db, $user, $passwd, @pattern) = @ARGV;
$likeclause = &sql_pattern_to_like_clause('name', @pattern);
print <<EOF;
-- This script is created by $0.
-- It would generate INSERT statements for tables whose names match the
-- following pattern:
/* $likeclause
*/
set nocount on
go
EOF
$dbh = new Sybase::DBlib $user, $passwd;
$dbh->{dbNullIsUndef} = 1;
$dbh->dbuse($db);
# Get the list of tables.
$tablelist = $dbh->sql("select name from sysobjects
where type in ('S','U') and $likeclause
order by name
");
foreach $tableref (@$tablelist) {
$table = @$tableref[0];
print "\n\n/*.............. $table ...............*/\n";
print "-- ", `date`, "\n";
print "declare \@d datetime\n";
print "select \@d = getdate()\n";
print "print ' %1! $table', \@d\ngo\n\n";
print "truncate table $table -- Lookout !!!!!!\ngo\n\n";
$dbh->dbcmd("select * from $table");
$dbh->dbsqlexec;
$dbh->dbresults;
while (@row = $dbh->dbnextrow()) {
print "insert $table values(";
for ($i=0; $i <= $#row; $i++) { # build the INSERT statement
# Analyse datatype to decide if this column needs to be quoted.
$coltype = $dbh->dbcoltype($i+1);
if (!defined($row[$i])) {
print 'NULL'; # Never quote NULL regardless of datatype
}
elsif ($coltype==35 or $coltype==39 or $coltype==47 or
$coltype==58 or $coltype==61 or $coltype==111 ){
# See systypes.type/name for explanation of $coltype.
$row[$i] =~ s/\r|\n/ /g; # Handles both DOS and UNIX line breaks
$row[$i] =~ s/"/""/g; # Stuff double quotes
print "\"" . $row[$i] . "\"";
}
else {
print $row[$i];
}
print ", " unless $i == $#row;
}
print ")\n"; # wrap up the INSERT statement.
# print `go' at every $batchsize interval.
print "go\n" unless $dbh->DBCURROW % $batchsize;
}
print "\ngo\n\n"; # print a `go' after the entire table is done.
print "-- ### End for $table: rowcount = ", $dbh->DBCURROW, "\n";
}
# ................................. sub ........................................
sub main'sql_pattern_to_like_clause {
local($field_name, @pattern) = @_;
$like_clause = "\t( 1 = 0 ";
foreach (@pattern) {
$like_clause .= "\n or $field_name like '" . $_ . "' ";
}
$like_clause .= "\n\t) \n";
}
]]>