Win has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

Does anyone know the best way to create SQL statements that recreate database tables in MS SQL Server? (ie. CREATE TABLE and INSERT statements). Do I need an SQL solution to this or a Perl solution?

Replies are listed 'Best First'.
Re: SQL table recreation
by gellyfish (Monsignor) on Jan 05, 2005 at 14:20 UTC

    You can do this (on Windows) using a combination of Perl and SQL Server capabilities: 

    use Win32::OLE;

my $dmo = Win32::OLE->new("SQLDMO.SQLServer") or die;

$dmo->Connect('ServerName','UserName','Login');

my $db = $dmo->{Databases}->{'DatabaseName'};
my $table = $db->{Tables}->{'TableName'};
$table->Script(undef,"foo.sql");

    [download]
    This will generate the SQL to create the table 'TableName' in the database 'DatabaseName' in the file "foo.sql". You will probably want to read more about the SQL-DMO in the SQL Server documentation.

    /J\

[reply]
[d/l]
Re: SQL table recreation
by EdwardG (Vicar) on Jan 05, 2005 at 14:10 UTC

    You need a SQL solution.

    You can "script object to..." in Query Analyzer and Enterprise Manager.

    You can also BCP data out and back in.

    And from the command line...

    ...\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s server /d database /P password /f textfile

     

[reply]
[d/l]
Re: SQL table recreation
by simonm (Vicar) on Jan 05, 2005 at 18:13 UTC
    I'm not sure which way is best, but there are certainly many ways to do this from Perl. One example, using my DBIx::SQLEngine module: 
    my $sqldb = DBIx::SQLEngine->new( $dbi_dsn, $user, $passwd );

push @sql, [ $sqldb->sql_create_table( $tablename, 
        $sqldb->detect_table( $tablename ) ) ];

push @sql, $sqldb->visit_select( table => $tablename, sub {
  [ $sqldb->sql_insert( values => (shift) ) ]
} );

    [download]

    The @sql array now contains a create table and several insert statements, each as a reference to an array of a SQL statement followed by a number of placeholder parameter values.

    (This is not specific to MS SQL Server, so you'll probably achieve better results with one of the Windows-specific solutions others have posted, but I thought it'd be good to include a Perl implementation.)

[reply]
[d/l]
Re: SQL table recreation
by legato (Monk) on Jan 05, 2005 at 14:45 UTC

    Your question is a bit vague, but I will try to guess at what you mean.

    • You want to clear tables before loading them with data?
      Depending on your permissions to the DB, 'TRUNCATE TABLE' followed by 'INSERT' will be the fastest. 'DELETE FROM' works, but is slower as it gets logged. This will preserve the table design, while clearing its contents.
    • You want to remove a table and recreate it with new rules?
      I would examine 'ALTER TABLE', which will probably do what you need. If it doesn't, your only real option is 'DROP TABLE' followed by 'CREATE TABLE'
    • You want to create a table from data that's already in the database?
      I suggest researching 'SELECT ... INTO' syntax.
    You probably are looking for a pure-SQL solution, in which case your question is a bit off-topic. However, if you are looking for ways to implement SQL Server access without having to learn SQL, DBIx::Abstract is a good place to start. There are several other modules that perform abstraction also available on the CPAN.

    If it is just SQL you're after, drop me a private message and I will see if I can help.

    Anima Legato
    .oO all things connect through the motion of the mind

[reply]
      I want to be able to create and insert data for a table and do that within a stored procedure. I already have this table, but I want the code that would allow me to recreate this table within a stored procedure.
[reply]
Re: SQL table recreation
by erix (Prior) on Jan 05, 2005 at 12:49 UTC

    I don't know exactly how you mean 'recreate' (from what?), but one Perl solution might be to use SQL::Statement

[reply]
      I don't know exactly how you mean 'recreate'

      THat would have been a good reason for not answering the question.

      gellyfish got it correctly and gave a sensible answer.

      Your advice is likely to confuse rather than enlightening the OP.

[reply]
Re: SQL table recreation
by dragonchild (Archbishop) on Jan 05, 2005 at 13:50 UTC
    SQL statements are just pieces of text. If you're looking to create templated text, you will want to break the targets up into what's shared and what differs. Then, build your pieces as needed.

    Often, I will use HTML::Template to build template files. I had very good success with this when I needed to build auditing tables in Oracle. Don't be fooled by the HTML:: part - HTML::Template is an excellent generic templating solution. It just so happens that its most common usage is HTML.

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

[reply]
Re: SQL table recreation
by r34d0nl1 (Pilgrim) on Jan 05, 2005 at 13:32 UTC
    I also don't know what you mean by re-create, but could use something like this: 
       $rv  = $dbh->do($statement);
   $rv  = $dbh->do($statement, \%attr);
   $rv  = $dbh->do($statement, \%attr, @bind_values);
#quoted for perldoc DBI

    [download]
    to use dml and ddl statements that will not return values
    like create table and insert statements.
[reply]
[d/l]

Back to Seekers of Perl Wisdom