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.
#!/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 = <mydata> ){
$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 = < <SQLEND;
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = '$2'
OPEN cursCol
DECLARE \@string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE \@stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE \@dataType nvarchar(1000) --data types returned for respective columns
SET \@string='INSERT '+ '$2' +'('
SET \@stringData=''
DECLARE \@colName nvarchar(50)
FETCH NEXT FROM cursCol INTO \@colName,\@dataType
IF \@\@fetch_status<>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;