insert into .... select question? 
Author Message
 insert into .... select question?

Lee,

    One way would be to create a virtual table, with calculated "rankings"
for each record, and then insert that virtual table with the rankings as the
id column.... This will only work if your data has some unique column... Say
it's "ColPK"

Then

Select (Select Count(*) From TblB ITA
             Where ITA.ColPK <= OTA.ColPK) As Rank,
            <OtherColumns>
From TblB
Where ......

This will geberate a virtual table of the records you want to insert, with
each record ranked (record numbers).

If you modify it slightly to add the Max(id) from the table you want to
insert into, then you should be able to insert the whole set...

Insert Into TblA
    Select (Select Count(*) From TblB ITA
             Where ITA.ColPK <= OTA.ColPK) +
               + (Select Max(id) From TblA) As NewID,
            <OtherColumns>
    From TblB
    Where ......

Regards,

Charly



Quote:
> I can insert one record into a table not using identity.


> AS


> But, how can I do if I want to use "insert into tblA (userid, [id],......
> select ........from tlbB where ....." to insert many records.

> Andy



Wed, 24 Apr 2002 03:00:00 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. INSERT INTO ... SELECT question

2. insert into .... select question?

3. insert into select * statement question

4. INSERT SELECT UNION question

5. SELECT DISTINCT and Insert. Interesting question

6. question about insert ...select

7. SQL Question: INSERT with SELECT in VALUES section

8. Questions about INSERT with a subquery SELECT

9. SQL-Question: insert into .. select from

10. Simple T-SQL Question - Select, Insert, then Delete

11. Insert/Select question in 4gl

12. INSERT/ VALUES vs INSERT/ SELECT


 
Powered by phpBB® Forum Software