Apr
14

Generate mutiple insert commands in sql file using shell script

The following small shell script will generate a SQL file which contains multiple insert statements.
The shell script will add unique numbers for each primary key object in the insert statements.

######################################################################
i=1270803816866
k=1
while [ $k -le 4 ]
do
echo "insert into CDSSCHEMA.PLAN_DESCRIPTION (PLAN_ID,PACKAGE_ID,LOCAL_IP,REMOTE_IP,STATUS,PLANCREATIONTIME,USERID) values ('$i','1270803791784','9.124.158.200','9.124.158.200',1,'2010-04-09 09:04:21.979898','CDS_Administrator')" >> dummydata.sql
let i="i+1"
let k="k+1"
done
####################################################################

Put the above lines of code in shell script and run it at the shell to generate mutiple insert statements according to ur need in  the SQL file dummydata.sql
Don't forget to add your better ideas to improve this script in form of comments.

Wednesday, April 14, 2010 // Labels: DB2, Shell Scripting, UNIX // 1 comments //

Do you like this article? Share It!

1 comments to "Generate mutiple insert commands in sql file using shell script"

• 

  26 April 2010 at 13:20
  Kishore says:

  hi babu
  Good one...have a look of below code, it might be useful stuff & off course this is main part of our code...but runsql function below must to useful one..
  Thanks
  kishore
  -----
  #********************************************************************************
  # Function Name : runSql
  # Description : Run the given sql as the given user with given login/passwd.
  # Parameters : $1=user $2=login/passwd/connectString $3=sql
  # Returns : N/A but $rows set
  # Notes : Can't just return rows as its only 8 bit.
  #********************************************************************************
  runSql ()
  {
  typeset user=${1%%/*}
  typeset dir=/home/${1#*/}
  typeset login=$2
  typeset sql=${3%;}
  typeset pattern=${4:-"^"}
  4>&-
  let rows=$(su $user -c "cd $dir; . ./.profile >/dev/null; \
  echo \"${sql};\ncommit;\nexit;\" | sqlplus -s $login \
  | grep \"${pattern}[0-9]\" 2>/dev/null \
  | tail -1 | sed 's/[^[:digit:]]\{2,\}/ /g' \
  | awk '{ print \$1 }' >&4" 4>&1 >/dev/null) 2>/dev/null
  return
  }
  
  ##main
  # To create an index for USERLINK_IDX1 and NODEAGGREGATE_VIEWTYPE(msp120779 Patch_293)
  if [[ $scriptType = "Postinstall" ]]
  then
  print "DISP: $scriptType for P$p create an index USERLINK_IDX1 and NODEAGGREGATE_VIEWTYPE."
  sql="CREATE INDEX USERLINK_IDX1 ON USERLINK (MAPID,ACTION,SESSIONID,MAPREFID) TABLESPACE NP_INDEX_S1;
  CREATE INDEX NODEAGGREGATE_VIEWTYPE ON NODEAGGREGATE(VIEWTYPE) TABLESPACE NP_INDEX_S1;"
  runSql oms "osm/dbmanager@osm" "$sql"
  print "DISP: $scriptType for P$p created an index EM_NOTIF_SVCRID."
  
  elif [[ $scriptType = "Postremove" ]]
  then
  print "DISP: $scriptType for P$p droping an index USERLINK_IDX1 and NODEAGGREGATE_VIEWTYPE."
  sql="DROP INDEX USERLINK_IDX1;
  DROP INDEX NODEAGGREGATE_VIEWTYPE;"
  runSql oms "osm/dbmanager@osm" "$sql"
  print "DISP: $scriptType for P$p Droped an index USERLINK_IDX1 and NODEAGGREGATE_VIEWTYPE."
  
  fi