Transact SQL :: Order Change In Parent To Its Child Tables Using FK Relations?

Apr 20, 2015

I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.

Link: [URL]

--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child

--When I am deleting a grand child table, it should be linked to child table first followed by Parent

--- Query 1

DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';

--Query 2

DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM  [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID] 
INNER JOIN
[dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';

how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2

View 15 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Order Change In Parent To Its Child Tables Using FK Relations?

Apr 20, 2015

I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.

Link:

[URL]

--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child

--When I am deleting a grand child table, it should be linked to child table first followed by Parent

--- query 1

DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';

--Query 2

DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID]
INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';

how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2.

View 1 Replies View Related

Transact SQL :: Parent / Child Tables - Pivot Child Data To Parent Row

May 19, 2015

Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row.  So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:

1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31

declare @parent table (Id int not null primary key, StartDate date, EndDate date)
declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date)
insert @parent select 1, '1/1/2015', '12/31/2015'
insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'

[Code] .....

View 6 Replies View Related

Transact SQL :: To Get Parent / Child / Grand Child Row On Various Order?

Jun 26, 2015

I have a table with below kind of data,

DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL

Here I want,

1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)

Looking for below output ...

View 3 Replies View Related

Transact SQL :: Parent Child Data With Different Order By Clause?

Jun 24, 2015

I'm asking my previous question in this new thread with more specific data and condition.I have below sample data,

DECLARE @DATA TABLE (U_ID INT, U_NM NVARCHAR(20), U_DT DATE, U_ORD INT, P_U_NM NVARCHAR(20) NULL)
INSERT INTO @DATA VALUES (1, 'Design', '06/15/2015', 2, NULL), (1, 'Plan', '07/01/2015', 1, NULL), (1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'), (1, 'Cust Plan 2', '06/05/2015', 0, 'Plan'),
(2, 'Design', '06/25/2015', 2, NULL), (2, 'Plan', '06/20/2015', 1, NULL)

We have 2 different U_ID (1, 2) and I want a SELECT query to display,

1. For U_ID = 1, we have 2 parent U_NM (Design & Plan) and Plan having 2 child (Cust Plan 1 & Cust Plan 2).
2. I want to display parent U_NM ORDER BY U_ORD
3. If any parent having child element, then need to show immediately under that parent and ORDER BY U_DT
4. For U_ID = 2, we don't have any child, hence display ORDER BY U_ORD

View 10 Replies View Related

Tables In Parent-Child Order

Nov 8, 2005

Greetings,I just wanna know if anyone can tell me how to get all user definedtables in parent-then-child manner. I mean all the parents should belisted first and then childs.I dont think there is any direct way to do this, but i am not able toform any sort of query to achieve this.Any help will be greatly appreciated.TIA

View 7 Replies View Related

Transact SQL :: Parent Child Grouping

Nov 4, 2015

I have a table as below

ItemID ParentID
6 NULL
7 NULL
8 7
11 7
12 8

Here ParentID null means it does not have the parent its the master. I want result in below format.

ItemID ParentID
6 NULL
--No parent no child
7 NULL
--No parent but has child
8 7
--7 is the parent of 8
12 8
--8 is the parent
11 7
--7 is the parent

Basically it should be in parent id then child id after that child's childID , In a recursive way.

View 5 Replies View Related

Transact SQL :: Need To Get Parent Child Data

Jun 24, 2015

I have a table with parent child relationship data.

DECLARE @DATA TABLE (U_ID INT, U_NM NVARCHAR(20), U_DT DATE, U_ORD INT, P_U_NM NVARCHAR(20) NULL)
INSERT INTO @DATA VALUES (1, 'Design', '06/15/2015', 2, NULL), (1, 'Plan', '06/01/2015', 1, NULL), (1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'), (1, 'Cust Plan 2', '06/05/2015', 0, 'Plan'),
(2, 'Design', '06/25/2015', 2, NULL), (2, 'Plan', '06/20/2015', 1, NULL)
SELECT * FROM @DATA

1. For U_ID = 1, we have two diffrent U_NM and for one U_NM we have 2 child data.  Need to show parent data order by U_ORD and need to show child data within their parent order by U_DT

2. For U_ID = 2, we don't have child data, hence need to show data order by U_ORD only

SELECT 1 AS U_ID, 'Plan' AS U_NM, '06/01/2015' AS U_DT, 1 AS U_ORD, NULL AS P_U_NM
UNION ALL SELECT 1, 'Cust Plan 2', '06/05/2015', 0, 'Plan' UNION ALL SELECT 1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'
UNION ALL SELECT 1, 'Design', '06/15/2015', 2, NULL UNION ALL SELECT 2, 'Plan', '06/20/2015', 1, NULL UNION ALL SELECT 2, 'Design', '06/25/2015', 2, NULL

View 3 Replies View Related

Transact SQL :: Display Child Data Under Parent Row

Jun 25, 2015

I would like to display child row right after parent row with ORDER BY DataDate for below set of data.

DECLARE @DATA TABLE (DataUID INT PRIMARY KEY IDENTITY(1,1), DataId INT, DataName NVARCHAR(20), DataDate DATE, ParentDataName NVARCHAR(20))
INSERT INTO @DATA (DataId, DataName, DataDate, ParentDataName)
VALUES (1, 'child plan 1', '2015-06-09', 'Plan'), (1, 'child plan 2', '2015-06-08', 'Plan'),
(1, 'Design', '2015-06-01', NULL), (1, 'Implement', '2015-06-01', NULL),
(1, 'child Implement 1', '2015-06-09', 'Implement'), (1, 'child Implement 2', '2015-06-10', 'Implement'),
(1, 'Plan', '2015-06-01', NULL), (1, 'Operate', '2015-06-01', NULL)
select * from @DATA

1. as a example the child row 'child implement 1' & 'child implement 1' show correctly under parent 'Implement' with Order BY DataDate.

2. I'm looking for a SELECT query which should display 'child plan 1' & 'child plan 2' under parent 'Plan' with Order BY DataDate clause.

Below is the expected output I'm looking for,

View 6 Replies View Related

Transact SQL :: Recursive CTE Parent Child Query

Oct 31, 2015

I have a Recursive CTE for TFS database which gives me below results:

Parent (User Story) level 0
             -------------.Task(Dev) and ask(QA) --

level  1
    BUG level 2
                ---------------Task (Dev) and Task(QA)
level2 

So, My ParentID column keeps two ParentId one for User story which keeps the child tasks and child bugs and another for Bug Child tasks

I need to update this results and want to see all of the tasks under User story so the result I want is:

User Story:

 Task(dev and QA)
 Task (Dev and QA ) but its should know that these tasks are the bug tasks.

View 10 Replies View Related

Transact SQL :: Make Tracks Or Paths Of Parent Has Many Child

Nov 19, 2015

I have a  tblActivity table :

ActivityID ActivityName
-----------------------------
1 A1
2 A2
3 A3
4 A4
5 A5
6 A6
7 A7
8 A8
9 A9
10 A10

and related it in tblParentActivity table with parent child relationship as:

ParentActivityID ChildActivityID
-------------------------------------
1 2
1 3
2 4
2 5
3 6
4 7
5 7
6 8
7 9
8 9
9 10

I want to write a SQL query which will make it in tracks depend on how many childActivity in one parentActivity like

ActivityID TrackGroup
-------------------------
1 1
2 1
4 1
7 1
9 1
10 1

[code]....

View 8 Replies View Related

Parent Child Tables

Jul 20, 2005

In our database we have a list of devices in a "Device" Table, eachhaving one or more IP's located in the "IP" Table linked through aforein key on the DeviceID Column.I would like to retrieve this information as SuchDeviceID IpAddress1 10.0.0.1, 10.0.0.2, 10.0.0.32 ...345etc.Is it possible to do that without using cursors? Through a query?

View 1 Replies View Related

Transact SQL :: Use FOR XML To Return Multiple Child Records Within Each Parent Record

Aug 7, 2015

I have a single complex query.

SELECT 
Col1, -- Header, 
Col2, -- Header, 
Col3, -- Detail
Col4, -- Detail 
Col5, -- Detail
FROM 
TableName;

The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using - 

SELECT 
Cols 
FROM 
Table Names 
FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL 

This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format - 

<Header >
  <detail 1>
   </detail 1>
  <Detail 2>
  </Detail 2>
  <detail 3>
  </detail 3>
</Header>

View 2 Replies View Related

Transact SQL :: Recursive Query To Find Child Of A Parent Until Last Leaf

Oct 22, 2015

I need to write recursive query to find child of a parent until the last leaf. Below is my code. 

;WITH Parent AS(
SELECT [ParentID],Value
FROM[DynamicColsValues_TP1]
WHEREValue IS null
UNION ALL
SELECT t1.[ParentID],T1.Value, FROM DynamicColsValues_TP1 t1 INNER JOIN Parent t2
ON t1.[ParentID]=t2.[ParentID]
)
SELECT * FROM Parent option (maxrecursion 0)

When I execute this code. It is returning me millions of rows. Whereas  i have only 20 rows in a table max 40 rows it should return.

View 7 Replies View Related

Transact SQL :: Set Child Records To Inactive When Parent Record Deleted From Table

Oct 16, 2015

I need to create a trigger to meet following conditions.

When parent record is deleted from UI record becomes inactive in table. i need to create a trigger when this happens.

When parent record is deleted child records needs to be inactivated in table.

View 12 Replies View Related

Check Constraint Across Parent-child Tables

Jul 20, 2005

Hi,DDLs and DMLs:create table #job (jobID int identity(1,1) primary key, jobNamevarchar(25) unique not null, jobEndDate dateTime, jobComplete bitdefault(0), check (([JobEndDate] is null and [JobComplete] = 0) OR([JobEndDate] is not null and [JobComplete] = 1)));Q1 with check constraint:sample dagtainsert into #job (jobName)values('first job');transaction Aupdate #jobset jobEndDate = '12/19/2003', JOBCOMPLETE=1where jobID = 3;RESULTSET/STATUS = Successupdate #jobset jobEndDate = NULL, JOBCOMPLETE=0where jobID = 3;RESULTSET/STATUS = Successtransaction Cupdate #jobset jobEndDate = '12/19/2003'where jobID = 3;RESULTSET/STATUS = Failurehow come check constraint can't set a value which is preset in thecheck constraint? If it's the way how it works with MS SQL Server2000, well, IMHO, it's limiting because the above transaction C is avalid one. Or maybe check constraint is not fit for this purpose?Maybe, it doesn't make much sense for me to go into Q2 but I'll try-- create job's child table, taskcreate table #task (taskID int identity(1,1) primary key, taskNamevarchar(25) unique not null, taskEndDate dateTime, taskComplete bitdefault(0), jobID int not null references #job (jobID));-- skip check constraint for taskEndDate and taskComplete for nowNow, the Business Rule says,1) if all tasks are complete then automatically set jobComplete forthe #job table to yes;2) the jobEndDate in the #job table must be >= the last/MaxtaskEndDateI tend to think trigger would slow down data update quite a bit, so,try to stay away for this purpose if possible.Always appreciate your idea.

View 2 Replies View Related

Insert Data Into Parent And Then Child Tables

Nov 16, 2006

hello -

i am trying to figure out how i can create an SSIS package to insert into multiple tables. After the first insert, I want to take the ID created (an Identity column) and then use that to insert into other associated (foreign key) tables.

For example, I have a table Users. The primary key is an Identity column. Once the SSIS insert is complete, the bulk load of new users has an identity ID value for each row. What I want to do, during the same SSIS package, is to take each row as it is inserted and add rows to other tables. Like, UserDepartment - it has a foreign key for the user id and a foreign key to the department being added. And, as part of this I will need to get the latest ID value and possibly some other values and store them in variables.

I looked at multi-cast, but I don't know/think that this will work for me.

does anyone know of a good example or article like this?

thanks
- will

View 8 Replies View Related

SSIS And Parent-Child Relationship Tables

Jun 15, 2006

Supose I have two records in a parent-child relationsuip (actually I have many more such records). Does SSIS offer any support ask for inserting one record into the parent table, the other into the child table, and updating the foreign key of the child to point to the parent?

TIA,

Barkingdog

P.S. I have to do just this as part of the datawarehouse test I'm running. Seems like a common task but I don't recall anything in SSIS addressing the issue.







View 5 Replies View Related

Copy Parent/child Rows To Same Respective Tables

Feb 29, 2008

My Question: Does anyone know of a decent way (i.e. I do not want to loop to insert each row and check the SCOPE_IDENTITY() field or anything like that) to copy parent/child rows to their same respective table besides using the method I have listed below (my manager does not really like the idea of the "PreviousID" field)? More details are listed below.



My Table Situation: I have a parent table and a child table. Both tables have an identity column as the primary key. The relationship between the tables is established using the parent table's primary key to the column in the child table that stores the relationship. The identity column in both tables is the only column that is unique in the tables.

Sample Data (made up and simplified for visualization purposes):
ParentTable - "Items"
ID ItemCategory Price
1,T-Shirt,$20
2,Blue Jeans,$50
3,T-Shirt,$40

ChildTable - "Components
ID ItemID Component
1,1,Fabric
2,1,ScreenPrinting
3,2,Fabric
4,2,Zipper
5,3,ThickFabric
6,3,ScreenPrinting
7,3,Elastic

My Need: I need to make a copy of the records (keeping the parent/child relationship intact) to the same table as the source records. For example, in my data example above, I may need to make a copy of all the "T-Shirt" items and their child records. As the parent records are copied, they will be assigned new keys since the primary key is an identity. Obviously, this new key needs to be used when creating the child records, but I need to somehow associate this new key to the new child records.

Possible Solution: I know this can be achieved by adding another column to the parent table to store the "PreviousID" (INT NULL). Using this new field, when I want to copy the "T-Shirt" items, I would insert the new records and store the ID of source records (i.e. the identity value of the row that was copioed would be stored in this "PreviousID" field). Once the parent record has been copied, I could then insert the child records, and I could join on the "PreviousID" field to get to the new ID to use for inserting the copies of the child records.

Thanks for reading this and for any help offered.

View 2 Replies View Related

Inserting Data Into Two Tables With Parent-child Relationship

Nov 13, 2006

I am trying to insert data into two tables with a SSIS package. One table has a foreign key relationship to the other table's primary key. When I try to run the package, the package will just seems to hang up in bids. I have found two ways around the issue but I don't like either approach. Is there a way to set which table gets insert first?

If I uncheck the check constraints option on the child table, the package will run very quickly but this option alters the child table and basically disables the constraint. I don't like this option because it is altering the database.

The second approach is to set the commit level on both tables to say 10,000 and make sure that the multicast component has the first output path moved to the parent table. I don't like this option because I am not sure if the records are backed out if the package should abend after records have been committed.

View 1 Replies View Related

How To Find All Child Tables That Belong To A Parent Table

Mar 5, 2008

How do i find the child tables that belong to a parent table. Thanks.

View 10 Replies View Related

T-SQL (SS2K8) :: Deleting From Parent And All Its Child Tables With FK (no Delete On Cascade)

Apr 8, 2015

I have a database with many tables. I would like to Delete all rows with practiceID=55 from all Parents tables and all corresponding rows from its child tables. Tables are linked with foreign key constraints (but there is no ON DELETE CASCADE).

How to write a generalized code for removing rows from both parent and child tables.

Query should pick parent table one by one and delete rows with practiceID=55 and all corresponding rows from its child tables

View 3 Replies View Related

Design Question - Parent Child Tables &&amp; Identity Columns

Apr 17, 2007

I don't know if this is the right forum but...



In a parent/child table structure (order/orderdetail) I have used identity columns for the orderdetail or compund primary keys. I find a single identity column on the detail table easier to manage (with a fk to the parent) but what ends up bieng easiest for the user is to have an order (say #3456) and detail items listed sequentially from 1 to n. This reflects a compound key structure but generating the 2nd field is a pain. Is there any way to tie an identity field to the parent key so that it will generate this number for me automatically?

View 3 Replies View Related

Transact SQL :: Retrieve Currently Created Date From Master Table To Load Into Parent And Child Table

May 12, 2015

In Master tabel i have these date datas

2015-05-10

2015-05-11

2015-05-12

SO when i try to load from  Master table to parent and child table i am using using expresssion like 

select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON 
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND 
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE())  and convert(date,B.Ftpdate) = convert(date,getdate()) ;

If i use this Expression i am getting the current system date data's only  from Master table to parent and child tables.

My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and  child tables.

If i run this expression to remote server  i cannot change the system date in server.

while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.

What is the Expression on which ever  date i am trying load in  the master table  same date need to loaded in Parent and child table without changing the system Date.

View 10 Replies View Related

SQL 2012 :: Group By Parent With One Child And Multiple Child Information?

Jul 25, 2014

Basically i have three Tables

Request ID Parent ID Account Name Addresss
1452 1254789 Wendy's Atlanta Georgia
1453 1254789 Wendy's Norcross Georgia
1456 1254789 Waffle House Atlanta Georgia

Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789

Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789

I want the Result

Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2

View 1 Replies View Related

Transact SQL :: Select Records From Two Child Tables

Jun 18, 2015

i have 3 tables names parent, child1, child2 parent has 1 record, child1 has 2 record and child 3 has 3 records the script

select Parent.*,child1.f1,child2.f2  from child1 inner join Parent on parent.id =child1.id 
inner join child2 on child1.id  =child2.id 

running above query gives me sixes rows but i want only all rows of childs but not their Cartesian products

Object: Table [dbo].[Parent] Script Date: 06/18/2015 17:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Parent](
[id] [int] NOT NULL,

[code]....

View 8 Replies View Related

Transact SQL :: Unable To Change Column Order Of Existing Table In Server

Jul 23, 2015

In y sql server table has millions of records available. I don't want to drop the tables.

My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.

Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !

View 13 Replies View Related

How To Change Column Order In Tables?

Apr 14, 2008

I'm using VBE 2008 and in the edit table schema box it doesn't allow reordering the columns. Any solutions?

View 1 Replies View Related

Transact SQL :: Retrieve All Records From Parent Table And Any Records From Child Table

Oct 21, 2015

I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.

SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY

[Code] ...

It's only returning two rows (the students where they have a row in the HomeworkLogLine table). 

View 3 Replies View Related

DB Design :: Change Order Of Column In Database Tables

Jul 22, 2015

How to Change Order of Column In Database Tables

View 10 Replies View Related

Bcp In Child Before Parent

May 19, 2004

Huh?

I've got good RI data...BUT..a developer loaded the tables in alpha table order....

Such that the child loaded BEFORE the parent....

Huh?

Got a test being set up now to mess with the child file to add a key that doesn't exist in the parent...

But Why is this allowed?

In DB2 you can specify

LOAD DATA REPLACE NO CHECK....

On the load card...you then need to run a check after to verify the data...

Is that what's going on? Is there such a utility in SQL Server to run a check post load?

I'm confused....

Any comments appreciated.

Thanks



Brett

8-)

View 2 Replies View Related

Insert Into Parent/child

Feb 25, 2008

hi,
i have two tables i want the identity value of the parent table to be inserted into the chile table
here is my code,but i don't know why it isn't working !
protected void Button1_Click(object sender, EventArgs e)    {        string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;        string pcontent = TextBox1.Text;        string data = TextBox2.Text;        addtopic(pcontent,connectionString);        addfile(data, connectionString);                    }    public void addtopic(string subject,string connstring)    {         using (SqlConnection connection = new SqlConnection(connstring))        {                        SqlCommand command = new SqlCommand("INSERT INTO parent" + "(content)" +              "Values(@content)", connection);            command.Parameters.Add("@content", SqlDbType.Text).Value = subject;            connection.Open();            command.ExecuteNonQuery();                    }    }    public void addchild(string name, string connstring)    {                using (SqlConnection connection = new SqlConnection(connstring))        {Guid id = Guid.NewGuid();           SqlCommand commandd = new SqlCommand("INSERT INTO child" + "(parentid,data,uniqueid)" +              "Values(@@IDENTITY,@data,@uid)", connection);            commandd.Parameters.Add("@data", SqlDbType.NVarChar, 50).Value = name;            commandd.Parameters.Add("@uid", SqlDbType.UniqueIdentifier).Value = id;           
 
thanks in advance :)
           
            connection.Open();            commandd.ExecuteNonQuery();        }
    }

View 2 Replies View Related

Parent Child Transversing

Jul 16, 2005

I have a parent/child relationship in a relational database broken out like this:
Table Name: categories[category_id]         int            (primary_key NOT NULL),[category_name]       varchar(50),[parent_fk]           int
The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this:
select category_id, category, parent_fk from categories where  category_id not in (  select parent_fk from categories)
Each bottom-level category has a count attached to it.
The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts).
My sql is a little weak, could you help me out?  I can utilize everying in SQL 2000 (stored proc, UDF, anything).
Thanks!

View 3 Replies View Related







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