Transact SQL :: Create Hierarchies Table Or Query From Multi Parent Table?

May 21, 2015

convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.

ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent

View 13 Replies


ADVERTISEMENT

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

Query Based Off Primary Key Of Parent Table - Adding Child Table

Jan 28, 2012

I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?

Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)

View 5 Replies View Related

Transact SQL :: Create A Temp Table On Results Of A Pivot Query?

Jun 17, 2015

I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.

IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,

[code]....

The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.".   The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT".  I will post the definitions from another effort below.

CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,

[code]....

SQL Server 2008 R2.

View 3 Replies View Related

Transact SQL :: Create Index On Temp Table To Reduce Run Time Of Update Query

Apr 29, 2015

I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!

The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.

Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index. 

And also I am not sure, whether creating index in more columns will create issue or not.

Attached the update query and index query.

CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC, 

[Code] ....

View 7 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

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

SQL Server 2012 :: Query To Select Parent Details From Child Table

Mar 3, 2015

I have a scenario,

We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.

Equipment table looks like :

Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177

There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.

Select query where i need to select the Code for all Parent_Id's.

View 8 Replies View Related

SQL Server 2012 :: Query To Generate Relationship (Parent Child Hierarchy From A Table)

Jul 18, 2015

I am working on a query to generate parent child hierarchy from a table.

Table has below records.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable

[Code] ...

how to achieve this.l tried with temp tables it doesn't work.

View 5 Replies View Related

Transact SQL :: Using A Parameter To Create A Table?

Jul 21, 2015

I create a script like below:

GO
SET ANSI_PADDING ON
GO
DECLARE
@ProjectID  AS NVARCHAR(128),
@TableName  AS NVARCHAR(128);
SET @ProjectID = N'EPA';

[code]....

The table created, but T-SQL created a table called @TableName.not like "EPA_SweetChargeCodeAssignees'

how to debug?

View 2 Replies View Related

Transact SQL :: Create Table Script

Jul 21, 2015

When I right click on a table and click Script Table as --> Create to -->  it scripts out:

CREATE TABLE [dbo].[Test](            
[FirstName] [varchar](50) NULL,            
[LastName] [varchar](50) NULL,            
[Address] [varchar](50()  NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Test] ADD [City] [varchar](50) NULL
 
Why is there an Alter table statement?  I’m assuming this table was altered at some point but why is SQL not just doing a create table and how is it keeping track of these changes?

View 4 Replies View Related

Getting Unique Rows From Multi-table Query

Mar 10, 2012

how to join these 3 tables but there are duplicate rows in the results. How can I get all the unique rows to only show up once.

My query is:

SELECT tbl_rate.rate_ID, tbl_rate.rate_site_IDref, tbl_rate.rate_rank_IDref, tbl_rate.rate_dollar, tbl_rate.rate_ot, tbl_rate.rate_dt, tbl_rate.rate_loa,
tbl_employee.employee_name, tbl_LEM.LEM_date, tbl_LEM.LEM_reg_hrs, tbl_LEM.LEM_ot_hrs, tbl_LEM.LEM_dt_hrs, tbl_LEM.LEM_tt_hrs, tbl_LEM.site_IDREF,
tbl_LEM.LEM_LOA, tbl_LEM.LEM_Expenseinfo, tbl_LEM.LEM_workorder, tbl_LEM.LEM_posted, tbl_LEM.LEM_Expense, tbl_LEM.LEM_tagnumberREF,
tbl_LEM.LEM_reg_rate, tbl_LEM.Lem_ot_rate, tbl_LEM.Lem_tt_rate, tbl_LEM.LEM_dt_rate, tbl_LEM.Lem_loa_rate, tbl_LEM.LEM_ID, tbl_LEM.LEM_equip_days

FROM tbl_rate INNER JOIN
tbl_employee ON tbl_rate.rate_rank_IDref = tbl_employee.employee_rankREF INNER JOIN
tbl_LEM ON tbl_employee.employee_ID = tbl_LEM.employee_IDREF

The results somehow need to be distinct by tbl_lems.lem_ID

View 4 Replies View Related

Multi Table Query Using Two LEFT JOINs

Jul 24, 2012

I'm trying to write a 3 table query using two LEFT JOINs. Originally, I only had one LEFT JOIN and prior to the addition of the the third table (parts) this query worked. Now it doesn't. I think it has to do with my GROUP BY.

SELECT
quote.quote_id, parts.material, machining_operations.machine, machining_operations.per_roughing, machining_operations.per_of_machining,
machining_operations.programming_time, machining_operations.setup_time, machining_operations.cycle_time, machining_operations.notes
quote.part_name, quote.revision_no, quote.quantity, quote.initial_volume, quote.final_volume, quote.material_price, machining_operations.mo_id
FROM quote
LEFT JOIN machining_operations
ON machining_operations.quote_num = quote.quote_id
LEFT JOIN parts
ON parts.package_no = quote.package_no AND parts.part_name = quote.part_name
GROUP BY quote.quote_id

View 10 Replies View Related

Automatically Adding Records To Child Table When Record Added To Parent Table

Aug 19, 2006

In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks

View 1 Replies View Related

Delete Child Table Rows Based On Predicates In A Parent Table

Jul 20, 2005

I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim

View 1 Replies View Related

Transact SQL :: Create Table With Timestamp Column

Jul 2, 2015

Im trying to insert the values from this query into a table, so I can later check the history of memory usage:

SELECT
[total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]

What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.

View 3 Replies View Related

Transact SQL :: Create Table Indexes In SSMS2012

May 26, 2015

I use Indexes Fundamentals of Microsoft SQL Server - Lesson 30: Indexes in the website of URL... to learn the basic things of Indexes. In my SQL Server 2012 Management Studio (SSMS2012), I executed the following code..

-- scFTX_CreateTableEmployees.sql
-- saved in C:/Documents/SQLServerIndexes_downloadCode
-- 26 May 2015 10:52 AM
USE ScottChangDB;
GO
CREATE TABLE Employees

[code]....

Where the SCHEMA and the index 'IX_Employees are located in the Object Explorer of the database "ScottChangDB" of my SSMS2012. 

View 3 Replies View Related

Transact SQL :: How To Create A Table Using Stored Procedure

Sep 1, 2015

Need creating a stored procedure which creates a table.

View 3 Replies View Related

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

Oct 8, 2015

I was writing a query using both left outer join and inner join.  And the query was ....

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
        (Production.Products AS P
         INNER JOIN Production.Categories AS C

[code]....

However ,the result that i got was correct.But when i did  the same query using the left outer join in both the cases

i.e..

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';

The result i got was same,i.e

supplier     country    productid    productname     unitprice    categorynameSupplier QOVFD     Japan     9     Product AOZBW    97.00     Meat/PoultrySupplier QOVFD    Japan   10     Product YHXGE     31.00     SeafoodSupplier QOVFD     Japan   74     Product BKAZJ    10.00     ProduceSupplier QWUSF     Japan    13     Product POXFU     6.00     SeafoodSupplier QWUSF     Japan     14     Product PWCJB     23.25     ProduceSupplier QWUSF    Japan     15    Product KSZOI     15.50    CondimentsSupplier XYZ     Japan     NULL     NULL     NULL     NULLSupplier XYZ     Japan     NULL     NULL     NULL     NULL

and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.

View 5 Replies View Related

Transact SQL :: Create Temp Table That Persists While A Front End App Is Open

Sep 30, 2015

I have an Access app. that I am migrating the DB portion (queries, tables) to SQL server. I need to create a temp table that lasts as long as the user has the Access FE app. open. Idea is that the temp table stores the user's parameters (used for filtering data entry forms and report). The parameters allow the app. to only show the user his data (cannot view other users data). The SP shown below works OK, it creates a ##Temp table and updates it with the parameters sent by Access FE app. The issue I am having is that as soon as the SP finishes the ##Temp table is removed. I thought of using a regular table, but, I am currently testing this migration in my local SQL server instance, as soon as I move the database to production environment, then users will not be able to create tables as permissions are only read/write.

USE [Work_Allocation]
GO
/****** Object: StoredProcedure [dbo].[spUser_Parameters_update] Script Date: 9/30/2015 12:27:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUser_Parameters_update]

[code]...

View 10 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

How To Join A Decendent Table On Parent Table Row?

Feb 24, 2006



I have one main Table "MainTable" which I want to relate with "subTable1, subTable2, ..." in such a way that:

"ith subTable" have to be related/joind on "ith row" of the "MainTable", "jth subTable" have to be related/joined on "jth row" of the "MainTable" and so on...

What I want Actually?

I want that when ever I delete a Record in the "MainTable", The corresponding "subTable" have to be deleted Itself.

I thought a solution that, I can cerate a Trigger on Delete of the "MainTable" and it would delete the corresponding "subTable". But I dont know how to ceate that.

Secound solution what I thought is, that may be there is some majic power in the Table Joinings. That I might join "MainTable" row with "subTable" ( ofcourse that I dont know either :))

So my question is, that what is the actual solution for my problem?

What ever solution is please give me a sample also with that. Like in a Trigger how can I write some Expression which can delete the "subTable" for the Currunt delete Row.



Regards,

Omer Kamal

View 3 Replies View Related

Transact SQL :: Create Table Named With Ending Date-time Format

Jun 25, 2015

what would be the TSQL in trying to create a new table with date-time format ending via a select into like:

select
*
into tblResults_
+
SELECT
CONVERT(VARCHAR(10),GETDATE(),112)
+
'_'
+
REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','_')
from qryResult

View 3 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

Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE

Nov 21, 2006

For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.

View 6 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

Transact SQL :: Query Plan Shows Table Not Even In Query?

Jul 22, 2015

I am trying to optimize a stored procedure in SQL 2008.  When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.

I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it?  I can't just change the query script because the table in question isn't there.

View 10 Replies View Related

How To Create A Table With Query

Nov 7, 2007

 I want to create a empty new table (if it doesn't exist) when the the page/program starts. Maybe with copying field names from other table.It would be nice to have its name personal for each user. But I don't know if it possible.Also, what event is best place for this. Perhaps like this:dim username as string = Page.User.Identity.NameDim tablename as stringtablename= shopping_basket+usernamecreate from t_product new @tablename 

View 3 Replies View Related

Query: Create Table

Jan 6, 2008

 
Hi All
 
In the following script
What "U" stands for: IF OBJECT_ID('dbo.sample_table', 'U') IS NOT NULL
Regards
Abdul 
 USE AdventureWorks
GO
IF OBJECT_ID('dbo.sample_table', 'U') IS NOT NULLDROP TABLE dbo.sample_table
GO
CREATE TABLE dbo.sample_table
(c1 int NOT NULL,
c2 char(10) NULL, c3 datetime NULL,
CONSTRAINT PK_sample_table PRIMARY KEY (c1)
)
GO

View 1 Replies View Related

Create Table Query

Sep 17, 2001

Hi Guys

Please help , I have a table that has been created like this :

CREATE TABLE [dbo].[Table1] (
[Field1] [char] (6) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[Field2] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
) ON [PRIMARY]

Could anyone tell me what this means exactly
"COLLATE SQL_Latin1_General_CP1_CS_AS"

Plus I am unable to find anything on BOTH views and triggers , can you create a trigger on a view??

Thanks

View 1 Replies View Related

Create A Table Using A Query

Mar 18, 2004

Hello,

With Oracle, we can create a table as following :

CREATE TABLE tab_name
AS SELECT
col1, col2...
from tab1, tab2
where ....


With MSSQL, can we do the same thing ?

View 3 Replies View Related

How To Create A Make-Table Query With A Union Query

Oct 30, 2006

I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?

View 2 Replies View Related

Transact SQL :: Dynamically Select Table In Query?

May 18, 2015

How  Can I select  Table Dynamically from in Side SQL Query

i.e.,
Select * from (Here I want Select the Dynamically from other Query)

View 6 Replies View Related







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