Please help with AWK

[oigeli]

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]

{ 
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.

[radoulov]

Just noticed you want separate statements,
so:

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