East question on stored procedure

Posted on 2005-05-16
Last Modified: 2010-08-05
I am trying to copy a record within a table. So for each record returned create a new record. i.e.

SELECT * FROM table2 where tb1_ID=5  ->> returns some rows: with columns (tb2_ID, tb1_ID, name)

what i want to do is: for each row returned i want to create a record such that:
INSERT INTO table3 (tb2_ID,name, age) values (...)  ->> the values tb2_ID is the id from the above SELECT

so if the first select returned:

i should do 3 inserts as follows:
INSERt into table3 (tb2_id,name,age) values (1,'test',23)
INSERt into table3 (tb2_id,name,age) values (2,'meh',16)
INSERt into table3 (tb2_id,name,age) values (3,'blah',56)

My question is how do i loop through the returned records and get the tb2_ID.
This is has to be a stored procedure. thx
Question by:jkain023
    LVL 23

    Expert Comment

    INSERt into table3 (tb2_id,name,age)
    SELECT field1, field2, field3  FROM table2 where tb1_ID=5

    Good Luck:)
    LVL 28

    Expert Comment

    Try this:

    INSERT INTO Table3 (tbl2_id, name, age)
    SELECT tbl2_id, name, <Age Column>
    FROM table2
    WHERE tbl1_id = 5

    By the way, where are you going to get the value for the age column?
    LVL 2

    Author Comment

    Let me give u the details to what i am doing:
    ps. all IDs are defined as identities....

    ID1, other fields

    ID2, ID1, other fields (ID1 foreign key)

    ID3, ID2, other fields(ID2 foreign key)

    I need to replicate a record in table1 as a new record and its other records in other tables that are linked

    I am done replication for table1 ->insert into (fields) SELECT fields FROM table1 where ID1=[some val]
    I am done replication for table2 ->insert into (ID1,fields) select ID1[from above insert],fields from table2 where ID1=[from the initial record in table1]

    now here is where i die out.
    table2 can have many records and each record in table2 can have many records in table3

    So esentially i need to loop though each record in table2 and create all the corresponding records in table3


    LVL 2

    Author Comment

    neva mind guys...
    i got hold of it

    I can use a cursor for my records returned and loop through them to execute my sqls for th table3
    below is a sample operation flow for looping through recordsets

                    declare @CustId nchar(5)
          declare @RowNum int
          declare CustList cursor for
          select top 5 CustomerID from Northwind.dbo.Customers
          OPEN CustList
          FETCH NEXT FROM CustList
          INTO @CustId
          set @RowNum = 0
          WHILE @@FETCH_STATUS = 0
            set @RowNum = @RowNum + 1
            print cast(@RowNum as char(1)) + ' ' + @CustId
            FETCH NEXT FROM CustList
              INTO @CustId
          CLOSE CustList
          DEALLOCATE CustList

    LVL 2

    Author Comment

    Im not sure how to cancel this question. Can a moderator help me out, i kinda solved my question myself
    LVL 28

    Expert Comment

    Here's a link to what you can do since you answered your own question:

    I answered my question myself. What do I do?
    LVL 2

    Author Comment

    thx rafrancisco

    Accepted Solution

    Question Closed, 125 points refunded.
    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now