Inserting into 2 tables select result of a table SQL Server -

how insert 2 tables result select statement.

i have table several data:

val1 val2 val3 .... valn -------------------------  12   21   54        78  ..   ..    ..       .. 

i have like: select t1.val1, t1.val2, t2.val3, t2.val4 table1 t1 , table2 t2 tablename.

so want val1, val2, being inserted new table 2 fields like:

tabble1: id fieldvalue 1   val1 2   val2 

the same goes val3 , val4. how can acomplished

tabble2: id fieldvalue 1   val3 2   val4 

is possible?

granted, difficult understand trying accomplish. if in fact trying insert rows 2 different tables, marc_s stated, must use 2 insert statements.

however, judging sample, may not case trying insert 2 tables rather use 2 tables insert third table transpose data. if case, can in single statement:

insert mysterytable( id, fieldvalue ) select 1, val1 table1 union select 2, val2 table1 union select 3, val3 table2 --assuming these come table2. isn't clear in op union select 4, val4 table2 --assuming these come table2. isn't clear in op 

of course, if table1 or table2 has many rows, many rows same id value in mysterytable.

update given change op

given clarification, seek can done requires 2 queries similar 1 above.

insert table1( id, fieldvalue ) select 1, val1 sourcetable union select 2, val2 sourcetable  insert table2( id, fieldvalue ) select 1, val3 sourcetable union select 2, val4 sourcetable 

another variation generates id values be:

with numbereditems     (     select val1 val     sourcetable     union     select val2     sourcetable     ) insert table1(id, fieldname) select row_number() over( order val ) num     , val sourcetable  numbereditems     (     select val3 val     sourcetable     union     select val4     sourcetable     ) insert table2(id, fieldname) select row_number() over( order val ) num     , val sourcetable 

btw, in above example, used union all, if trying normalize data, might want have distinct values. in case, use union instead of union all.