Re: Routine to create SQL Insert statements from table data

In article <373064bd.65697197@news.maricopa.gov>, fprose@rocketmail.com says... > 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 -- ____________________________________________________________________ | Colin McGrath cmm@trac3000.ueci.com | Raytheon Engineers & Constructors, Inc. (215) 422-4144 | Philadelphia, PA, USA | Any opinions I state are my own and not necessarily of my employer |___________________________________________________________________

In article <7gsgvt$rrq$1@news.xmission.com>, Colin M McGrath <cmm@trac3000.ueci.com> wrote: > > In article <373064bd.65697197@news.maricopa.gov>, fprose@rocketmail.com says... > > 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> > -- > __________________________________________________ __________________ > | Colin McGrath cmm@trac3000.ueci.com > | 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 jim_a@hotmail.com --== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.---