Please help with AWK

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • oigeli
    New Member
    • Dec 2007
    • 1

    #1

    Please help with AWK

    I need a AWK script do generate multiple INSERT statement for MySQL. Following is the example of the INSERT statement;

    INSERT INTO `profile_setup` (`user_id`, `profile_name`, `profile_number `, `profile_type`) VALUES
    ('155214222', 'User Defined Profile', 2, 'user_defined') ,
    ('155214222', 'User Defined Profile', 3, 'user_defined') ,
    ('155214222', 'User Defined Profile', 4, 'user_defined') ,
    ('155214222', 'User Defined Profile', 5, 'user_defined') ,
    ('155214222', 'User Defined Profile', 6, 'user_defined') ,
    ('155214222', 'User Defined Profile', 7, 'user_defined') ,
    ('155214222', 'User Defined Profile', 8, 'user_defined') ;


    I have a text file with the just the couple hundreds of user_id(s), example as following;

    2061975327
    21029196016
    23331984074
    60319888854
    12273996604
    900119944710
    180819545267
    152648481288
    186018001855
    194145515163
    207281876612
    219666022218

    Please help me with script that will generate the sql statement for all the user ids. The profile number starts with 2 and end at 8.

    Thanks
  • radoulov
    New Member
    • Jun 2007
    • 34

    #2
    Code:
    { 
    echo 'INSERT INTO `profile_setup` (`user_id`, `profile_name`, `profile_number`, `profile_type`)
    VALUES'
    awk '{for(i=2;i<=8;i++)
            print p v $1 v", "v"User Defined Profile"v", "i", "v"user_defined"v p1","
    }' p="(" v="'" p1=")" filename
    }
    Use nawk or /usr/xpg4/bin/awk on Solaris.

    Comment

    • radoulov
      New Member
      • Jun 2007
      • 34

      #3
      Just noticed you want separate statements,
      so:

      Code:
      awk '{
      	print "INSERT INTO " a "profile_setup" a " " p a "user_id" a ", "a "profile_name" a ", " a "profile_number" a ", " a "profile_type" a p1 " VALUES";
      	for(i=2;i<=7;i++)
      			print p v $1 v ", " v "User Defined Profile" v ", " i ", " v "user_defined" v p1 ","
      	print p v $1 v ", " v "User Defined Profile" v ", 8, " v "user_defined" v p1 ";"
      }' a="\`" p="(" v="'" p1=")" filename

      Comment

      Working...