Here is a quick hack with Perl to generate insert statements to reproduce data already exist in a given table. It is similar to results mysqldump generates.

A few things worth mentioning here:
1. This script uses a text file, TableList, that contains a list of tables that you want insert statements created. Each line in the list contains the database name and table name, separated by space(s). This is a quick hack, one could easily pass the file name as an input;
2. Obviously the connection information needs to be modified for your own needs;
3. The script does escape single quote in character fields properly;
4. Use
perl ScriptName.pl > c:\MyFolder\MyTableInsert.sql
to collect the results into c:\MyFolder\MyTableInsert.sql file.
5. Thanks to Summit Amar and people who commented on this article for sql script ideas.
6. Update: modified the script so it handles identity field if it is present in a table.

By the way, Microsoft’s new scripting language, PowerShell, borrowed a lot of good stuff from Perl and Unix shell scripting, which is pretty powerful. My friends Yan Pan and Ananthakumar Muthusamy wrote a book, Microsoft SQL Server 2008 Administration with Windows PowerShell, which I helped to tech-edit. If you are looking to learn more automation and scripting tricks with PowerShell, definitely check out that book, which will come out next month.

[sourcecode language=”perl”]
#!/usr/bin/perl
use strict;
use Win32::SqlServer;

# Get server name from the command line
# my $ServerName = shift or die “Please enter server name as the first only parameter needed”;
# my $TableName = “authors”;

open(MYDATA, “RefList.txt”) or
die(“Error: cannot open file ‘RefList.txt’\n”);

while( my $line = ){
$line =~ /(\w+)\s+(\w+)/;
my $db;
my $table;

if ($db ne $1){
print “—–Tables in $1—–\n”;
}
$db = $1;
my $sqlsrv = sql_init(“.”, “sa”, “s3cr3t”, $1);

my $HasIdentityColumn = $sqlsrv->sql_one(“select objectproperty(object_id(‘$2’), ‘TableHasIdentity’)”);
if ($HasIdentityColumn == 1) {
print “SET IDENTITY_INSERT $2 ON\n”;
}
# Our SQL statement.
my $stmnt = < 0
begin
–print ‘Table ‘ + $2 + ‘ not found, processing skipped.’
close curscol
deallocate curscol
return
END

WHILE \@\@FETCH_STATUS=0
BEGIN
IF \@dataType in (‘varchar’,’char’,’nchar’,’nvarchar’)
BEGIN
SET \@stringData=\@stringData+””+”’+isnull(””’+””’+REPLACE(‘+\@colName+’,””’+””’,””’+””’+””’+””’)+””’+””’,”NULL”)+”,”+’
END
ELSE
if \@dataType in (‘text’,’ntext’) –if the datatype is text or something else
BEGIN
SET \@stringData=\@stringData+””””’+isnull(REPLACE(cast(‘+\@colName+’ as varchar(2000)),””’+””’,””’+””’+””’+””’),””)+”””,”+’
END
ELSE
IF \@dataType = ‘money’ –because money doesn’t get converted from varchar implicitly
BEGIN
SET \@stringData=\@stringData+”’convert(money,”””+isnull(cast(‘+\@colName+’ as varchar(200)),”0.0000”)+”””),”+’
END
ELSE
IF \@dataType=’datetime’
BEGIN
SET \@stringData=\@stringData+”’convert(datetime,’+”’+isnull(””’+””’+convert(varchar(200),’+\@colName+’,121)+””’+””’,”NULL”)+”,121),”+’
END
ELSE
IF \@dataType=’image’
BEGIN
SET \@stringData=\@stringData+””””’+isnull(cast(convert(varbinary,’+\@colName+’) as varchar(6)),”0”)+”””,”+’
END
ELSE –presuming the data type is int,bit,numeric,decimal
BEGIN
–SET \@stringData=\@stringData+””””’+isnull(cast(‘+\@colName+’ as varchar(200)),”0”)+”””,”+’
–SET \@stringData=\@stringData+”’convert(datetime,’+”’+isnull(””’+””’+convert(varchar(200),’+\@colName+’,121)+””’+””’,”NULL”)+”,121),”+’
SET \@stringData=\@stringData+””+”’+isnull(””’+””’+convert(varchar(200),’+\@colName+’)+””’+””’,”NULL”)+”,”+’
END

SET \@string=\@string+\@colName+’,’

FETCH NEXT FROM cursCol INTO \@colName,\@dataType
END
DECLARE \@Query nvarchar(4000)

SET \@query =’SELECT ”’+substring(\@string,0,len(\@string)) + ‘) VALUES(”+ ‘ + substring(\@stringData,0,len(\@stringData)-2)+”’+”)” Col1 FROM ‘+ ‘$2’
exec sp_executesql \@query

CLOSE cursCol
DEALLOCATE cursCol
SQLEND

# Run query. The return value is a reference to an array.
my $result = $sqlsrv->sql($stmnt);
# Print results. Each array entry is a hash reference.
foreach my $row (@$result) {
print “$$row{Col1}\n”;
}

if ($HasIdentityColumn == 1) {
print “SET IDENTITY_INSERT $2 OFF\n”;
}
}
close MYDATA;
[/sourcecode]