Solved

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

Posted on 2011-09-12
15
491 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 51

Expert Comment

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

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
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 
LVL 79

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 79

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 79

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 79

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 79

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

626 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