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.


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

  • 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

Post a Comment

Who ever writes Inappropriate/Vulgar comments to context, generally want to be anonymous …So I hope U r not the one like that?
For lazy logs u can at least use Name/URL option which don’t even require any sign-in, good thing is that it can accept your lovely nick name also and URL is not mandatory too.
Thanks for your patience
~Krishna(I love "Transparency")
వీలయితే నాల్గు పోస్టులు ...కుదురితే ఒక కామెంటూ ...

Translate

Enter your email address:

Buffs ...

Visitors


hits counter
View My StatsCheck Google Page Rank

Add to Google Reader or Homepage

Bookmark and Share

Tags


Powered by WidgetsForFree

Archives