2000).
Is there an easy way to generate a series of insert statements from my
database so I can transfer this text file to my remote system. If possible
I like to be able to exclude data with a where statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004http://vyaskn.tripod.com/code.htm#inserts
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
quote:
> I have data in a table that I wish to transfer to a remote system (both
SQL
quote:
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
quote:|||Here's a PERL script for generating SQL INSERT statements.
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>
The script deals with all data types, except image and
sql_variant, but including the user defined data types.
#############Begin script###########
# Created by: Linchi Shea
use strict;
use Data::Dumper;
use Win32::ODBC;
use Getopt::Std;
my %opts;
getopts('S:d:t:', \%opts);
my ($server, $dbName, $tbName) = ($opts{S}, $opts{d}, $opts
{t});
(defined $server && defined $dbName && defined $tbName) or
printUsage();
Main: {
my $connStr = "Driver={SQL Server};Server=$server;" .
"Database=$dbName;Trusted_Connection=yes";
my $conn = new Win32::ODBC($connStr) or
die "***Err: " . Win32::ODBC::Error();
my ($columnRef, $attribRef) = getColumnProperties
($tbName, $conn);
my $sql = constructINSERT($columnRef, $attribRef,
$conn);
print $sql;
$conn->Close();
} # Main
############################
sub getColumnProperties {
my($tbName, $conn) = @._;
my @.columns;
my %attrib;
if (! $conn->Sql("select * from $tbName where 1=2") ) {
1 while $conn->FetchRow();
# first get the data type for each column
my @.fields = $conn->FieldNames();
%attrib = $conn->ColAttributes($conn-
quote:
>SQL_COLUMN_TYPE_NAME, @.fields);
# in case the data type is user defined, we need
# the real data type to help us decide how to handle
# the retrieved data in an INSERT statement
foreach my $field (@.fields) {
if (! $conn->Sql("sp_help '$attrib{$field}'") )
{
while($conn->FetchRow()) {
my ($type) = $conn->Data("Storage_type");
$attrib{$field} = $type;
}
}
if ($attrib{$field} =~ /^(image|sql_variant)$/i) {
die "***Err: data type $attrib{$field} not
supported.\n";
}
push @.columns, $field if lc($attrib{$field})
ne 'timestamp';
}
}
else {
die "***Err: failed to run select * from $tbName
where 1=2.\n";
}
return (\@.columns, \%attrib);
} # getColumnProperties
########################
sub constructINSERT {
my($columnRef, $attribRef, $conn) = @._;
(scalar @.$columnRef && scalar %$attribRef) or
die "Err: \$columnRef or \$attribRef is empty.\n";
my $sql;
if (! $conn->Sql("select * from $tbName") ) {
# now get the data values for each row
while ($conn->FetchRow()) {
$sql .= "INSERT $tbName (" . join(',',
@.$columnRef) . ")\n";
my @.values = ();
my %data = $conn->DataHash();
# decide how to handle the VALUE clause of the
INSERT
foreach my $column (@.$columnRef) {
# the values of these data types can be used
as is
if ($attribRef->{$column}
=~ /int|smallint|bigint|tinyint|
bit|decimal|numeric|money|
smallmoney|float|real
/ix) {
if (defined $data{$column}) {
push @.values, $data{$column};
}
else {
push @.values, 'NULL';
}
}
# the values of these types must be quoted
with a pair of
# single quotation marks
elsif ($attribRef->{$column}
=~ /datetime|smalldatetime|
char|varchar|nchar|nvarchar|
text|ntext|uniqueidentifier
/ix) {
if (defined $data{$column}) {
$data{$column} =~ s/'/''/g;
push @.values, "'$data{$column}'";
}
else {
push @.values, 'NULL';
}
}
# the binary data must be converted to a HEX
string format
elsif ($attribRef->{$column}
=~ /binary|varbinary
/ix) {
if (defined $data{$column}) {
push @.values, '0x' . unpack("H*", $data
{$column});
}
else {
push @.values, 'NULL';
}
}
else {
print "***Assert: invalid code path. Skip
this row.\n";
next;
}
}
$sql .= "VALUES (" . join(',', @.values) . ")\n";
}
}
return $sql;
} # construtcINSERT
###################
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl GeneratedataInserts.pl -S <SQL server or
instance>
-d <database name>
[ -t <table name> ]
--Usage--
exit;
} # printUsage
##############End script###############
quote:
>--Original Message--
>I have data in a table that I wish to transfer to a
remote system (both SQL
quote:
>2000).
>Is there an easy way to generate a series of insert
statements from my
quote:
>database so I can transfer this text file to my remote
system. If possible
quote:
>I like to be able to exclude data with a where statement.
>Regards
>Jeff
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.564 / Virus Database: 356 - Release Date:
19/01/2004
quote:|||If that's your sole purpose, you can get by more easily with
>
>.
>
1. creating a separate db
2. put the data in a table in that db
3. backup that db
4. do the reverse on the remove server.
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
quote:
> I have data in a table that I wish to transfer to a remote system (both
SQL
quote:
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
quote:
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>
No comments:
Post a Comment