Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-12
15
Medium Priority
?
499 Views
Last Modified: 2012-05-12
Hi,

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

eg:
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
0
Comment
Question by:shmz
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 51

Expert Comment

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

Expert Comment

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

http://msdn.microsoft.com/en-us/library/ms188365.aspx

0
 
LVL 37

Expert Comment

by:ValentinoV
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)?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 80

Expert Comment

by:arnold
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.
0
 
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.

0
 
LVL 80

Expert Comment

by:arnold
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
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ tabelMtoM] 
      FROM 'data_file' 
     WITH 
    ( 
    FIELDTERMINATOR = ','  
  ,  FIRSTROW = 1  
  , ROWTERMINATOR = '\n'  
 , ERRORFILE = 'location_where_the_errors_will_be_recorded.log'  
    )

Open in new window

0
 

Author Comment

by:shmz
ID: 36533594
Arnold,

"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??

Thanks

0
 
LVL 80

Expert Comment

by:arnold
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?
0
 
LVL 37

Expert Comment

by:ValentinoV
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?
0
 

Author Comment

by:shmz
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)
Period

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.
0
 
LVL 80

Expert Comment

by:arnold
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?
0
 

Author Comment

by:shmz
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.
0
 
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.
0
 
LVL 80

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:shmz
ID: 36555077
thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Loops Section Overview

971 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