Solved

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

Posted on 2011-09-12
15
487 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 50

Expert Comment

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

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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 78

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 50

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 78

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 78

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 78

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 50

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 78

Accepted Solution

by:
arnold earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I get the entire database script? 7 35
SQL Recursion 6 49
Tracking Problematic Page Splits 1 50
What is GIS method of Geometry data type? 6 36
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

710 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