Prepare / Generate repetitive SQL Scripts Using Microsoft EXCEL CONCATENATE Function

advertisements

_____________________________________________________________________________________________________________________

Suppose if you wanted to create a series users like user1 to user100 with default tablespace is USER and temporary tablespace TEMP. I mean all the details for these users are same apart from the user name and password. In this scenario you can create the scripts using Microsoft Excel Concatenate function.

1. Open a New excel spread sheet.
2. In Column A populate the usernames from User1 to User100. You can use fill series option to generate n number of users.
3. In Column B enter the following formula and copy the formula upto B100th column. This will generate the user creation scripts.

=CONCATENATE ("Create user ",A1," identified by ",A1," default tablespace users temporary tablespace temp;")

4. In Column C enter the following formula and copy the formula upto C100th column which will generate the tablespace quota scripts.

=CONCATENATE ("ALTER USER ",A1," QUOTA UNLIMITED ON USERS;")

5. In Column D enter the following formula and copy the formula upto D100th column which will generate the grant scripts.

=CONCATENATE ("grant connect, resource to ",A1,";")

6. Copy the generated scripts into a .sql file and you can run it on SQL prompt.

_____________________________________________________________________________________________________________________

Website Stats