XML Into SQL Tables With Relationships

Aug 21, 2006

Hi,

I'm trying to find the most efficient method of importing XML files into SQL Server using SSIS. The objective is to read these files created in a source directory and potentially there will be a large number of these created every second.

I've experimented with the XML Source data flow task and been able to import using either the OLEDB Destination or SQL Server Destination.Using the associated XSD file

These methods work well when there are no PK or FK relationships, i.e. the target tables are stand alone.

However when I use a schema that has relationships I need to be able to import and be able to return the key then use this key to import into another table to ensure the FK relationship and so on.....

From what I know I will need to create multple Data Flow tasks when reading a single xml file then in turn create a 'look up' on a table to see if this entity exists and on error insert using the OLEDB command and then somehow return the PK generated???

Has anyone tried this? And would be happy to share there experiences?

It seems a lot easier to use an SP and then use an execute SQL task method

Your thoughts would be greatly appreciated

Thanks

John

View 13 Replies


ADVERTISEMENT

For What Are Good Relationships Between Tables?

Jan 25, 2008

hello,
i am wondering, for what i use relations between tables? if i make sure that the data inserted into the table matches, for what i need table relationships?
i have a table named Pictures with: PictureID, UserID, UserName, PictureName, Description...
do i have to make relationships between UserID from Pictures and UserId from aspnet_Users?
and the second table PicturesComments witch has: CommentID, PictureID, UserID, UserName...
do i have to makre relationship between PictureID from PictureComments and PictureID from Pictures AND between UserID from Pictures and UserId from aspnet_Users?
sorry for this stupid questions...
thanks

View 5 Replies View Related

Setting Up Tables And Relationships?

Mar 12, 2014

I am creating a small piece of software which handles a pre-advice data file, several scanner inputting weights and a supplier weight report.

The pre-advice contains a list of barcodes and details revolving around that barcode, each barcode will be scanned and a weight will be obtained, this will be place in the DB.

Currently my tables can be summarized like this.

Pre-Advice Table
Barcode

Lots of other info…

Scanners Table
Barcode
Date
Time
Weight

Which device input this information

Suppliers Weight Table
Barcode
Date
Time
Weight

Here is the process(es):

1.Pre-advice table populated.

2.When the barcode is scanned and a weight is obtained, a record will be added to the scanners table and this becomes the ‘active’ weight.If there is already an ‘active’ weight against this barcode the user will be asked if they wish to use the ‘active’ weight already in the system or update the ‘active’ weight to the weight they have entered.

3.Then when the supplier weight report is inputted into its table, should the ‘active’ weight be less than the supplier weight, the supplier weight becomes the ‘active’ weight.

At any given time a user can pull a report based on information in the Pre-Advice table, which will need to include the ‘active’ weight for that barcode’s record.

Additionally, no weights should be deleted, I need a fully traceable log of who, what, where & when. I will need to pull reports on the trials of scans so it really is a must to keep these.

Furthermore, if a scanner was to scan the same barcode after the Suppliers weight had been input and the user chooses to replace the weight then, the scanners weight should become the new ‘active’ weight.

I believe I will need to modify my tables to accommodate my needs, however I am not sure exactly what or how to set up what I need.I understand I will need some type of relationship between all of the tables to be able to pull the report on the Pre-Advice table, but I am lost.

how I will need to set up my tables/relationships in order to achieve my requirements?

View 3 Replies View Related

Relationships Between Tables In Different SQL Databases

Jul 27, 2007

There are several databases (currently in Access) that are being moved to SQL Server. Would prefer keeping those databases separate in SQL Server. How do you do something similar to Access's "link" capabilities and relate tables in different physical databases? For example, relate the authors of a document in a document database to persons in a people database where AuthorID in Docs.DocAuthors.AuthorID is related to People.Persons.PersonID

View 4 Replies View Related

Insert Into Tables With Relationships

May 15, 2008

Hi, I have two tables. One is called ContentRecord and it is one field, an identity field that is a primary key. I have another table that is has several fields including a field that is a foreign key called contentFK. I have set up a foreign key relationship between the tables. There is a one to many relationship. I would like to know the best way to add a record to both tables. The way I am thinking to do it is to do the insert statement to add the record to the ContentRecord which will return the value of the primary key field. Then do a seperate insert statement to insert the record into the second table where I set the contentFK equal to the returned value from the first statement. Does that make sense? Is there a better way to do this?

Thanks,
Laura

View 4 Replies View Related

How Do I Transfer Tables And Relationships From One Database To Another?

Aug 24, 2006

Hi, I'm working on Sql Server 2005 Express and I'm trying to transfer over some of my tables with the primary and foreign keys relations as well as the data in them from one asp.net 2.0 website to another.But I can't seem to find the Import/Export option in Sql Server 2005 Express Edition, does anyone know how I can do this?Thanks in advanced.

View 1 Replies View Related

Using Code To Maintain Relationships Across Tables.

May 17, 2002

Can anyone give me an idea like, what percentage of organizations use 'code' to maintain the parent-child relations on their tables than
having FK constraints thru the db model? Because,all the companies that I worked with used 'code' to control the relationships across the tables(not the PK/FKs.!!)
Thanks.
Neil.

View 2 Replies View Related

Tables Export 'Success' But No Relationships...

May 29, 2003

I export all tables from serverA databaseAA to serverB (both SQL2k sp3)databaseAA with 'Success', but without any relationships. In SQL6.5 Tools of Transfer will bring all objects include relationship.

I also tried export "All Objects" which fails with unclear message. I just don't want to do a backup and restore....

thanks
David

View 9 Replies View Related

How To Implement One-to-one, One-to-many And Many-to-many Relationships While Designing Tables?

Mar 20, 2008



Can Anybody explain me this how to Do this ?

Is this done by definning the Primary key and foreign key constraints ?

View 1 Replies View Related

Discover Relationships Between Existing Tables Of A Database

Mar 23, 2007

Hello Friends,
I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules.
I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.

View 2 Replies View Related

Truncate Tables Based On Foreign Key Relationships

Nov 5, 2007

Guys,

I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.

For example

EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table

My truncate script should be

TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE

IS there any automated way to figure out parent and child tables and generate truncate script for the same.

Thanks

View 3 Replies View Related

Creating Tables In SQL Server Via Query Analyzer With Relationships

Jun 7, 2005

I've been searching around for some info on how to set this up, but with no luck.I need to have a .sql file that will set up a few tables and these tables will have relationships and contraints.I can do this by hand in enterprise manager, but need to set up some procedures that will do the same thing.For instance, I can create the tables just fine.....CREATE TABLE students ( sId int NOT NULL PRIMARY KEY,        studentId varchar(50) NOT NULL,               course varchar(50)              )
CREATE TABLE courses  ( cId int NOT NULL PRIMARY KEY,        course varchar(50) NOT NULL,               sco varchar(50)              )But, I need to set up relationships in there somehow.Once student may have many courses (one to many) and one course may have many sco's (one to many) SCO would be another table.Can someone point me to a good link that would show how to complete these procedures?Thanks all,Zath

View 4 Replies View Related

Get New Database Created Then Running Script To Created Tables / Relationships

Jun 29, 2015

trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication. 

E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".

From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName]   Go   CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?

View 3 Replies View Related

One To One Relationships

May 29, 2007

 Ok, so I open up a book on beginning sql and it discusses relationships.There is something that appears wrong. I am not understanding how the book is describing the one to one relationship. In it's figure, it shows that the same agency can have two different artist id's. But according to it's definition of one to one list can be related from both tables.Can someone explain this further please?Thanks in advance.

View 3 Replies View Related

ADO.NET - Many-To-Many Relationships

Aug 8, 2007

How do you show many to many relationships in ASP.NET with datasources?
 I can retrieve the information from the database easily but I can't manage to save that information into a variable so that i can then call for only information in the M2M table related to the ID Key I want.  For example..1 <asp:Label ID="lblTestLabel" Text='<%# DataBinder.Eval(Container.DataItem, "title_id") %>' Runat="server" />
2
3 <script language="VB" runat="server">
4 sub Page_Load(sender as Object, e as EventArgs)
5 Dim lblTitleID As Label = Repeater1.FindControl("lblTestLabel")
6 Response.Write(lblTitleID.text)
7 End Sub
8 </script>

 When I try to run this script it dosn't work and I get the error:
 System.NullReferenceException: Object reference not set to an instance of an object.
For line 6 of the code above.  Is there a way to just retrieve information from a datasource without storing information into a label first?
PS: I use sqldatasource tags to retrieve information like:1 <asp:SqlDataSource ID="DataSet1" runat="server"
2 ConnectionString="Driver={MySQL ODBC 3.51 Driver};server=localhost;database=;user=;pwd=;option=3;"
3 ProviderName="System.Data.Odbc"
4 SelectCommand="Select * from titles order by title_id">
5 </asp:SqlDataSource>
 

View 2 Replies View Related

Many To Many && Other Relationships

Aug 10, 2007

Can someone help me sort something out. Suppose we have two tables in a database.One is named Person, and one is named Birthday. Is this a many to many relationshipor a 1 to many relationship?
Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life. birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.

View 6 Replies View Related

Relationships...

Feb 2, 2004

Is there any easy way of getting all the relationships for the tables in aSQL server db? I've looked through Google and the Books online but I'm barely scratching the surface on the systables. I know I can go to each individual table and get the dependencies or even print out the diagram through Enterprise Manager but I really don't want to have to type out all the primary/foreign keys for 100+ tables. Any help on getting this data either through a 3rd party software or querying the systables would be great.

THT
DMW

View 5 Replies View Related

One-to-one Relationships

Mar 21, 2001

We have a table with approximately 150 colums and 2/3 of them are bit datatypes. We do not expect much activity on this table. Does it make sense to separate it into a few tables instead of 1 with one-to-one relationships? Will the join(s) significantly slow down our web application? Thank you

View 2 Replies View Related

Are Relationships Necessary

Aug 6, 2004

My hosting servers have "myLittleAdmin" installed. I am new to ms sql I have some experience with Access but I have never set up Relationships inside the database server. I have always just relied on my application logic to call the relations. What is the benefit to setting up relationships in sql, if any?

View 4 Replies View Related

One To Many Relationships

Nov 11, 2004

Is there a tool in Enterprise manager to list out the one to many & many relationships with a database?????:?:

View 4 Replies View Related

Relationships

Feb 1, 2007

Hey, I have SQL Sever Enterpise Manager. I am new at this company and I need to know the relationships between ALL the tables. Is there anywhere I can go to view all the primary keys and the relationships?

View 3 Replies View Related

Key Relationships

Jun 1, 2007

I am viewing tables in the ‘object explorer’- I can see the tables. If I open it up I can see columns, keys, constraints etc…

So I can see they key… can I tell which table/field the foreign key is connected to?

thanks

View 10 Replies View Related

Many To Many Relationships

Oct 17, 2007

Hello,

I have 3 tables. Posts, PostsTags and Tags.
This is a many to many relationship.

So PostsTags has the following columns:
PostId (PK) and TagId (PK)

I always did that way in SQL 2005.

In some examples I see PostsTags have 3 columns:
PostTagId(PK), PostId (FK), TagId (FK)

Should I use it this way, instead?

Thanks,
Miguel

View 8 Replies View Related

Relationships

Jul 13, 2006

Is it possible to have 2 different databases and create relationships inbetween the two? I am thinking about way down the road and the size of the databases and I believe that I need to split my data into multiple databases to keep from running into the size limitations.

Davids Learning

View 1 Replies View Related

One To One Relationships

May 29, 2007

Ok, so I open up a book on beginning sql and it discusses relationships.
There is something that appears wrong. I am not understanding how the book is describing the one to one relationship. In it's figure, it shows that the same agency can have two different artist id's. But according to it's definition of one to one list can be related from both tables.

http://foo.gearsector.com/foo/instr_scrns/oneToMany.gif


Can someone explain this further please?

Thanks in advance.

View 5 Replies View Related

RelationShips In SQL

Apr 1, 2008



How can I use with relationShips in SQl?
Tamir BLoch .

View 1 Replies View Related

Relationships Vs. No Relationships

Apr 3, 2007

Hello!



I have a really small database in terms of number of objects, but very big in terms of data (possibly millions of records). I always try to design my databases to be in the 3rd normal form (at least I hope they are!), but sometimes (for databases consisting of just a few tables) maintaining M:N relationships looks like an over-bloated approach to me... Let's see an actual example. I have a table like this:






Code SnippetCreate table [Airfares]
(
[OriginCity] Char(3) NOT NULL,
[DestCity] Char(3) NOT NULL,
[FareDate] Datetime Default getdate() NOT NULL,
[Price] Decimal(9,2) Default 0 NOT NULL,
[PriceChange] Decimal(9,2) Default 0 NOT NULL,
[Wrong] Bit Default 0 NOT NULL,
Primary Key ([OriginCity],[DestCity],[FareDate])
)
go







Now, at first there also was an Airline nvarchar(50) field, which stored the airline name. But now the client requested to store fares for a few airlines if it's the same price and city pair. This way we can have 3 airlines offering the same price on the same itinerary for example. So I added a dictionary table called Airlines:






Code SnippetCreate table [Airlines]
(
[AirlineCode] Char(2) NOT NULL,
[Name] Nvarchar(50) NOT NULL,
Primary Key ([AirlineCode])
)
go







Now, there are two things I can do. We are obviously dealing with the M:N relationship type here. I can either add another nvarchar column in the first table, like Airlines nvarchar(255) and store something like 'AA,AB,AC' there - those would be airline codes. OR I could do it the proper way and create a cross-reference table which will connect both Airfares and Airlines tables (this will store as many records as there are airlines for that particular itinerary, offerring the same price):






Code SnippetCreate table [AirfareAirlines]
(
[AirlineCode] Char(2) NOT NULL,
[OriginCity] Char(3) NOT NULL,
[DestCity] Char(3) NOT NULL,
[FareDate] Datetime Default getdate() NOT NULL,
Primary Key ([AirlineCode],[OriginCity],[DestCity],[FareDate])
)
go







But this is starting to look overly complicated, isn't it? The pros of this are I'm going to get real relationships, referential integrity, etc., but cons are more difficult code to query all this. What would you do? Do we really need referential integrity everywhere? Are there times when we're better not using relationships? I have seen many programmers skip referential integrity before, which always made me think they are missing something important in a Relational Database Management System.

View 13 Replies View Related

Relationships In SQL 2005?

Feb 13, 2008

Hi
How I can get to see the relationships, like in access, in sql 2005?
Thank,
K

View 1 Replies View Related

An Easy One: The Need For Relationships?

Mar 16, 2008

 Ok - I am still a bit weak on SQL.  My understanding of FK relationships in a database are to reinforce the data integrity - Correct?  For example, does it make sure that the id given for SiteID does indeed exists.  Can it link tables like JOIN in select statements? If not, is there any gains by creating them, such as performance?

View 1 Replies View Related

I've Never Been Good With Relationships

Feb 15, 2004

I'm having some trouble working out how to query some data. Rather than explain up front, here's some examples of what I want to achieve:

*******************************************************

I've got a structure which looks vaguely like this:

[ANCESTORS]

Grandparent
Parent
Child

If limit by grandparents, then I only get the lineage for that particular grandparent. I.e.:


SELECT *
FROM [ANCESTORS]
( some sort of joins here )
Where Grandparent.Name = 'Cybill'



This would return all of the children of 'Cybill' and their children. Now, if I use the following query:


SELECT *
FROM [ANCESTORS]
( some sort of joins here )
Where Child.Name = 'Jean'



This would return Jean's parents + the parents of Jean's parents (Jean's grandparents).

Likewise, if I enter:


SELECT *
FROM [ANCESTORS]
( some sort of joins here )
Where Parent.Name = 'Ron'



Then I would get Ron's parents and also his children.


*******************************************************

So, as you can see, at first it appears that I'm after a LEFT JOIN - meaning that the grandparents don't need to have child records to be returned, but, then it turns out that I need INNER JOINS - to limit grandparents when I choose children.

Can anybody see my dilemma here?

Mark-up ASP.net posts here
MarkItUp.com

View 10 Replies View Related

Relationships In MSDE - How Do I Do It?

Nov 27, 2005

Hi
I am using the Web Matrix Project to write a .NET
application.   I have set up a database using MSDE which I
accessed through the Web Matrix Project to start with.  I have now
found the Web Data Administrator which I use to backup my database and
help to transfer it to the Server where I hope to run this application
(if I ever get it finished!).

I need to set up some relationships between database tables but cannot
see a way to do this.  SQL Server Books on Line referes to
Enterprise Manager which I believe ships with the full MS SQL Server
package (and costs a package).  I am doing this on a
shoe-string.  Any ideas on how to do it?

Thanks in advance.

Mike

View 2 Replies View Related

Same Old Question About Relationships

Sep 11, 2003

I wanted to know that whether using relatioships betwenn tables improves performace for select queries?

what is the significance of using joins in queries after implementing relationships?

thanks

View 4 Replies View Related

Database Relationships

Aug 22, 2006

While playing about with SQL Server 2005 I am sure that I saw a tool to do relationships the same was as in access - however now I can't find it.

Am I seeeing things or does this really exist?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved