Guidance Needed: Loading Hierarchical XML Into Relational Tables

Aug 8, 2007

I've got a lot of XML like this (simplified):




Code Snippet





... 8 MORE


... 9 MORE TIMES






I need to get this into three existing SQL Server 2005 tables, each with identity columns for their primary keys:




Code Snippet
CREATE TABLE ELEMENT1 (

[ID] INT IDENTITY
)

CREATE TABLE ELEMENT2 (

[ID] INT IDENTITY,
[ELEMENT1_ID] INT
)

CREATE TABLE ELEMENT3 (

[ID] INT IDENTITY,
[ELEMENT2_ID] INT
)





With primary and foreign keys as you'd expect, and, of course, many more columns!

How would I get this into tables through SSIS, preferably in a high-performance manner (there may be several gigabytes of XML to load).

The issue, of course, is that in order to insert an ELEMENT2 row, I need the ID from the coresponding ELEMENT1, etc.

Any ideas or pointers to articles would be welcome.

View 14 Replies


ADVERTISEMENT

Storing Hierarchical In A Relational Database

Dec 27, 2005

What is the best approach for storing hierarchical
data in a database? For example, if I need to store a tree menu system,
how would I do that allowing for the most normalization within the
database, using the least number of queries/resources when pulling the
data out, and using the least amount of overhead both in storage and
retrival?

-Chris

View 3 Replies View Related

Help Needed On Loading Different Files To Different Tables

Sep 16, 2007

I am trying to do here is to load different flat files to different tables:
For example, if the file name starts with "enrollment", then it goes to table "enrollment" table;
if the file name starts with "student", then it goes to "student" table.

For now, I created a foreach loop container for the each different files. So it ended up using several foreach loop containers. I am wondering if there is a way just to use one foreach loop containters to process the loading.

Anyone shed some light on this?? Thank you very much for your help!

View 1 Replies View Related

Guidance Needed

Sep 20, 2007

Hello Dear Forum! I am new to this web. I am now days working with .NET Tecs especailly ASP.NET. I have developed a small school system in which i used  SQL MDF file. I want to know about its approach if they want to make it online. and one more question, is MDF file is best for more than 10,000 Students? Can i convert the MDF into normal table format like SQL Server 2005 , i am using SQL Server Express.
Take Care.Wating for reply.
Developer
 

View 4 Replies View Related

Node ID - Loading Hierarchical Data

Aug 3, 2013

CREATE TABLE #Source
(
Id int identity(1,1)
,categoryint
,Leaf_Node_code varchar(10) --
,Level1_Name varchar(20)
,Level2_Name varchar(20)

[Code] ....

Here category 1 has 3 levels ,

category 2 has 4 levels ,
category 3 has 5 levels ,

Below is the target table, here Leaf_Node_code should populate to only for leaf nodes for each category .. Need to populate Node_id with hierarchical data

I am unable frame a sql query to handle different levels , in future #Source may have more levels .

How to handle multiple hierarchy levels .. here only leaf node should have Leaf_Node_code

CREATE TABLE TARGET_TABLE
(
ID INT IDENTITY(1,1) primary key
,Node_id HIERARCHYID
,category int
,Parent_id int references TARGET_TABLE(id)
,Leaf_Node_code varchar(10)
,Namevarchar(20)
)

Here is the expected output:

IDcategoryParent_idLeaf_Node_codeName Node_id
11NULLNULLWorld
211NULLAsia
312101India
42NULLNULLa
524NULLaa
625NULLaaa
726102aaaa
83NULLNULLb
938NULLbb
1039NULLbbb
11310NULLbbbb
12311103bbbb

View 1 Replies View Related

Database Administration/Maintenance - Guidance Needed

May 16, 2006

I have been working SQL server for many years as a web developer, however in my current position we are lacking an expert in maintenance and admninistration as the company has recently switched over to SQL Server 2005.

I therefore need to develop a greater understanding of the best practices with regards to administration and am a bit lost as there are so many options which seem like they might match my requirements. As I have never looked beyond the basic administration/maintenance side of things before I have no idea which options to investigate.

Basically I want to clear any records older than 3 months old on a scheduled/automatic basis. There seem to be various options that could do this, however I want to know what the best option is, rather than waste a lot of time and risk problems by resorting to trial and error.

We are already backing up this data, however I am not sure if I should combine these tasks. It is highly unlikely that we will ever have to restore these older records, however we do need to store the data for legal reasons.

I dont expect anyone to tell me everything, as I am happy to investigate, I just need a nudge in the right direction.

Thanks

maw

View 3 Replies View Related

DTS .ini File Versus SSIS XML Configuration - Guidance Needed.

Apr 25, 2008

Hello

I've been given the task of migrating a DTS package to SSIS (neither of which I am particularly familar with). The first job in the DTS package is to read a .ini file and set a bunch of variables. These variables are then used throughout the DTS package. After running the DTS package through the SSIS migration wizard this job turns into an execute script task and I can't see if it is still reading the .ini file. However, the only real purpose of this step is to allow different parameters to be passed in development, test, production etc. So I am thinking this whole step can be removed and effectively replaced with a package configuration (I'll probably use an XML file). My understanding is that by selecting the name/value pairs as appropriate in the XML package configuration file means this values will be passed in at runtime and achieve the same functionality. Is this the correct way to do this in SSIS, or do I still need the .ini file and variables?

Thanks for any advice on this issue.

Regards, John

View 7 Replies View Related

Fast Loading Relational Data

Apr 8, 2006

I am searching for a way to fast load relation data. I know how to load data fast but how can i store relation data fast.

For example :

Table1 ( tabel1Id int identity , name varchar(255) )

Table2 ( tabel2Id int identity , table2Id , name varchar(255))



When i insert 50 records into Table1 i can't get the 50 identity fields back, to insert the related data into Table2.



I think one of the solutions could be returning a selection of
Table1 joined with syslockinfo, but i have no idea how to do it.



Does anyone have an idea?

View 3 Replies View Related

Relational Tables Are Not Relational After Exported From My Sql Server To Host Sql Server

Dec 25, 2007

hello,
I am beginner for asp.net and sql server. I used Sql server manegement studio full version and I exported my aspnetdb which was created by VS2005 to my host sql server. I have a question: 
relational tables are not relational no longer. I noticed that when I created database diagram. what is wrong by exporting?
thanks for your helps...

View 3 Replies View Related

Hierarchical Query From Two Tables

Nov 17, 2014

I have created a store procedure as below:

WITH TextType AS
(
SELECT AppTxtTypeId,AppTxtTypeCode, AppTxtTypeParentCode, Name,Description,Active,SortOrder ,0 as TypeLevel,AppTxtTypeId as parentId
FROM [ApplicationTextTypes]
WHERE AppTxtTypeParentCode IS NULL

[Code] ....

From this i am able to get data in the below format:

Parent
--Child1
--Child2
---Subchild1-Child1
---Subchild2-Child1
---Subchild1-Child2
---Subchild2-Child2

Actually my requirement is :

Parent
--Child1
---Subchild1-Child1
---Subchild2-Child1

--Child2
---Subchild1-Child2
---Subchild2-Child2

View 1 Replies View Related

Designing Relational Tables

Sep 22, 2007

Hi, not sure if this is the right forum for this question.
 I am creating relational tables for the first time in sql server express. I will have an orderItems table and an orders table. the MenuItems table is the problem. It is a catalogue of books. There will be about ten columns. all are unique to each book. i.e isbn number, title, author, publisher etc. but ten columns seems to be quite cumbersome. it may be easier to break the table down into two tables (i.e. primary details and secondary details perhaps) However to populate the table in the first place it would be easier to have it as one table instead of opening and closing 2 tables Adding the odd book to the two tables in the future would not be a problem. so the question is can i create a table and then brak it into two relational tables afterwards. If so how do i do this. this is my foirst go at relational tables and i am still trying to get a handle on visualising them. If my logic is up the wall please let me know....
Nick

View 2 Replies View Related

Insert On Relational Tables

Apr 7, 2008

I understand the basics for doing an insert. Their seems to be many ways of achieving the same thing in .net. I have used the tableadpaters and sqlcommand functionality to achieve this. My one question is how foregin keys should be created in associated tables. EG so if i create a new record entry in one table and the primary key for that entry is a foreign key in another table, do I need to call 2 table adapters, or run 2 sqlcommands(taking the primary key from the first traction and use this in the next transaction)?

Any help or direction for some good tutorials on this would be much appreciated.

Thanks

Mark

View 4 Replies View Related

What Are The Properties Of The Relational Tables?

Mar 4, 2008



Hello friends .....What is the answer for this question in frame of SQL 2005 Management Studio or SQL Server 2000 ?

What are the properties of the Relational tables?

View 1 Replies View Related

Help Needed -- Dynamically Loading Different Files/types

Nov 29, 2007

I am pretty new to SQL Server 2005 and SSIS. I am trying to develop a package that will dynamically load files into SS2005 based on the contents of a configuration table. The configuration table (see below for example) contains the path to the file, a flag indicating whether or not to process the flag, the type of file (specifies the nature of data -- financial, order, etc.) and some parameters specific to each file.












FileName
ProcessFlag
Type
ExcelTab
Param1

C:File1.xls
TRUE
1
Sheet1$


C:File2.xls
TRUE
1
Sheet1$


C:File3.xls
TRUE
1
Sheet1$


C:File4.xls
TRUE
2
Sheet1$


C:File5.xls
FALSE
2
Sheet1$


C:File6.csv
TRUE
3



C:File7.txt
TRUE
4



Right now I basically have a seperate sequence for each of the file types. The task in each sequence are virtually identical with the exception of the the data flow source in the data flow task (since the source file could be .xls, .txt, .csv). The first sequence ran fine in isolation, but when I linked a second sequence I started getting a Package Validation Error:
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
I use the same variables (FileName, ExcelTab) for each of the sequences, so I am not sure if that causes te error. I also tried changing the ValidateExternalMetadata setting to false, since the connection variable wont be applicable until that sequence is being processed. I am not sure where to go here ... should I rearchitect how the package all together?

Is there a better/most efficient way to to architect this package to handle the different file types (develop a package for each type which is called by a master package, create one package with a different sequence container for each type, etc).

Any help would be greatly appreciated!!

View 5 Replies View Related

SQL Server 2012 :: Index View Won't Work With Hierarchical Tables?

Apr 4, 2015

Consider following code:

SELECT e1.EntityIdentity as CompanyID
FROM dbo.Entitye1 --company
JOIN dbo.EntityAssociationea
ON e1.EntityID = ea.EntityID1
JOIN dbo.Entitye2 --user
ON ea.EntityID2 = e2.EntityID

This query occurs as a sub-query in many stored procedures where exists a WHERE clause that includes CompanyID IN (above query).

Since dbo.Entity and dbo.EntityAssociation change infrequently I thought that an indexed view would really improve performance. But I've found one of the seemingly undocumented Microsoft features when trying to create the clustered index and get the following error msg:

Msg 1947, Level 16, State 1, Line 1
Cannot create index on view "ROICore.dbo.vEntityEntityAssociation_CompanyUser". The view contains a self join on "ROICore.dbo.Entity".

I really need to improve performance on this subquery. Entity currently has over 20m rows and EntityAssociation over 35m rows and both are growing.

How to improve performance? Indexes on both tables for the most part give index seeks, but I thought my saviour might be the index view. Obviously this will not work.

View 3 Replies View Related

Better Practices Wanted For Cascading Inserts Of Hierarchical Data From Staging Tables

Aug 28, 2007

I apologize if this has been asked, but I can't find a complete answer.

We have a situation with parent/child tables which have an identity column as their PK. We need to be able to insert into the live tables from staging tables. The data in the staging tables are related via a surrogate key.

I have found the OUTPUT clause, but that can only refer to columns of the actual table (since there is no FROM clause in an INSERT). Our current best solution to this problem involves adding bogus "staging" columns to the destination tables, and removing them after we've inserted everything from staging. This is an unattractive solution to say the least.

I'll give an example that mirrors our actual solution, and ask if anyone has a better solution?
----------




Code Snippet
CREATE TABLE [dbo].[TABLE_A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DATA] [nchar](10) NOT NULL,
[STAGING_COLUMN] [bigint] NULL,
CONSTRAINT [PK_TABLE_A] PRIMARY KEY ([ID] ASC)
)
GO
CREATE TABLE [dbo].[TABLE_B](
[ID] [int] IDENTITY(1,1) NOT NULL,
[A_ID] [int] NOT NULL,
[DATA] [nchar](10) NOT NULL,
[STAGING_COLUMN] [bigint] NULL,
CONSTRAINT [PK_TABLE_B] PRIMARY KEY ([ID] ASC)
)
GO
ALTER TABLE [dbo].[TABLE_B]
ADD CONSTRAINT [FK_TABLE_A_TABLE_B] FOREIGN KEY([A_ID]) REFERENCES [dbo].[TABLE_A] ([ID])
GO
CREATE TABLE [dbo].[STAGE_TABLE_A](
[A_Key] [bigint] NOT NULL,
[DATA] [nchar](10) NOT NULL
)
GO
CREATE TABLE [dbo].[STAGE_TABLE_B](
[B_Key] [bigint] NOT NULL,
[DATA] [nchar](10) NOT NULL,
[A_Key] [bigint] NOT NULL
)
GO


The STAGING_COLUMN columns are the ones that will be added before, and dropped after.






Code Snippet
DECLARE @TABLE_A_MAP TABLE (
A_ID INT,
A_Key BIGINT
)
INSERT INTO TABLE_A (DATA, STAGING_COLUMN)
OUTPUT INSERTED.ID, INSERTED.STAGING_COLUMN INTO @TABLE_A_MAP
SELECT DATA, A_Key FROM STAGE_TABLE_A
INSERT INTO TABLE_B (A_ID, DATA)
SELECT TAM.A_ID, STB.DATA
FROM STAGE_TABLE_B STB INNER JOIN @TABLE_A_MAP TAM ON TAM.A_Key = STB.A_Key






This seems to work, but I'd really like another alternative. Even though this is happening when nobody else is using the database, I cringe at the thought of adding and removing columns just to make this work.

Here are a few of my constraints:



The above is a simplification of the actual problem. The actual problem goes about five levels deep (hence the B_Key in STAGE_TABLE_B). At the top level, our larger customer will have 100,000 rows to insert. Each level will average 3 times as many rows as the next higher level, so we're talking about real volumes here.

This has to finish over the course of a weekend.

This has to be delivered to QA this Friday
Thanks for any help or insight.

View 3 Replies View Related

Need To Insert Data Into 2 Tables, Relational Having Problems

Mar 7, 2007

Ok, I have a page on my website where we can add products to our database.  We are a music store, and most products have different versions or colors.  I've created 2 tables, Products and Subproducts.  The products table may hold info like Fender Stratocaster, and the subproducts would hold colors (Blue, Sunburst, etc).  The subproducts table has an integer field called MainProductID, which is linked to the mainproducts table field RecordID. So far the page uses a wizard where if first creates the main product using an sql datasource.  After the data has been added to the main products table, my page gives you the opportunity to add different sub products.  The problem I am having is actually feeding in the RecordID from the main products table to my insert parameter on the sub products data source.  This is what I have tried so far: There is a formview on the page that is bound to the main products table, after the entry is created I can physically see the info on my screen, so I know the data is there at my disposal SubProductsDataSource.InsertParameters.Add("@MainProductID", Formview1.datakey.item("RecordID"))SubProductsDataSource.Insert()Using this adds the data to the table, but the MainProductID is nullalso is there a cheap little way to refresh a page, because when I upload the product images I have it go to the next step where you are supposed to be able to see the images you uploaded, I don't see them which makes me think that the page is loading faster than the images are uploading.  Thanks   

View 1 Replies View Related

Insert/Update Relational Tables Using Dataadapter

May 2, 2008

Hi!

I am trying to insert data into 2 different tables. I am using dataadapter and dataset.

Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
Call ConnectionString()

Dim insertSQL As New SqlCommand()
insertSQL.Connection = sqlConn
insertSQL.CommandText = "SELECT location.CountryName, location.CityName, location.BuildingName, location.FloorID, rooms.name, rooms.FloorID AS Expr1 FROM location INNER JOIN floors ON location.FloorID = floors.id INNER JOIN rooms ON floors.id = rooms.FloorID"

Dim ds As New DataSet()
Dim da As New SqlDataAdapter()

da.SelectCommand = insertSQL
Dim scb As New SqlCommandBuilder(da)

Try
da.Fill(ds)
Dim ndr = ds.Tables("location").NewRow
Dim ndr2 = ds.Tables("rooms").NewRow

ndr("FloorID") = FloorIDDDL.SelectedValue
ndr("CountryName") = CountryNameTextBox.Text
ndr("CityName") = CityNameTextBox.Text
ndr("BuildingName") = BuildingNameTextBox.Text
ndr2("name") = RoomNameTextBox.Text
ndr2("FloorID") = FloorIDDDL.SelectedValue
ds.Tables("location").Rows.Add(ndr)
ds.Tables("room").Rows.Add(ndr2)
da.Update(ds)
ErrMsgLbl.Text = "Information saved successfully"
Catch ex As Exception
ErrMsgLbl.Text = ex.ToString
End Try

sqlConn.Close()
End Sub

The above code does not throw any error. It also does not update the tables.

Your help will be appreciated.

Thanks!

View 5 Replies View Related

Union Two Tables With Relational Ordering / Grouping?

Jul 16, 2012

I have two tables with data that I need to get and display in a combobox. What I want to do is have the parent table listed in the combobox with all of its children indented. Sorted by parent then by child.

This SQL seems to be more complex than I have done previously

I can get all of the records from both tables easily:

Code:
SELECT strName, ID FROM tblParents as pp
INNER JOIN tblChildren as cc
ON cc.pID = pp.uiGUID
UNION (SELECT strName FROM tblChildren as c
INNER JOIN tblParents as p
ON c.pID = p.ID)

However, this simply returns a list that is not ordered in any fashion. I'd like to have all of the parent's children shown under the parent name (there is only 1 parent per child and multiple children per parent)

View 2 Replies View Related

Hierarchical Table Functions Vs Hierarchical CTE

Jun 9, 2006

Recently I was in need of a hierarchical tree data. I learned about CTE and how they can be used to build hierarchical data with simple syntax. I used CTE and was through with the task. Later during free time, I tried to compare CTE approach with the traditional SQL 2K Table Function approach. It was surprising to see the query costs when I ran both the modes at one go...

Query Cost (relative to batch) : 0.49%
Query Text : Select * From fn_GetTree(8);

Query Cost (relative to batch) : 99.51%
Query Text : with treedata (id, parentid, status, prevStatus, lvl) as (select ...)


What does that indicate? Does it mean that the Table Function approach is much faster than CTE? I am sure that I was not making unwanted Joins in the CTE mode.

Can someone explain why that huge difference is there? And what the scenarios where CTE is better over Table Functions?

View 8 Replies View Related

SQL XML :: Shred To Relational Tables - Creating Foreign Keys

Oct 7, 2015

I'm shredding the below xml into relational tables. Each element of the xml has it's own table and there is a foreign key to join the tables, you can see this in the below picture. The process I follow is each relational table I always bring the nesecary xml and store it in the table and when shredding I always look at the parent table.So for example when processing the seat table, I use seat xml from the parent route table, also taking the ROUTEID from the route table. The reason I do this is all about taking the id from the previous step to create the relationships between the tables. without taking the xml down to the tables?The problem with this approach is I have xml stored in most tables and the tables are becoming very large.

<Route Type="OneWay" >
<Seat Type="FirstClass">
<Prices>
<Price Price="10" />
<Price Price="11" />
</Prices>

[code]....

View 4 Replies View Related

Create A Relational Diagram From Non-relational Database

Aug 4, 2005

Hi all,
I am trying to create a diagram for our database, during the creating, I create some of the relationships which were not there(basically our original database is not relational database, that's why I am doing it)
So sometimes I have to chage data type in order to create a relationship for the coloumns in different tables. i.e. change char(16) to varchar(7) (I checked the field that make sure all the data in this field is <= 7 characters)

But when I saved the diagram, there is an error message that state:
Errors were encountered during the save process. Some of your database objects are not saved on your diagram.

'agent' table saved successfully
'VisitUSA' table
- Unable to create relationship 'FK_VisitUSA_agent'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VisitUSA_agent'. The conflict occurred in database 'CMC', table 'agent', column 'AgentCode'.

What does that mean? is it caused by some of the agentcode data in VisitUSA table which is not in agent table?
Thanks!
Betty

View 3 Replies View Related

Loading Of Tables

Mar 19, 2001

We are trying to load flat text files with upwards of 7 million records into a table on SQL. The table has a clustered index on 3 fields. We are sometimes able to complete smaller tables (500,000-750,000 records) and build the indexes prior to importing the data, however when we try the larger tables an error occurs :

Error at Destination for row number 6785496. Errors encountered so far in this task: 1

Location: somerge.c:1573
Expression: mrP->mrStatus!=MERGERUN::NONE
SPID: 11
Process ID: 173

None of the recods end up importing. The row number it gives is always the total number of records that was in the text file I was trying to import. I tried to import the text files first and then build the clustered indexes but a table with only 300,000 records ran for nearly 4 days without completing before we killed it.

Please help me on where to look......:(

Thanks,
Cheri

View 3 Replies View Related

Loading Data From XML To Tables

Dec 1, 2007

Hi,

I have got an xml file with size more than 2 GB. I have to load this file into tables. With 32 bit platform, I am unable to load this file using SSIS. Ram is 8 GB, but it is still bombing out. As I know it uses XML DOM Parser and tries to shred the file in memory and because of memory limition, it fails. Although I have already written code in C# using XmlTextReading object(implemetation of SAX Parser) to load data in tables, but I want to keep this loading process within the limits of DBAs.

I am stuck. Can someone guide me through the situation?

Thanks for your help!

Navnish

View 8 Replies View Related

Loading Data Into Columns 75 - N In Tables?

Jun 23, 2004

I'm having a very irritating time trying to migrate data from a COBOL system to SQL Server.

One of the A/R Master files has approx. 200 columns.

I can export this file any number of ways that will (sometimes) load partially into my database, but always when the load succeeds, columns 75 through N simply contain NULL, even though there is data in the file. When the load fails in DTS, the error is always missing column delimiter. Using BULK INSERT the error is always something like data too long at column 75.

Putting all this together, I have deduced that something isn't working if I try
to load a staging table with more than 74 columns of data. This seems like a way-too-low threshold for a robust database, especially since SQL Server is supposed to be able to handle up to 1,024 columns per table.

Has anyone ever encountered this problem?

Thanks in advance for any help

View 2 Replies View Related

Loading Tables With Foriegn Key Contraints

Aug 31, 2006

Hi,

I am having trouble loading tables (within the same data flow) that have a foriegn key relationship defined between them. For instance:

Table A is a parent (one side of the relationship) to Table B (many side of the relationship).

I am trying to load Table A first within the data flow and then Table B after, but I get the following error:

[OCMD EntityRole Insert [2666]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EntityRole_Entity". The conflict occurred in database "ODS", table "dbo.Entity", column 'EntityGuid'.".

I am currently using OLE DB commands to perform the inserts, I load table A and move on to then load Table B, I can see the records in Table A before trying to load Table B but for some reason Table B load still fails.

I was thinking maybe this has something to do with the transaction setting or Isolation level but have played with this to no avail (currently everything is the default - supported/serializable). Also, I am thinking maybe because the OLE DB commands are creating two seperate connections (they are using the same connection manager) the second one is unable to see the transactions from the other (first) connection (Table A)?

Is there a way around this without dropping (disabling) forigen keys before the load and adding them back in after? Would like to avoid this?

I would also like to avoid reading the data source multiple times. Everything I need is in the one source so I would like to populate multiple tables from the one source data stream instead of reading the same data 2,3 or 4 times etc.

Seems to me there must be a simple explanation/solution for this but I'm stuck at this point?

P.S.

I was intially using OLE DB destinations (because they are much faster) and was having the same issue, which made sense because the OLE DB destinations do not let you pass the data stream on so I had to multi cast to the destinations so they were loading at the same time. I would rather use the OLE DB destinations so if you have any ideas around how I could do this using those components that would be appreciated too!

Thanks!

View 3 Replies View Related

Loading Data In SQL Server To Related Tables (how)

Jul 14, 2004

Hi I have a question how to load data to tables linked by Foreign Keys in MSDE/SQL server. Example:
If I have 2 tables linked (by Foreign Key):
One table:

ITEM idITEM NAMEITEM CATEGORY (FK)
1cheese 2

And another:

Category IDCATEGORY NAME
1 household
2 food
3 general

How do I enter the load of data
Do I have to enter it as
1cheese2
or is there some way of entering it as
1cheesefood

TDS wizard does not allow me to transfer to views/querries what I thought would be a normal way as I would enter data to view(relevant to Access's form) and it would update related tables . When I wrote sql to do it it said I can not update my view table as too many tables would be affected(I had lookup tables empty then though)
I am doing it by number using TDS wizard to transfer it directly to the main table but there must be a better way

View 1 Replies View Related

Loading Tables With An Identity Field Using DTS Packages

Aug 2, 2001

Is there a way ,(if so what is the syntax?), to set up a DTS package
that loads a table that has an identity column. I am trying to load the data from another table, (leaving the identity field unmapped), and de-selecting the "enable identity insert" from the advanced tab of the Data Transformation Properties window. I keep getting errors due to the table not allowing null values. I tried using the set_identity command, but this still did not work.
Any help would be appreciated.
TB

View 1 Replies View Related

SQL 2012 :: Loading XML Files Into Tables By Using SSIS?

Apr 6, 2015

Currently we are trying to load the xml files into sqlserver tables by using ssis 2012,We are getting xml files as a column in source table ,so we have to push these xml files into destination tables.

I'm following the below way to perform this activity

[URL]

But We have standard XSD structure for all the xml files ,and if xml file matches the XSD structure then only we have to load ,else it should skip to next xml file.

View 1 Replies View Related

LOADING TEMP TABLES AND MULTIPLE SEARCHES

May 6, 2008

Hi Guys,

I have to work with a poorly designed table :(, that has columns

IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
Description varchar(8000) null,
ProductTypevarchar(255) null,

The reason it is poorly designed is the table is used to hold questions and answers, all with a 1:1 relationship. Instead of having ID, ProductType, Question, Answer they have unfortunately adopted the approach of the above i.e:

id 1
thisID 3
parentid nuLL
DESCRIPTION: this is a question

id 20
thisID 3_1
parentID 3
DESCRIPTION: this is the answer to the question above

So I am writing a sproc that does this using a temp table. I got this far:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Spencer
-- =============================================
ALTER PROCEDURE [dbo].[GetFAQs]
-- Add the parameters for the stored procedure here
@ProductType varchar(255)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE TEMP
(
IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
DescriptionQvarchar(8000) null,
DescriptionAvarchar(8000) null,
ProductTypevarchar(255) null,
)

SELECT
ID,
thisID,
parentID,
Title,
DescriptionQ,
DescriptionA,
ProductType
FROM
A2Z
WHERE
ProductType = @ProductType AND parentID IS NULL
END
GO

This gets all my questions for that product type.

What I need to do is load the questions into my temp table and then run through the a2z table again gaining the answers to the questions (the parentid holds the question ID). The answers then will also get loaded into the temp table.

Any bright sparks out there that can help me?

Cheers

View 9 Replies View Related

Loading Multiple Excel Sheets In To Different Tables In A DB

Nov 21, 2007



Hi All,

I have problem in loading multiple excel sheets data in to according to that excelsheets tables in a DB.
All the excel sheets are in a folder,from that folder i have to acces all excel sheets.
For this i am unsing script task and one dataflow task.
But the error is coming in script task i am not able to put the path in the script..

Is this the correct way to do like this? Or any other way?

Can u please tell me the solution for this..Thanks in advance who are responding to this mail...

Maruthi..

View 9 Replies View Related

Error Loading Tables Of Different File Groups.

Apr 6, 2006

Hi All,

I am facing a peculier problem. Problem definition goes like this,

I have one staging DB in which all the tables resides in Primary file and one production DB in which tables resides in 2 secondary files.

Now when iam trying to load the data from the table A in staging which is on primary file to the table A1 in production DB which in secondary file, all the data are going to error log instead of table A1.

Can you please tell me, where am i going wrong.

Regards,

Chetan

View 2 Replies View Related

Loading Data Into Dimensions &&amp; Fact Tables

Jul 10, 2007

Hello Everybody,



Can anybody please let me know the procedure for loading data into Dimensions & Facts? And what is the sequence of loading?



Thanks in Advance

Rajesh

View 1 Replies View Related







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