Transact SQL :: Select Lowest Level From Hierarchical Data
Jun 22, 2015
I have hierarchical data such as:
Id   Level   ParentId
1Â Â Â 0Â Â Â Â Â Â 1
2Â Â Â 1Â Â Â Â Â Â 1
3Â Â Â 2Â Â Â Â Â Â 2
4Â Â Â 0Â Â Â Â Â Â 4
5Â Â Â 1Â Â Â Â Â Â 4
6Â Â Â 0Â Â Â Â Â Â 6
7Â Â Â 1Â Â Â Â Â Â 6
8Â Â Â 2Â Â Â Â Â Â 7
9Â Â Â 3Â Â Â Â Â Â 8Â Â Â Â Â Â
10Â Â 4Â Â Â Â Â Â 9
11Â Â 0Â Â Â Â Â Â Â Â 11
As you can see even the parent element has parentId(in this case id = parentid)
How can I select the lowest level data in the hierarchy and get this result:
Id   Level   ParentId
3 Â Â 2 Â Â Â Â Â 2
5Â Â Â 1Â Â Â Â Â Â 4
10Â Â 4Â Â Â Â Â Â 9
11Â Â 0Â Â Â Â Â Â Â Â 11
View 4 Replies
ADVERTISEMENT
Apr 22, 2015
I have the data in this format
ID Â Â Â Â NAME Â Â Â Â ParentID
CV1 Â Â Â CV1NAME Â Â Â CV
CVX1 Â Â CVX1NAME Â Â CV1
CVXX1 Â CVXX1NAME Â CVX1
CV2 Â Â Â CV2NAME Â Â CV
CVX2 Â Â CVX2NAME Â Â CV2
CVXX2 Â CVXX2NAME Â CVX2
How can i flatten this data into this format
CVID Â Â CVNAME Â Â CVXID Â CVXNAME Â CVXXID Â CVXXNAME
cv1     cv1name  cvx1     cvx1name   cvxx1   cvxx1name
cv2     cv2name  cvx2     cvx2name   cvxx2   cvxx2name
View 4 Replies
View Related
Mar 11, 2015
I've got a fairly large hierarchy table and I'm trying to put together a query to find the lowest level descendants of the hierarchy. I think there must be some way to use the "Breadth-first" approach that's stated in the MSDN technet sites about SQL Server HierarchyID but i'm not sure how to write the necessary T-SQL to traverse that. I know I can get all the descendants of a parent node like this
SELECT *
FROM AdventureWorks2012.HumanResources.Employee
WHERE OrganizationNode.IsDescendantOf(@ParentNode) = 1
However, this query returns all levels for that parent's branch. If I just wanted list of employees that were at the lowest level of the branch(es) for this parent node, how would I do this?
View 1 Replies
View Related
Jul 23, 2005
Hi,This is probably a classic scenario with a shared dimension that weneed to use in different cubes, where all fact tables do not offer thesame level of detail. Dimension is snow-flaked.The cube that's causing me troubles was designed by marking the lowestdimension level Diabled and not Visible. This allows me to get rid ofone of the snow-flake tables (the one with the lowest level), thusallowing an INNER JOIN with the remaining table which has a level ofdetail corresponding to the fact table.When processing the cube, I get a 'member with key '[blah]' was foundin the fact table but was not found in the level '[blah]' of thedimension '[blah]'' that seems to indicate that none of my fact foreignkeys exist as primary keys in the dimension table. However if I thenattempt to query the cube, all data seems to be there.Would anybody be in a position (and willing ;-)) to share his/her ownexperience working around a similar issue?Thanks,SRL
View 2 Replies
View Related
Oct 19, 2015
I have a lookup table with 4 levels of codes like follows:
create table #RiskElementCategory(
[RiskElementCategoryCode] [nchar](5),
[RiskElementCategoryCodeDsc [nvarchar](50),
[RiskElementCategoryCode_2] [nchar](5),
[RiskElementCategoryLevel2Dsc] [nvarchar](50),
[Code] ...
Along with some other rows with the same format. I need to join to this table using a RiskElementCode that I get from the Source system. Â The trick is that it can be at any level, but I don't know which level it is at. Â So what I have to do is somehow get the correct row from the lookup table based on the code from the source to get the correct level.
So for Example, If i receive the RiskElementCode of 'SSR', that is in column RiskElementCategoryCode_3 so I need the row that has 'NA' for anything after RiskElementCategoryCode_3 where RiskElementCategoryCode_3 = 'SSR'. Â If i get 'DFR' I need to get the row where RiskElementCategoryCode_4 = 'DFR' since there are no levels deeper than 4 i don't need to check anything else. Â If I get 'PRR', then I need the row where RiskElementCategoryCode = 'PRR' and code_2, code_3 and code_4 = 'NA'.
So besides getting the correct row based on the code, i need to get the correct row based on the level where the next levels are 'NA'. Â I should only get 1 row each time.
View 2 Replies
View Related
Dec 20, 2007
I'm using sql express 2005. I need help with buliding a view.
I'm trying to join two tables and then, if duplicate ID's exist, retrieve the row with the lowest value in a specific column.
For instance:
TableHousehold:
ID Name Status
001 The Smith€™s A
002 The Jone€™s A
003 The Adam€™s A
004 The Grant€™s A
TableResidents
Pk HouseholdID ResidentID Name Gender
1 001 1 Mary Smith F
2 001 2 Jason Smith M
3 001 3 Rachel Smith F
4 002 1 Mike Jones M
5 002 2 Sara Jones F
6 003 2 Erik Adams M
The resident ID is auto increment and assigned by the server. It is possible for resident 1 for each household to have been deleted. It is also possible that a household does not contain any residents. Therefore, I wish to join these tables and return this:
ID HouseHoldName Status ResidentName Gender
001 The Smith€™s A Mary Smith F
002 The Jone€™s A Mike Jones M
003 The Adam€™s A Erik Adams M
004 The Grant€™s A null null
There are other columns in both tables that I will want to join, but for simplicity, the above example illustrates my need.
I was able to accomplish this by creating two views. But I€™d like to try and do this in one.
View 3 Replies
View Related
Jan 21, 2004
I have a table and am usuing ASP to query the database, the connection is to a MS Access table at the moment but am working to convert to SQL Server.
Question:
I need to select the last 20 records, by a date field, then from those 20 records select the 10 lowest scores.
Example is a member logs on an that member has say 80 total records in the table. Then I need to select the last 20 records entered by the date field then select the lowest 10 scores out of those 20.
I am new to more compex SQL Statements any help would be mostly appreciated!
table = HC_ID
date field = date
member_id = member
score = ScoreHC
View 14 Replies
View Related
Jun 7, 2007
CREATE TABLE RS_A(ColA char(1), ColB varchar(10))INSERT INTO RS_AVALUES ('S', 'shakespeare')INSERT INTO RS_AVALUES ('B', 'shakespeare')INSERT INTO RS_AVALUES ('P', 'shakespeare')INSERT INTO RS_AVALUES ('S', 'milton')INSERT INTO RS_AVALUES ('P', 'milton')INSERT INTO RS_AVALUES ('B', 'shelley')INSERT INTO RS_AVALUES ('B', 'kafka')INSERT INTO RS_AVALUES ('S', 'kafka')INSERT INTO RS_AVALUES ('P', 'tennyson')SELECT * FROM RS_ANow i need a select which selects based on hierarchyif ColA = 'S', then select only that rowelse if ColA = 'B' then select only that rowelse if colA = 'P' then select only that rowSo my results should look likeS shakespeareS miltonB shelleyS kafkaP tennysonIs there a way to do this within a select statementI tried using a CASE in WHERE CLAUSE but it put out all rows whichexisted/If any of you can help me with this right away, its is greatlyappreciatedThanks in advance
View 2 Replies
View Related
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
Jul 9, 2015
I have just been running a query which I was planning on improving by removing a redundant GROUP BY (there are about 20 columns, and one of the columns returned is atomic, so will mean that the "group by" will never manage to group any of the data) but when I modified the query to remove the grouping, this actually seems to slow the query, and I can't see why this would be the case.
Both queries return the same number of rows (69000), as I expected, and looking at the query plan, then they look nearly identical, other than at the start, there is a "stream aggregate" and "sort" being performed. The estimated data size is 64MB for the non-grouped query (runs in 6 min 41 secs), vs 53MB for the aggregated query (runs in 5 min 31 secs), and the estimated row size is smaller when aggregated.
Can rationalise this? In my mind, the data that is being pulled is identical, plus there is extra computation for doing an unnecessary aggregation, so the aggregated query should be unquestionably slower, but the database engine has other ideas; it seems to be able to work more quickly when it needs to do unnecessary work :) Perhaps something to do with an inefficient query plan for the non-aggregated query? I would have thought looking at the actual execution plan might have made this apparent, but both plans look very similar.
Edit: More information, the "group by" query had two aggregations on it, a count of one of the columns, and an average of another one. I changed this so that it was just "1" instead of the count, and for the average, I changed it to be the expression within the average aggregate, since the aggregation effectively does not do anything.
View 2 Replies
View Related
Sep 28, 2007
Hi,
I would like to know best way to design the database for the following requirement. I have a collection of tree nodes. each node has a type and set of attributes and a parent node (except for the node which has no parent). node type refers to the level of the node in the tree. child node inherits the attributes from the parent node (similar to object oriented programming where derived class inherits properties of the base class). user can add/update/delete nodes from the tree and user can choose to override the attributes of the parent node in child node.
what is best way to store this type of data? should there be a separate table for each node type (level in the tree). but the problem with this approach is that we need to duplicate the columns of the parent node, because user can overwrite the parent node attributes in the child node. there can be more than one at the same level and all of them share same set of attributes. this concept is exactly like inheritance in object oriented programming. as far as the data is concerned, there are around 15 levels, around 30K nodes and 30 attributes spread across different node levels.
thanks,
RK
View 1 Replies
View Related
May 1, 2006
How can I create a function that returns hierarchical data from a table with this structure:
- CategoryID
- CategoryName
- CategoryFather
I want to bring the result set like this...
CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2
How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.
Thanks.
View 5 Replies
View Related
Apr 17, 2008
Hello
I have a problem that I am hoping somebody can help me with!
I have built a hierarchy using the adjacency list model so I have records with an id that maps to the parent record so my hierarchy looks something like this:-
Newspapers
National Newspapers
Daily Express
Express Publications
Express Supplements
Daily Mail
Mail Publications
Mail Supplements
Mirror
So my table would look like below:-
1 Newspapers Null
2 National Newspapers 1
3 Daily Express 2
4 Express Publications 3
5 Express Supplements 3
and so on. What I would like to be able to do is flatten out the hierarchy so I get something like below where each level is in a column.
NewsPapers National Newspapers Daily Express Express Publications
NewsPapers National Newspapers Daily Express Express Supplements
Ive used CTE's for displaying the hierarchy and producing aggregate figures when joing the hierarchy to spend information but am struggling to come up with any code that would produce a flattened hierarchy.
Any help would be greatly received!
Thanks
Rich
View 5 Replies
View Related
Feb 17, 2006
hi folks,
I have to import hierarchical text files like:
32;country;city;postalcode;street
21;name;firstname;salutation;title;age;nickname
21;name;firstname;salutation;title;age;nickname
...
additionally I have to eleminate doubles. what is the best way for this problem ?
I have set up a flatfilesource with two columns and a conditional split on the first column
so now I have an output with [country;city;postalcode;street] and one with [name;firstname;salutation;title;age;nickname]. How do I split this in columns, put it in a dataset keeping the relations and remove doubles ?
Iam looking forward for any helping idea.
rgrds,
matze
View 11 Replies
View Related
Sep 22, 2015
my business user want all record where glcode must start with 2 and 4
<sample data
Tran_NoÂ
GLCode
abcd123
2123
abcd123
21235
abcd123
4289
[code]....
View 6 Replies
View Related
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
Nov 30, 2013
I have two table. Department is hierarchical table.
Department
--- id (int primary key)
--- name (varchar)
--- parent (int)
Users
--- Id
--- name
--- department_id
This query return all data from departments. But i cannot understand how get data from users
SELECT t1.name AS lvl1, t2.name as lvl2, t3.name as lvl3
FROM Department AS t1
LEFT JOIN Department AS t2 ON t2.parent = t1.id
LEFT JOIN Department AS t3 ON t3.parent = t2.id
View 1 Replies
View Related
Oct 12, 2007
Hi,
in my application, the data is in hierarchical format. there is a tree with set of nodes having parent child relationships. this data can be stored either through adjacency or nested set model approach. this is fine. but the issue here is that each child node inherits the properties of its parent node, parent's parent node and so on until the root node. lets say root node has two attributes A1 and A2 and they are stored in two columns in a table. but its child nodes inherits this data from its parent and it has its own extra attributes. so should I copy parent's data for the child node as two additional columns? the problem is that there are around 15 levels in the tree and the attribute list grows from top to bottom in the tree. lets say I need to find all the attributes for a leaf node in the tree (both direct and inherited), if I am not storing the inherited attributes for each node, then I need to walk-up the tree and find all the inherited attributes. there are around 30K nodes and each node has around ten attributes. xml is not option because of large volumes of data and auditing and reporting on individual nodes. what is the best way to store this type of data? my current approach is to have an attribute table having nodeid as a foreign key and only store the direct and NOT the inherited attributes of the node in the table, but this means to find all the attributes for the node, I need to gather the attributes of all the parents until the root node. I can't see any easy way out for this.
View 4 Replies
View Related
Mar 28, 2012
I'm using sql 2008 and triying to build a dynamic sql script to split the records 50/50.I know using newid() with order by clause selects randomly but how should I build the select statement to split the data 50/50 so i don't need to run the script manually everytime ?
View 10 Replies
View Related
Sep 3, 2006
I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure. Example data:ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level------------------------------------------------------------------------------------------------------------------------------------------------1 Custom Furniture 0 02 Boxes 0 03 Toys 0 04 Bedroom 1 Custom Furniture 15 Dining 1 Custom Furniture 16 Accessories 1 Custom Furniture 17 Picture Frames 6 Accessories 28 Serving Trays 6 Accessories 29 Entertainment 1 Custom Furniture 110 Planes 3 Toys 111 Trains 3 Toys 112 Boats 3 Toys 113 Automobiles 3 Toys 114 Jewelry 2 Boxes 115 Keepsake 2 Boxes 116 Specialty 2 Boxes 1Desired output:Custom Furniture Accessories Picture Frames Serving Trays Bedroom Dining EntertainmentBoxes Jewelry Keepsake SpecialtyToys Automobiles Boats Planes Trains
View 4 Replies
View Related
Feb 18, 2008
Hi All,
I am attempting to create a Visual C++ application based on displaying financial charts and am using SQL Express to store Stock information such as the Exchanges the stocks are traded on, the indicessectors they belong to and the Closing prices for as long as I can download data for. I am not proficient in C++ nor SQL and am using this project to learn both languages as well as making myself rich beyond my wildest dreams.
I have "designed" a database with the following tables:
tblDate_ 1 column clmDate (Primary Key, smalldatetime, NOT NULL)
tblStockExchange_ 4 column clmStockExchangeID (PK, int, NOT NULL)
clmParentID (int, null)
clmStockExchange (nvarchar(50), NOT NULL)
clmMarkets_ (FK, nchar(20), NOT NULL)
tblMarkets_ 1 column clmMarkets (PK, nchar(20), NOT NULL)
tblIndices_ 1 column clmIndices (PK, nchar(50), NOT NULL)
tblSectors_ 1 column clmSectors (PK, nchar(50), NOT NULL)
tblMarkets_Sectors 3 columns clmMarkets_SectorsID(PK, int, NOT NULL)
clmMarkets_ (FK, nchar(20), NOT NULL)
clmSectors_ (FK, nchar(50), NOT NULL)
tblSecurities_ 4 columns clmEPIC (PK, nchar(10), NOT NULL)
clmSecurity_Type (nchar(5), NOT NULL)
clmSecurty_Name (nchar(50), NOT NULL)
clmSectors_ (FK, nchar(50), NOT NULL)
tblSecurities_Indices 3 columns clmSecurities_IndicesID (PK, int, NOT NULL)
clmEPIC_ (FK, nchar(10), NOT NULL)
clmIndices_ (FK, nchar(50), NOT NULL)
tblSecurities_Date_OHLCV 8 columns clmOHLCVID (PK, int, NOT NULL)
clmEPIC_ (FK, nchar(10), NOT NULL)
clmDate_ (FK, smalldatetime, NOT NULL)
clmOpen (float, NOT NULL)
clmHigh (float, NOT NULL)
clmLow (float, NOT NULL)
clmClose (float, NOT NULL)
clmVolume (float, NOT NULL)
Why so many tables? perhaps you should put some more in...
This was the only way I could work out how to store one-to-one and one-to-many relationships required for:
- Many closing prices for many stocks
- Stocks belonging to many indices
- Stocks belonging to only one sector
- Stocks belonging to only one market (MainMarket or AIM for LSE)
- Stocks belonging to only one Exchange (I am aware of dual listed stocks but one thing at a time)
Why nchar's and not nvarchar's?
Because I didn't realise the benefits of nvarchar's until recently. How can I change this a loose the extra spaces in the cells.
Why do some tables have IDs and others don't?
I decided to put ID columns in for tables that didn't have obvious Primary Keys - if someone could explain the advantages if ID columns I would be grateful.
To the SQL Professional's eye there will be some obvious things wrong with this design and your criticism is welcome. The database I have is achieving what I would like it to do; I can plot charts using the data but I have ran into problems when trying to create a TreeView control which is what I would like to use as a navigational tool in my application.
It would seem that pulling hierarchal data from a relational database, to pass to the TreeView control, is a tricky task to say the least. I have found many articles online which discuss how to do this (using an Adjacency List Model or Nested Set Model) but they define a fairly simple example at the beginning (based on fruit or electrical goods) but don't appear to talk about gathering data from an existing relational database or changing an existing relational database so that it is more suited to storing hierarchal information. I have Joe Celko's - Tree and Hierachies in SQL for Smarties but sadly this fine material is a little beyond me!
I would like the hierarchy to look like this:
StockExchange
Market
Sector
Stock
Indices
Sector
Stock
I have written three queries to get the StockExchangeMarketSectorStock information individually from each table but am struggling with ways to put all the rows together, add left and right values (Nested Set Model) then run queries against this to get individual nodes to pass to the TreeView control. Therefore is there something I need to add to the original design?
Any help would be greatly appreciated.
View 4 Replies
View Related
Sep 15, 2015
I want to select custom format from table?
View 7 Replies
View Related
Aug 1, 2015
i have table that has rows as below
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
[code]...
i want to select the data priority wise. the o/p should look likeÂ
e.g first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'Â
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
View 6 Replies
View Related
May 9, 2008
Hi all!
I am trying to organize a hierarchical data structure into a table. I need to have the possibility to set 2 parents for some nodes. Curently I see following two options:
Example 1
id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1,2 Level 2 Child
Example 2
id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1 Level 2 Child3 2 Level 2 Child
Is any of the two examples valid database logic wise? In fact, is it possible to achieve the requirement by using only one table?
Thanks in advance,
View 4 Replies
View Related
Mar 10, 2015
selecting table data in hierarchical XML .
Here is the sample table DDL and data
Declare @continents Table
(
id int identity (1,1)
,continent_id int
,continent_Name varchar(100)
,continent_surface_area varchar(100)
,country_id int
[code]....
View 8 Replies
View Related
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
Aug 12, 2015
I want to enforce a constraint on the table where , the same MenuId cannot have 2 different prices for the same date range (accidentally).I think CHECK constraint won't work.
View 4 Replies
View Related
Sep 29, 2015
I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
How can I do the following: AS A Test_user
SELECT * FROM DB2.dbo.Test_view --Should work
SELECT * FROM DB1.dbo.Source --Should Not work
View 3 Replies
View Related
Apr 22, 2015
We have one table.We have updated the status for one column to 1 and we did not committed the transaction. Can we do an another update on another row.
In below example i am updating GEO_D and transaction is not committed. Now my requirement is we have to update other records (not Geo_D). If  try to update GEo_D it should wait.Â
IF OBJECT_ID('TEMPDB..##TEMP_STSTUS') IS NOT NULL
DROP TABLE ##TEMP_STSTUS
CREATE TABLE ##TEMP_STSTUS
(
 ID INT IDENTITY(1,1)
 ,NAME VARCHAR(10)
 ,STSTUS VARCHAR(10)
)
INSERT INTO ##TEMP_STSTUS SELECT 'GEO_D','0'
[Code] ....
View 6 Replies
View Related
Aug 5, 2015
I have a query which works absolutely fine when connecting to an actual server:
WITH CLAIMDATA AS(
SELECT DISTINCT
DB_NAME() AS DBName,
'UA' AS Client,
POLICY AS KMPONO,
[code]...
If i change the connector to REPLPROD (which is a linked server): From REPLPROD.XUNMDTAUAI.dbo.UPPOREP UP INNER JOIN REPLPROD.XUNMDTAUAI.dbo.UKKMREP UK
I get the error:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
View 7 Replies
View Related
Oct 22, 2015
I have a requirements to collect Transactions per second from a sql server instances level. Any script to collect TPS in the instance level ?
View 5 Replies
View Related
Nov 22, 2015
I have a table of Affiliates, each Affiliate can have a ParentId, which refers to another Affiliate. How can I retrieve all children of a given Affiliate, along with their depth level? I have this query which selects the total number of descendants of a given Affiliate, but I need to select all the children + their depth level, up to a certain level.
WITH Descendants AS(
  SELECT RootId = Id, Id
  FROM Affiliates
UNIONÂ ALL
  SELECT RootId, Af.Id
  FROM Descendants
[Code] .........
What I want to achieve is a statement that has 3 parameters: RefererId, StartLevel, and EndLevel.
I need a statement that selects all the specified Affiliate.ReferrerId's descendants including their level distance from the referrer, filtered by the StartLevel and EndLevel (WHERE CurrentLevel >= StartLevel AND CurrentLevel <= EndLevel ).
View 2 Replies
View Related
Sep 29, 2015
I cannot find the problem with this function.
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
View 20 Replies
View Related
