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;