Inserting Data into two tables when there is reference between two tables

Question

I have two tables

USE Ticket
GO
CREATE TABLE Test1
(
FirstName NVARCHAR(255),
MiddleName NVARCHAR(255),
LastName NVARCHAR(255),
ContactID INT
CONSTRAINT pk_Test1_pid PRIMARY KEY(ContactID)
)
GO
CREATE TABLE Test2
(
ContactID INT,
SalesOrderID INT,
TotalDue MONEY,
CONSTRAINT pk_Test2_sid PRIMARY KEY(SalesOrderID),
CONSTRAINT fk_Test2_sid FOREIGN KEY(ContactID) REFERENCES Test1(ContactID)
)

Trying to insert rows from a flat file

File is very big i am only giving the column headers for reference and there is one to many relationship between two tables

FirstName,MiddleName,LastName,ContactID,TotalDue

What should be the approach to insert data in to two tables using SSIS

---

Smash126

Answer 1

Insert into Test1 first because it is your parent, then Test2.

Bulk insert is your friend see post this for more: http://beyondrelational.com/modules/2/blogs/43/posts/10202/loading-data-from-text-files-to-sql-server-tables-csv-files-tab-separated-files-or-fixed-column-leng.aspx

---

Arthur My Blog

Answer 2

Hi Smash126,

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view. When you specify the location of the text source file, consider the following:

1. The server must have permission to access both the file and the destination database.
2. The server runs the Bulk Insert task. Therefore, any format file that the task uses must be located on the server.
3. The source file that the Bulk Insert task loads can be on the same server as the SQL Server database into which data is inserted, or on a remote server. If
the file is on a remote server, you must specify the file name using the Universal Naming Convention (UNC) name in the path.

For more details about how to use Bulk Insert Task in SSIS, please refer to:
http://www.c-sharpcorner.com/uploadfile/ae35ca/sql-server-integration-services-ssis-bulk-insert-task-in-ssis/
http://msdn.microsoft.com/en-us/library/ms141239.aspx 

Thanks,
Eileen