Post reply

multiple insert from one table depending on the number of rows in another table

  • July 30, 2013 at 8:17 am

    #296195

    Hi

    I have 2 tables as below which I import to sql server from supplied text files. I would like to repeatedly select / insert all the same rows from Table1 into a new table depending on the number of rows there are in Table2 adding the ID from Table2 each time per insert. As per the results table below. Both table1 and Table2 could have a variable number of rows.

    I'm not sure how to accomplish this at he moment, maybe to use a for each loop or a cursor.

    Any help would be appreciated.

    Table 1

    chrstartend

    chr11450118250

    chr1102501103750

    Table 2

    ID

    100001

    100007

    result

    chr start end ID

    chr11450118250100001

    chr1102501103750100001

    chr11450118250100007

    chr1102501103750100007

  • July 30, 2013 at 8:24 am

    #1637011

    I'm not totally clear what you're trying to do, but have you tried CROSS JOINing the two tables?

    John

  • July 30, 2013 at 8:34 am

    #1637017

    Ah perfect, thanks.

    that's exactly what I want. I think to much in procedural code....

  • July 30, 2013 at 8:53 am

    #1637023

    As it stands there appears to be no definitive way to associate a row from table1 to table two. However, assuming you want it to be the order that they occur in the files, I would suggest that you either modify your ETL process to just combine the two files into one file and load that.

    As an alternative you could add an identity column to each stage table then do an insert into table3 by joining the two tables.

    The probability of survival is inversely proportional to the angle of arrival.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply