bulk import into many to many relationship, sql 2008, query

Posted on 2011-09-12
Medium Priority
Last Modified: 2012-05-12

I have a three tables:

tableA (1:M)  tableMtoM  (M:1) L_tableB

The relationship between tableA and L_tableB is M:M hence tableMtoM.

TableB is a lookup table already populated.

I need to populate tableA first and then TableMtoM.

tableA columns: a_ID primary key , a_Description
tableB column: b_ID primary key, b_desc
tableMtoM columns: M_Id identity primary key, a_id foreign key, b_id foreign key

b_id can have many b_id's which should be stored in tableMtoM

M_id        b_id           a_id          
1             300            88888
2             300            99999
3             300            11111
4              400            88888
5              400            11112
6              899            11111

how can I do a bulk import to populate tableMtoM?

Thanks in advance
Question by:shmz
  • 6
  • 4
  • 3
  • +1
LVL 51

Expert Comment

by:Steve Bink
ID: 36530068
Do you have a data file with the relationships as they should exist?
LVL 81

Expert Comment

ID: 36530069
What is the format of the file that you will be importing?


LVL 37

Expert Comment

ID: 36530135
What is missing the story is how you're going to match a record from table A with a record from table B.  What's the logic here?  Aren't there any business keys (like a unique code that matches the description for instance)?
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 81

Expert Comment

ID: 36531254
He is using the ID column from each table in the relationship table.

If the format of the data is in CSV form, the link provides an example on how to specify.
note the order of columns has to match the way the data is arranged in the file, or you have to as part of the import specify the order of the columns using a format file.
Make sure to use the KEEPIDENTITY if the relationship table has M_ID as auto.

Usually a relationship table, only has id from one and id from the other.  There is no point to the id column in the relationship table.
Why not add a column into tableB that points back to a_id?

select * from tablea a join tableb b on a.a_id=b.fk_a_id

relationship tables are used when there are many tables with relationship among them such that having each table references to many other tables is unmanageable. i.e. you will have tables with 5 columns of data and 10, 20 columns of references to other tables/columns.
LVL 51

Expert Comment

by:Steve Bink
ID: 36531506
>>> Why not add a column into tableB that points back to a_id?

Because he is defining a many-to-many relationship, which requires an intermediary table.

LVL 81

Expert Comment

ID: 36532842
Did not look close enough, to see that it was not a one to many.

Or you can use the import wizard in ssms.

here is an article explaining the configuration option with an example
   [ database_name . [ schema_name ] . | schema_name . ] [ tabelMtoM] 
      FROM 'data_file' 
  ,  FIRSTROW = 1  
  , ROWTERMINATOR = '\n'  
 , ERRORFILE = 'location_where_the_errors_will_be_recorded.log'  

Open in new window


Author Comment

ID: 36533594

"Usually a relationship table, only has id from one and id from the other.  There is no point to the id column in the relationship table.

Good question, our data warehouse specialist likes identity keys everywhere!!

Why not add a column into tableB that points back to a_id?

TableB is a lookup table. TableMtoM is the data entry table.

There are about millions of records, should it be done using csv file??


LVL 81

Expert Comment

ID: 36533662
You can adjust the import based on the type of file separator being used.
if it is a tab separated, you would use \t as the cell separator.

TableMtoM is the relationship table. I do not see a point to use an ID since it has no significance nor not sure how it will ever be used.

tableA and L_tableB are the data tables. These are the tables that have data description for itemA and  description of item B.

lets try it from the begining.
What and how is the information that you have  organized?
A set of three tables has been created as you've outlined.
Now you would like to import an existing set of data consisting of millions of rows into these newly created tables?
LVL 37

Expert Comment

ID: 36534168
So table B is already populated and is a lookup table.  What field are you planning to lookup on?

You say you need to populate first Table A, then the M2M, sounds logical.  But what does the incoming data look like?  One of the fields in your incoming data should match with one of the fields in your lookup table B, hopefully that's the case?

Besides the logical part, there's also the technical side: what technology would you like to use?  Does it need to be T-SQL, or is SSIS an option?

Author Comment

ID: 36535635

Table A (1:M)                  TableM                           (M:1)             L_TableB
ID_A(identity PK)             ID_M(identity PK)                              ID_B (identity PK)
BussinessID_A                ID_A(FK)                                          
country                            ID_B(FK)

a random example would be:
Student                           StudentCourse                                  Courses

This is a survey so, a student can be identified by Bussness_ID +Country + Period
the period could be the fist half year for example.

TableA must be populated before populating TAbleM.
LVL 81

Expert Comment

ID: 36536033
How is the information in the file relate to the three tables you reference?

Does it mean there are external reference tables Student, StudentCourses and Courses? which you are trying to combine into these three new tables to simplify lookups?

Author Comment

ID: 36540419
Arnold, Yes, exactly, but the source database has very redundant tables. not related by PK, FK.... the info were stored in separate tables for a given period.
I basically should import student info period by period as Business_ID is unique within a given period only.
LVL 51

Expert Comment

by:Steve Bink
ID: 36540506
The only way you bulk import the relationship table is if:

1) You already have the relationships defined in your import file.  That means having the actual join values as data in the file.  And,
2) The identity values of the data do not change during the import.

Otherwise, you'll need to recreate the relationship table based on raw source data.  You could probably write a stored procedure to do this, but it will be a bit more effort than having the relation data ready to import.
LVL 81

Accepted Solution

arnold earned 2000 total points
ID: 36540676
Can you create a View where the data you need can be aggregated/assembled.
Once the data is in a view, you are closer to what you need

you could use SSIS/VB, C# to query the existing database/data and then extract the information you need and insert it into the three tables.
Do the three new tables have constraints. i.e. a logic that an entry must exist in tableA and L_tableb before the relationship can be entered in MtoM. not i.e. the FK is the constraint.
you would first have to import data into tableA, then L_tableB and then the relationship references have to be loaded in.

Author Closing Comment

ID: 36555077

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

627 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