SQL Server 2008 insert into table using loop

Question

I want to insert records in a table with certain values which I got from a SELECT query on other other tables.

SELECT TOP 1000 c.ContaId
FROM FastGroupe fg
INNER JOIN FastParticipant fp 
    ON fg.FastGroupeId = fp.FastGroupeId
INNER JOIN Participant p
    ON fp.ParticipantId = p.ParticipantId
INNER JOIN Contact c
    ON p.ContaId = c.ContaId
WHERE FastGroupeName like '%Group%'

I want to use the IDs I get from this query to insert in another table Member which use ContaId as a foreign key.

I want to use a WHILE loop, but I don't know the number of records I will get from the SELECT query.

So, is there a workaround for this problem.

EDIT

This is the table Member

CREATE TABLE [dbo].[Request](
[MemberId] int IDENTITY(1,1) NOT NULL,
    [ContaId] int NOT NULL,
[PromoId] int NOT NULL
);

The PromoId is a non null column, but I want to put the same value for all the records I am about to create.

The Member records have to be like this

 MemberId = Automatic,
 ContaId = // one of the query results,
 PromoId = 91

Mahmoud Gamal · Accepted Answer · 2012-11-19 11:33:03Z

1

I want to use the IDs I get from this query to insert in another table Member which use ContaId as a foreign key.

You can use INSERT INTO .. SELECT instead of cursors and while loops like so:

INSERT INTO Member(ContaId)
SELECT TOP 1000 c.ContaId
FROM FastGroupe fg
INNER JOIN FastParticipant fp 
    ON fg.FastGroupeId = fp.FastGroupeId
INNER JOIN Participant p
    ON fp.ParticipantId = p.ParticipantId
INNER JOIN Contact c
    ON p.ContaId = c.ContaId
WHERE FastGroupeName like '%Group%'

Update: Try this:

INSERT INTO Member(ContaId, PromoId)
SELECT TOP 1000 c.ContaId, 91 AS PromoId
FROM FastGroupe fg
...

This will insert the same value 91 for the PromoId for all the 1000 records. And since the MemberId is set to be automatic, just ignore it in the columns' list and it will get an automatic value.

edited Nov 19, 2012 at 11:33

answered Nov 19, 2012 at 11:03

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kbaccouche Over a year ago

wow so simple, but in the table Member, I have other columns which don't come from the SELECT query. Will that be a problem ?

Mahmoud Gamal Over a year ago

@the_ruby_racer, It will be a problem, because the select statement will give you 1000 ids, for the other columns which don't come from the select query. How did you will get them? You have to put them by somehow within this select statment. By JOIN or UNION. Can you please explain more?

CloudyMarble · Accepted Answer · 2012-11-19 11:04:06Z

0

You need only to make sure that number of columns match the selected data in the subquery

the Syntax is like this:

Insert into YourTable(calumn1, column2, ..., columnN) 
values (Select (calumn1, column2, ..., columnN) from ... ) // The Subquery

answered Nov 19, 2012 at 11:04

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

2 Comments

kbaccouche Over a year ago

in the table Member, I have other columns which don't come from the SELECT query. Will that be a problem ?

CloudyMarble Over a year ago

IT depends if these are Not Nullabel or Constraint Columns you will have to decide what values get there. Otherwise this will be no problem you will have only to write as much columns after Insert as much as you select in the Subquery

Collectives™ on Stack Overflow

SQL Server 2008 insert into table using loop

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related