Solved

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

Posted on 2011-09-12
15
475 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 50

Expert Comment

by:Steve Bink
Comment Utility
Do you have a data file with the relationships as they should exist?
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
>>> 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 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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
Comment Utility

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 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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
Comment Utility
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 76

Accepted Solution

by:
arnold earned 500 total points
Comment Utility
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
Comment Utility
thanks
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now