December 4, 2009 at 2:24 am
I have one large table that is coming in from an external source. I need to import this into my own database, which means splitting the table. The problem is that I have FKs on the tables, based in the ID of the person, which is the auto increment on the Person table.
I have tried to use SSIS, but the insert into the "satellite" tables fails, with a FK Constraint error. Perhaps this needs to be done via T_SQL Script? I am open to either method.
I realise that Ihaven't given enough info to recreate the problem, but in principle, is there something in particular that needs to be done to allow this type of data insert?
Paul
December 4, 2009 at 6:01 am
This can easily be accomplished using a SSIS package. It will take many small steps. You can always drop the foreign key constraints, pump the data in, and add the foreign key constraints after all the data is in place.
They are not called 'satellite' tables. A relational database has parent and child tables.
You need to tell us if this is a one time data pump, or will this happen multiple times.
Andrew SQLDBA
December 4, 2009 at 7:30 am
--Thanks for the reply.
--Child tables. I will remember that.
--This is a data feed that will need to be run regularly, and not a one-off.
--I think I understand what you are saying. Is there a single command to disable all foreign key constrains on a database?
--Also, if I add the data to all the tables, how would I then "tie" it all up again, to insert the FK value for the child tables? The FK value is only generated when a record is inserted.
--I will give more details
--For example, the following record comes from the feed:
--In database, which come in from an external source:
CREATE TABLE [dbo].[Inbound](
[TABLE_ID] [int] IDENTITY(1,1) NOT NULL,
[TITLE] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[FIRST_NAME] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[LAST_NAME] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[GENDER] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[DATE_OF_BIRTH] [datetime] NULL,
[nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[IS_DEFAULT_CONTACT_ADDRESS] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[ADDRESS_LINE_1] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[ADDRESS_LINE_2] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[ADDRESS_LINE_3] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[CITY] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[COUNTY] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[POSTCODE] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[COUNTRY] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[buys_Prod1] [char](1) COLLATE Latin1_General_CI_AS NULL,
[buys_Prod2] [char](1) COLLATE Latin1_General_CI_AS NULL,
[buys_Prod3] [char](1) COLLATE Latin1_General_CI_AS NULL,
[buys_Prod4] [char](1) COLLATE Latin1_General_CI_AS NULL,
[buys_Prod5] [char](1) COLLATE Latin1_General_CI_AS NULL,
[Pref_Depot] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Loyalty_Card] [char](1) COLLATE Latin1_General_CI_AS NULL,
[Wants_Magazine_Online] [char](1) COLLATE Latin1_General_CI_AS NULL,
[Wants_Magazine_Mail] [char](1) COLLATE Latin1_General_CI_AS NULL)
Insert into Inbound(title, first_name, last_name, Gender, Date_of_birth, email, is_default_contact_address, address_line_1, address_line_2, address_line_3, city, county, postcode, country, buys_prod1, buys_prod2, buys_prod3, buys_prod4, buys_prod5, Pref_depot, loyalty_card, wants_magazine_online, wants_magazine_mail) select
'Miss','Laura','Muster',NULL,NULL,'lmussie@internet.com','T','3 Station Close','Minehead',NULL,'Glasgow','Lanarkshire','G19 6YH','United Kingdom','Y',NULL,'N',NULL,'','Brighton','Y','Y','Y'
Insert into Inbound(title, first_name, last_name, Gender, Date_of_birth, email, is_default_contact_address, address_line_1, address_line_2, address_line_3, city, county, postcode, country, buys_prod1, buys_prod2, buys_prod3, buys_prod4, buys_prod5, Pref_depot, loyalty_card, wants_magazine_online, wants_magazine_mail) select
'Mr','John','McAlister',NULL,NULL,'jonnymac@bwee.com','T','1 St Eddies Road','Mandlessonville',NULL,'Edinburgh',NULL,'ED9 5EH','United Kingdom','Y','N','Y',NULL,'','Edinburgh','N',NULL,'Y'
Insert into Inbound(title, first_name, last_name, Gender, Date_of_birth, email, is_default_contact_address, address_line_1, address_line_2, address_line_3, city, county, postcode, country, buys_prod1, buys_prod2, buys_prod3, buys_prod4, buys_prod5, Pref_depot, loyalty_card, wants_magazine_online, wants_magazine_mail) select
'Master','Barry','McNulty','M',NULL,'BMW@bigbazza.com','T','The Roadway','Fridayville',NULL,'London',NULL,'N1 3EF','United Kingdom','Y','Y','Y',NULL,'','London','Y','N','Y'
--In the database the data has to be imported to:
CREATE TABLE [dbo].[Person](
[id] [int] Primary Key IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](20) NULL,
[First_Name] [nvarchar](50) NOT NULL,
[Last_Name] [nvarchar](80) NOT NULL,
[Email] [nvarchar](255) NULL,
[D_O_B] [datetime] NULL,
[Gender] [nvarchar](1) NULL,
[Pref_Depot_id] [int] NULL)
CREATE TABLE [dbo].[Address](
[id] [int] IDENTITY(1,1) NOT NULL,
[Person_id] [int] NOT NULL references Person(id),
[Address_1] [nvarchar](255) NOT NULL,
[Address_2] [nvarchar](255) NULL,
[Address_3] [nvarchar](255) NULL,
[TownCity] [nvarchar](50) NOT NULL,
[County] [nvarchar](50) NULL,
[Postcode] [nvarchar](20) NOT NULL,
[Country_Code] [nvarchar](10) NOT NULL)
CREATE TABLE [dbo].[Literature](
[Person_id] [int] NOT NULL references person(id),
[Wants_Magazine_Online] [Bit],
[Wants_Magazine_Mail] [Bit])
CREATE TABLE [dbo].[Likes](
[Person_id] [int] NOT NULL references person(id),
[buys_Prod1] [bit],
[buys_Prod2] [bit],
[buys_Prod3] [bit],
[buys_Prod4] [bit],
[buys_Prod5] [bit],
[Loyalty_Card] [bit])
CREATE TABLE [dbo].[Depot](
[id] [int] Primary Key Identity (1,1) NOT NULL,
[Name] [Varchar] (50))
ALTER TABLE [dbo].[Person] WITH NOCHECK ADD CONSTRAINT [FK_Person_Depot] FOREIGN KEY([Pref_Depot_id])
REFERENCES [dbo].[Depot] ([id])
December 7, 2009 at 3:11 am
Hi All,
Still having terrible trouble with this.
Can anyone give me a pointer please?
:w00t:
December 7, 2009 at 7:26 am
I think what Andrew is trying to say is that you should do the insert in multiple steps in SSIS.
Here is the order I would think you need to load in several Data Flow Tasks:
1. Load Depot
2. Load Person which will use a lookup transform to lookup the Pref_Depot_ID
3. Load Address using a lookup transform to get Person_ID
3. Load Literature using a lookup transform to get Person_ID
3. Load Likes using a lookup transform to get Person_ID
Or you could just load the the data into a staging table and use T-SQL statements to do it all, but you will need to run the statements in the same order as above.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 7, 2009 at 7:32 am
Thanks Jack,
Using a staging table would be fine.
I think I understand the logic of performing the actions in the order that you state.
What I don't know how to do is make the lookup transform (in SSIS I assume?) or the SQL statements know which records to fetch.
The depots are preloaded, and no new depots will come through on this table.
If I insert the details into the Person table, how can I then get the personID back to insert the Likes, for example? The only unique field is the person ID, as I am likely to have many people with the same first and last name.
I know that I am quite new to this, so perhaps I am missing something really obvious. I do appreciate the time taken to respond.
December 7, 2009 at 7:43 am
In order to do a lookup or to even use a Foreign Key you will need to find something that defines a unique person. If you can't define a person as unique, how can you determine which Address, etc... links to that row? It can't work accurately without a way to define a person as unique.
As far as the Depot's go, I am assuming the name in the Depot name matched the PRef_Depot in the source. So you could set up a lookup component in SSIS that uses a connection to the Depot table with a query defined as Select id, name from Depot and your lookup would be Pref_Depot to name with id being the column returned from the lookup. Just be careful because by default the lookup transform is case and space sensitive.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 7, 2009 at 7:51 am
Case and space sensitive. I'll remember that..thanks!
I think, once again, I haven't explained too well. I know which address belongs to which person because it comes through on a table from a supplier as a single record. In addition, within the table the Email address is unique to each person.
However once I load the person into my database, I would need to immediately "grab" the personID, becuase this person is unlikely to be unique in first and last name. There is also, unfortunately (due to historical mishandling of the system) no guarantee that the email address will be unique.
So when I load the person, I need to know what PersonID is used, because this will be the only unique field. Does that make more sense?
December 7, 2009 at 8:10 am
Sure that explains it better, but you still have the likelihood of duplicate people if this is an on-going load process. What if Jack Corbett comes across a second time with all the data the same or with new address? I just want to make sure that you understand that eventually you are likely to have duplicate people, if that's okay with the business then no problem, otherwise you need to try to define a unique person.
You can do what you need to do it is just ugly and will be slow. SQL Server 2005 introduced the OUTPUT clause. The problem is that in your situation you'd need to process row by row (RBAR). I've never done it, but you could do your insert using the Execute SQL Task and output the id in a variable and use the variable in your next task in SSIS. Or do it in a stored procedure that does all the inserts.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 7, 2009 at 8:19 am
Thanks Jack,
I will read up on OUTPUT CLAUSE to see how I can put it to use.
I appreciate your comments about potential duplicates. This is a situation that I am aware of, and currently looking into.
Paul
December 7, 2009 at 3:28 pm
Wish I could have been more help. Hope you get it all worked out.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 11, 2009 at 6:04 am
I haven't really found any useful articles on the use of Output Clauses to enable me to do this task. However I have been able to do it in SSIS, in the way alluded to in this thread.
I inserted into the main tables, then performed a lookup to get the relavent IDs, which I used to insert into the child tables.
It all worked well.
Thanks to all who advised me.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply