using tsql split row into multiple rows -
i looking write script change table 1 table 2.
table 1
accountno name other field
1 mr t , mrs m lambert xxx
i need rewrite
table 2
accountno split name other field
1 mr t lambert xxx 1 b mrs m lambert xxx
if you, take scripting language of choice , write converter aid. me looks perl or ruby fit task quite nicely.
for example, in ruby like:
require 'active_record' activerecord::base.establish_connection('postgresql://localhost/db') sql = activerecord::base.connection sql.begin_db_transaction # fetch data source table sql.execute('select * source_table').each |source_row| # source_row hash of following form: # { 'col_name_1' => 'col_value_1', 'col_name_2' => ... } # prepare transformed rows array result in destination table transformed_rows = [ ] # make transformed rows need, based on source fields transformed_rows << { 'another_col_1' => source_row['col_name_1'], # ... } transformed_rows.each |transformed_row| # generate , execute insert statement every transformed_row sql.execute("insert destination_table(...) values(...)") end end sql.commit_db_transaction
undoubtedly can achieved in sql, in richer dialects pl/sql, text parsing (which doing quite lot here) not sql's strong side. therefore spend lot of time figuring out string operations in language not quite suited them.
hope helps!
Comments
Post a Comment