Link to home
Start Free TrialLog in
Avatar of Aleks
AleksFlag for United States of America

asked on

Insert multiple records into a table

I have a list of items I need to include for each record on a table.

I have table : Forms  (this has a list of forms in our system)
I have table : x_refernece  (this has the ID of the form "Qid" and a field for 'usertype'

There are many usertypes such as beneficiary, contact, father, mother, etc.

In the x_reference table I need to insert one row for every usertype for every form I have. So I would end up with something like:

Qid: 1  |  usertype  'beneficiary'
Qid: 1  | usertype 'mother'
Qid 1 | usertype 'father'  

and so on. all usertypes, then the query needs to go to the next form, take the Qid and insert into the x_reference table

2 | beneficiary
2 | mother
2 | father

and so on, I should end up with 10 records or so (one for each usertype)  for every form (qid) I have.

how can I create such a query ?  
This are the tables involved:

table:  forms
field : qid

table :usermap
field: usertype  (there are many entries there for each usertype, so in order to prevent duplicates the query below will group them)

table : x_reference
field: autoid (autoid)  (int)
field: qid:  (Comes from the forms table)  (int)
usertype:  comes from the 'usermap' table.  (varchar 20)

I can list them by using this query:

SELECT usertype FROM dbo.UserMap
GROUP BY UserType

Open in new window


I get 29 results. (29 usertypes)
I have 642 forms

This means I should end up with 18618 records if my calculations are correct. each form form with 29 usertypes included.
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Before you actually perform the inserts you might want to add a unique index to the x_ref table... especially if you already have some data in it.

Here is what I would use:    CREATE UNIQUE INDEX ix_qid_usertype ON x_reference (qid, usertype);
Avatar of Aleks

ASKER

I double checked the numbers. and it is 613 forms and 29 usertypes, which is 17,777 my bad on the count.
Ill try to do the insert now.
Avatar of Aleks

ASKER

Great query!