The Ji Village News

季庄新闻–Haidong Ji’s Blog

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;