Routine to create SQL Insert statements from table data 
Author Message
 Routine to create SQL Insert statements from table data
Does someone have a routine which will take the contents of a table
and create SQL Insert statements?  I've searched the IIUG library but
can't find what I'm looking for.  We have a need to repopulate certain
tables in a training database several times a day and want the
instructors to control this.


Wed, 18 Jun 1902 08:00:00 GMT
 Routine to create SQL Insert statements from table data


says...

Quote:
> Does someone have a routine which will take the contents of a table
> and create SQL Insert statements?  I've searched the IIUG library but
> can't find what I'm looking for.  We have a need to repopulate certain
> tables in a training database several times a day and want the
> instructors to control this.

try this:

to create a script that will load the following table:

 test
   field1 integer,
   field2 integer,
   field3 char(5)

unload to reloadit.sql
select
   "insert into tablename values (",
   field1,
   ",",
   field2,
   ", \"",
   field3,
   "\");"

then just remove the pipes and you've got a sql to load the data.

Note:  if there's a way to do an unload without delimiters, I'd be
interested in learning it.

--
---------------------------------------------------------------
  Aaron A. Brooks  
  Senior Manager, Information Logistics
  PC Service Source




Wed, 18 Jun 1902 08:00:00 GMT
 Routine to create SQL Insert statements from table data


Quote:
> Does someone have a routine which will take the contents of a table
> and create SQL Insert statements?  I've searched the IIUG library but
> can't find what I'm looking for.  We have a need to repopulate certain
> tables in a training database several times a day and want the
> instructors to control this.

I have a script that loads data from an unload file into a table.
It uses dbload and requires that the unload file have the same name
as the table name, with a ".unl" extension.  (table1, table1.unl)
It uses isql to query "systables" to get the number of columns in
the table (needed for the command file used by dbload) and to delete
data when the "remove old data" option is chosen.  (dbaccess could be
substituted).  

So get the data to be reloaded into the various tables into pipe-delimited
files and be in that directory when you invoke this script.

------------

#!/usr/bin/ksh
# reload.run  CMcG  04/09/98
# Autogenerate a dbload command for loading data from a pipe-delimited
#   unload file into a table, then run it.
# Optionally delete previous data in file before loading (-z flag)

USAGE="Usage: $0 [-z] [-d database] [-t table-to-load]"

delete_old=0
db=''
tn=''

while getopts zd:t: c
do
  case $c {
    z)  delete_old=1;;
    d)  db=$OPTARG;;
    t)  tn=$OPTARG;;
        \?) echo $USAGE
        exit 2;;
  }
done

if [ "$tn" = "" ]; then
  echo "  "
  echo "A tablename must be supplied for this load operation"
  echo $USAGE
  echo "exiting ..."
  sleep 2
  exit 2
fi

fn=$tn.unl

if [ ! -f $fn ]; then
  echo "  "
  echo "Error!  The unload file for this load operation"
  echo "   1) must be a pipe-delimited ASCII unload file"
  echo "   2) must be in the current directory,"
  echo "   3) and must be called $tn.unl."
  echo "exiting ..."
  sleep 2
  exit 2
fi

ncols=`
echo "output to pipe \"cat\" without headings
select ncols from systables where tabname = \"$tn\";" |
isql tracsy 2>/dev/null`
ncols=`echo $ncols`
if [ -z "$ncols" ]; then
  echo " "
  echo "Table $tn not found!"
  echo "exiting ..."
  sleep 2
  exit 0
fi

(
echo "FILE \"$fn\" DELIMITER \"|\" $ncols;"
echo "INSERT INTO $tn;"
) > $tn.cmd

if [ $delete_old -eq 1 ]; then
  echo "Deleting old data"
  echo "delete from $tn where 1=1" | isql $db -
fi

dbload -d tracsy \
       -c $tn.cmd \
       -l $tn.err \
       -e 1000 | tee -a $tn.log

--
  ____________________________________________________________________

  | Raytheon Engineers & Constructors, Inc.        (215) 422-4144
  | Philadelphia, PA, USA
  | Any opinions I state are my own and not necessarily of my employer
  |___________________________________________________________________



Wed, 18 Jun 1902 08:00:00 GMT
 Routine to create SQL Insert statements from table data

Quote:

> Does someone have a routine which will take the contents of a table
> and create SQL Insert statements?  I've searched the IIUG library but
> can't find what I'm looking for.  We have a need to repopulate certain
> tables in a training database several times a day and want the
> instructors to control this.

Why not just have a script that does a dbaccess UNLOAD and another that
does a LOAD?  The instructors can then just run the two scripts as
appropriate.

I do have an awk script to generate such scripts from dbschema/myschema
output if that would help.

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 Routine to create SQL Insert statements from table data

That "reload" script I sent out earlier today had a hard-coded database
name in two spots, the "tracsy" entries should be replaced with "$db"

--
  ____________________________________________________________________

  | Raytheon Engineers & Constructors, Inc.        (215) 422-4144
  | Philadelphia, PA, USA
  | Any opinions I state are my own and not necessarily of my employer
  |___________________________________________________________________



Wed, 18 Jun 1902 08:00:00 GMT
 Routine to create SQL Insert statements from table data


Quote:

> In article



Quote:
> > Does someone have a routine which will take

the contents of a table
Quote:
> > and create SQL Insert statements?  I've

searched the IIUG library but
Quote:
> > can't find what I'm looking for.  We have a

need to repopulate certain
Quote:
> > tables in a training database several times a
day and want the
> > instructors to control this.

> I have a script that loads data from an unload
file into a table.
> It uses dbload and requires that the unload file
have the same name
> as the table name, with a ".unl" extension.

(table1, table1.unl)
Quote:
> It uses isql to query "systables" to get the

number of columns in
Quote:
> the table (needed for the command file used by

dbload) and to delete
Quote:
> data when the "remove old data" option is

chosen.  (dbaccess could be
Quote:
> substituted).

> So get the data to be reloaded into the various

tables into pipe-delimited
Quote:
> files and be in that directory when you invoke
this script.

> ------------

> #!/usr/bin/ksh
> # reload.run  CMcG  04/09/98
> # Autogenerate a dbload command for loading data

from a pipe-delimited
Quote:
> #   unload file into a table, then run it.
> # Optionally delete previous data in file before
loading (-z flag)

> USAGE="Usage: $0 [-z] [-d database] [-t
table-to-load]"

> delete_old=0
> db=''
> tn=''

> while getopts zd:t: c
> do
>   case $c {
>     z)     delete_old=1;;
>     d)     db=$OPTARG;;
>     t)     tn=$OPTARG;;
>    \?) echo $USAGE
>         exit 2;;
>   }
> done

> if [ "$tn" = "" ]; then
>   echo "  "
>   echo "A tablename must be supplied for this
load operation"
>   echo $USAGE
>   echo "exiting ..."
>   sleep 2
>   exit 2
> fi

> fn=$tn.unl

> if [ ! -f $fn ]; then
>   echo "  "
>   echo "Error!  The unload file for this load
operation"
>   echo "   1) must be a pipe-delimited ASCII
unload file"
>   echo "   2) must be in the current directory,"
>   echo "   3) and must be called $tn.unl."
>   echo "exiting ..."
>   sleep 2
>   exit 2
> fi

> ncols=`
> echo "output to pipe \"cat\" without headings
> select ncols from systables where tabname =
\"$tn\";" |
> isql tracsy 2>/dev/null`
> ncols=`echo $ncols`
> if [ -z "$ncols" ]; then
>   echo " "
>   echo "Table $tn not found!"
>   echo "exiting ..."
>   sleep 2
>   exit 0
> fi

> (
> echo "FILE \"$fn\" DELIMITER \"|\" $ncols;"
> echo "INSERT INTO $tn;"
> ) > $tn.cmd

> if [ $delete_old -eq 1 ]; then
>   echo "Deleting old data"
>   echo "delete from $tn where 1=1" | isql $db -
> fi

> dbload -d tracsy \
>        -c $tn.cmd \
>        -l $tn.err \
>        -e 1000 | tee -a $tn.log

> --

__________________________________________________
__________________
Quote:
>   | Colin McGrath


Quote:
>   | Raytheon Engineers & Constructors, Inc.
(215) 422-4144
>   | Philadelphia, PA, USA
>   | Any opinions I state are my own and not

necessarily of my employer
|_________________________________________________
__________________

This is a script I wrote that makes loading
pipe-delimited files very easy.  You don't have to
create the command file because it does it for
you.  It checks the first line of the file you are
loading to make sure that the number of fields and
the number of columns in the table match.
Syntax:   dbload.sh  databasename tablename
filename

Here it is:
###---cut here ---###
#!/bin/ksh
#Script: dbload.sh
#Written by: Jim Andersland
#This utility runs the informix dbload utility
creating the required command
# file used by dbload automatically.  It takes
three parameters,
# database, tablename, and pipe-delimited
datafile.
# It checks the 1st line of the datafile to make
sure that it contains
# the same number of fields as the table it is
being loaded into before
# a load can take place.
#Parameters:
# $1 = database name
# $2 = table name
# $3 = datafile name
# $4 = nocheck  (optional, to avoid check on the
number of pipes in file and table)

USAGE="\n\nUsage:  dbload.sh database tablename
datafile [nocheck]\n"
if (($# < 3))                        #Three parameters
required
then
    print "A utility to load a pipe-delimited
datafile into a database table"
    print $USAGE
    exit 1
fi

DATABASE=$1
TABLENAME=$2
DATAFILE=$3
NOCHECK=$4

#Return the # of columns in the database table
TBLCOLS=$(dbschema -d ${DATABASE} -t ${TABLENAME}
| grep "number of columns" | sed 's/^.*columns =
//' | sed 's/ index.*$//')
if [[ -z $TBLCOLS ]]
then
   print "Error:  Table not found in database"
   exit 1
fi
#echo "TABLE COLUMNS= " $TBLCOLS

if  [[ $NOCHECK != "nocheck" ]]
then
  #Return # of columns in datafile - must match
the table column count
  #Sed passes 1st data row to sed remove all but
pipes and assign to string
  PIPESTR=`sed -n '1,1p' ${DATAFILE} | sed
's/[^|]*//g'`
  #The length of the string will be the number of
data columns in the file
  DATCOLS=${#PIPESTR}
else
  #No checking, assume the number of columns are
correct
  DATCOLS=$TBLCOLS
fi

if [ $TBLCOLS = $DATCOLS ]
then
   #echo "Table and datafile column counts are the
same"
   print 'Processing ... Please Wait ...'
   ERRSALLOWED=10
   LOGFILE=load_${TABLENAME}.log

   #build command file with unique timestamp
   TIMESTAMP=`date +%y%m%d_%H%M%S`
   CMDFILE=/tmp/loadcmd.$TIMESTAMP
   echo "FILE "'"'${DATAFILE}'" DELIMITER "|"
'${TBLCOLS}';' > ${CMDFILE}
   echo "INSERT INTO "${TABLENAME}';' >>
${CMDFILE}

   dbload -d ${DATABASE} -c ${CMDFILE} -l
${LOGFILE} -e ${ERRSALLOWED}
   rm ${CMDFILE}
   print "Loading Completed."
else
   echo "Table and Datafile Number of Columns do
not match: " ${TBLCOLS} " and " ${DATCOLS}
fi

###--- cut here ---###

What you could do is have a script that does the
something like following:

echo 'drop table mytable' | dbaccess mydatabase
dbaccess mydatabase mycreatetablescript.sql
dbload.sh mydatabase mytable mydatafile

I hope that helps.

Jim Andersland
Moore Data Management Services

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Create insert-statements from data in table

2. Create SQL insert Scripts based on existing data in table

3. How? insert data with SQL Insert statement

4. Is there a procedure to generically create an insert statement from any table passed in

5. Creating INSERT statement for each row in a table

6. extract data from tables as INSERT statements?

7. Inserting data into mutliple tables using SQL Insert Command

8. .DBF files to SQL CREATE and INSERT statements

9. auto create script to insert data from table?

10. scripting problem - ALTER statements are before the create table statements

11. Help with SQL Insert Statement of Binary Formatted data

12. Help: Sql statement to insert a file in a table


 
Powered by phpBB® Forum Software