Import 2 new colums

• vime

  SSC-Addicted

  Points: 496

  More actions

  December 10, 2004 at 5:25 am

  #60027

  Hey guys

  I am currently importing data from mulitple .ini files to a Staging table (one column) and then selecting the data into the live table

  current Text format

  user1=

  logintime

  location

  machine

  user2=

  logintime

  location

  machine

  etc

  Code -  From staging table to Transform 

  insert

    transform (username,logintime,location,machine)

   

  SELECT substring (u.output,7,15),substring (t.output,16,36),substring (l.output,19,43), substring (m.output,17,37)

  from stagingtable u,stagingtable t,stagingtable l,stagingtable m

  where (U.output like 'User1=%'or U.output like 'User2=%')and

  (T.output like 'User1logonDate%'or T.output like 'User2logonDate%')

  and

  (L.output like 'Userloggedinfrom1%' or L.output like 'Userloggedinfrom2%')

  and

  (m.output like 'User1loggedinto%' or m.output like 'User2loggedinto%')

  and U.id+1 = T.id

  and U.id+2 = L.id

  and U.id+3 = m.id 

  But I now have  2 lines at the end of the file that need to be added to a new columns PD & PT, hence after machine on each user. I cannot use U.id +4 as the PD and PT columns are at the end of the file and the PD on each file must match the machine on each file.ie user1 and user2 on each file will have the same PD and PT

  so the table would like like this

  user1=

  logintime

  location

  machine

  PD

  PT

  user2=

  logintime

  location

  machine

  PD

  PT

  Thee loop and look at the next file.

  Any ideas?

  Quick response would be gratly appreciated

  Thanks

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736255

  More actions

  December 10, 2004 at 10:21 am

  #533379

  When you say PD and PT at the end of file, do you mean 2 lines that are repeated for everyone or two lines for each user?

  If there'd the same, just get them from another task and update the table.