How to create a temporary table or CTE with values from two sources
Need to build a temporary table or a CTE for reporting purposes. Users will be able to select a location and courses from drop-downs.
The tables involved are the Person table that holds all employees and a Common Table Expression that will have the courses selected from drop down.
I need to be able to create a temporary table or a CTE with employee id field from the person table and a course name field from Course CTE.
For example, if courses A, B, C are selected each employee will have three records, one for each Course selected. So employee 1 will have three records in this temporary table or CTE. I'm using SQL Server 2008.
You can use
UNIONqueries inside CTE to get values from Multiple Tables. So If you want to get the EMployeeId and CourseName for Each employee, YOu can Join the tables inside the CTE like this
;WITH CTE AS ( SELECT E.EmployeeId, C.CourseNm FROM dbo.Employee E INNER JOIN dbo.Course C ON E.CourseId = C.CourseId ) SELECT * FROM CTE
if you want to use Temp tables, you can try this
SELECT E.EmployeeId, C.CourseNm INTO #Temp FROM dbo.Employee E INNER JOIN dbo.Course C ON E.CourseId = C.CourseId SELECT * FROM #Temp
See also questions close to this topic
nvarchar or varchar or date in joins of SQL Server
I have a management project developed in Web using SQL Server 2008 R2 and Windows Application using SQL Server CE 4.0.
Both uses almost same database structure. I have a fees table as :
TABLE [fees] ( [feeid] bigint NOT NULL IDENTITY(1,1), [acno] int NULL DEFAULT 0, [billingmonth] nvarchar(10) NULL, [oldbal] numeric(18,2) NULL DEFAULT 0, [duedtamt] numeric(18,2) NULL DEFAULT 0, [afterdtamt] numeric(18,2) NULL DEFAULT 0, [bal] numeric(18,2) NULL DEFAULT 0, [depamt] numeric(18,2) NULL DEFAULT 0, [totdpo] numeric(18,2) NULL DEFAULT 0, [depdt] datetime NULL )
billingmonthwill always use format
BillingMonthuses various joins (inner and left outer join) in other tables.
How to increase performance of joins with
BillingMonth? , should i :
- Convert nvarchar to varchar ( as it will always store Month in SQL2008R2 )
- Convert nvarchar to datetime ( as first day of month 01-MMM-yyyy in SQL Server CE and SQL Server 2008 R2)
How do you update a DB table with Python where the update statement uses a Python method?
I'm having a hard time finding an online example of this. I want to update a database table by passing the columns of the table into the function. So my pseudo code looks like this:
import pypyodbc from fuzzywuzzy import fuzz from fuzzywuzzy import process UPDATE table SET result = fuzz.ratio(DBColumn1,DBColumn2)
Import data from Oracle to SQL server using SSIS
We want import data from Oracle to SQL server using SSIS I was able to transfer data from Oracle to one table (Staging)in SQL. then I need to transform data and I found that I need to run stored procedure to transform the data from Staging to Actual production data. But I wonder How we can do it.
What is the quickest way to update each row in one table with a use-once value from another table?
I have a table called
dbo.RecycleProductIDthat has just a primary key
ProductIDcolumn and a
ProductID (pk, int) | Used (bit) 23 1 65 1 68 1 90 NULL 104 NULL ... 60983471 NULL
The table above has about 6.5m
ProductIDvalues that need to be 'recycled' and assigned to products in a different table. Once a
ProductIDhas been assigned to a product, I must update the
Usedcolumn value to
1to indicate it has been used. There are gaps in the
ProductIDsequence, some in the thousands (.e.g it can jump from 1010 to 8055) and there are way more rows in this table than the receiving table
dbo.Producttable (has about 1.5m rows) is just a list of products but with no
ProductID (pk, int) | ProductName (varchar) 23 Toothpaste 65 Speakers 68 Galaxy S8 NULL Plate NULL Monitor NULL Carpet .....
I am currently running a while loop to get the
DECLARE @Max int = (select max(ProductID) from [dbo].[RecycleProductID]); DECLARE @Min int = (select min(ProductID) from [dbo].[RecycleProductID]); while @Min <= @Max begin UPDATE TOP (1) [dbo].[Product] SET ProductID = (SELECT TOP 1 ProductID FROM [dbo].[RecycleProductID] b1 WHERE b1.Used IS NULL ORDER BY ProductID ASC) OUTPUT INSERTED.ProductID INTO dbo.UsedProductID WHERE ProductID is null; UPDATE rp1 SET rp1.Used = 1 FROM [dbo].[RecycleProductID] rp1 INNER JOIN dbo.UsedProductID ub1 ON ub1.ProductID = rp1.ProductID set @Min = @Min+1 end;
With this basically being a CURSOR, it is taking forever. It has been running for almost two days and only updated about 326515 rows. Is there a quicker way of doing this?
SQL Execute AS userA vs Login as userA
I have a tableA that disallow userA to update it. (userA is a DB user)
DENY UPDATE ON tableA TO userA;
When I login with Window Authentication, I update tableA with below query.
EXECUTE AS USER = 'userA'; GO update tableA set colA='0001' where id=1; GO REVERT;
The UPDATE permission was denied on the object 'tableA', database 'DW', schema 'dbo'.
====> This is my expected result.
However, when I changed to login as 'userA' with SQL Server Authentication.
update tableA set colA='0001' where id=1
====> It allowed to update! It is supposed to block from updating, right? May I know why it does not block it? Anything I have missed out?
T-SQL Select - Over clause equivalent LINQ
I need to write an extension method for LINQ on C# which implements following scheme
...ROW_NUMBER() OVER(PARTITION BY columnName1 ORDER BY columnName2 DESC) ....
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD, ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS RowNumber FROM Sales.vSalesPerson WHERE RowNumber = 1
How to join multiple CTE's into a temp table
I have been given a script to clean up which uses approx 85 temp tables, I have been advised to use Common Table Expressions.
I have 3 CTE's, the first is the result of 7 tables pulled together using Union all. Followed by 2 more CTE's. The script runs up to:
select * from CTE_1 Union all select * from CTE_2 Union all select * from CTE_3
I then want to put all these results into a reusable table so I can then add some joins with various case statement logic. How can I put these into a temp table so that I can reference it later.
I'm looking to reduce the amount of temp tables so rather than put each CTE into a temp table I would ideally put multiple CTE's into one temp table. I currently have:
; with [CTE One] as ( select 1 as col ), [CTE Two] as ( select 2 as col ), [CTE Three] as ( select 3 as col ) select * from CTE_1 Union all select * from CTE_2 Union all select * from CTE_3
Error when inserting CTE table values into physical table
I have a complex query that creates a master CTE_Table form other CTE_Tables. I want to insert the results of the master CTE_Table into a physical table. I'm using Teradata version 15.10.04.03
SELECT Failed.  Syntax error, expected something like a 'SELECT' keyword or '(' or a 'TRANSACTIONTIME' keyword or a 'VALIDTIME' keyword between ')' and the 'INSERT' keyword.
DROP TABLE dbname.physicalTablename ;
CREATE MULTISET TABLE dbname.physicalTablename , NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( col1 INTEGER, col2 INTEGER, col3 INTEGER ) NO PRIMARY INDEX ;
cteTable3 AS ( SELECT A.colA, A.colB, A.colC, B.col1, B.col2, B.col3 FROM cteTable1 A INNER JOIN cteTable2 ON (blah blah blah) ),
cteTable2 AS ( SELECT col1, col2, col3 FROM SourceTableB ),
cteTable1 AS ( SELECT colA, colB, colC FROM SourceTableA )
INSERT INTO dbname.physicalTablename ( col1, col2, col3, col4, col5, col6 )
SELECT (C3.colA, C3.colB, C3.colC, C3.col1, C3.col2, C3.col3) FROM cteTable3 C3 ;
Using a Common Table Expression (CTE) in a Parent-Child Tree relationship
I'm making a little webapp with a Parent/Child relationship. The problem is that the relation is recursive, is not just one level.
Bob is father of Peter Peter is father of Steve Steve is father of Charles
Actually I'm doing a
RIGHT JOINbut obviously doing that I can only access to inmmediate child.
This is that I want to achieve:
If the input is
SteveI expect to get
If the input is
PeterI expect to get
[Steve, Charles]as output.
If the input is
BobI expect to get
[Peter, Steve, Charles]as output.
I read that CTE are perfect for this situation, because doing something like this is weird and a bit crazy (in my opinion):
SELECT children_user_id, ud.name, parent_user_id FROM ((SELECT * FROM users_parent ORDER BY parent_user_id, children_user_id) parents_sorted, (SELECT @pv := '1') initialisation) LEFT JOIN users_data ud ON parents_sorted.children_user_id = ud.user_id WHERE FIND_IN_SET(parent_user_id, @pv) > 0 AND @pv := CONCAT(@pv, ',', children_user_id);
My table looks like something like:
I'm completely new to CTE, how can I get all decendents/progeny of a give user, using CTE?
Thanks for reading and please forgive my bad English.
Using temp tables in separate queries
I have a scenario where I have to insert into a table for thirteen different use cases. I am primarily getting the data from a "MainTable" and I have to only get records with "IsLocal" = 1 from this "MainTable".
I am just contemplating whether I should use the table directly with the "IsLocal" condition in all my thirteen different use cases or just use a temporary table and populate it with records from "MainTable" for the condition of "IsLocal" = 1. Which would be the better option to me?
This "MainTable" is expected to have around 1 million records with significant portion of them having "IsLocal=1".
Temp tables vs subqueries in inner join
Both SQL, return the same results. The first my joins are on the subqueries the second the final queryis a join with a temporary that previously I create/populate them
SELECT COUNT(*) totalCollegiates, SUM(getFee(c.collegiate_id, dateS)) totalMoney FROM collegiates c LEFT JOIN ( SELECT collegiate_id FROM collegiateRemittances r INNER JOIN remittances r1 USING(remittance_id) WHERE r1.type_id = 1 AND r1.name = remesa ) hasRemittance ON hasRemittance.collegiate_id = c.collegiate_id WHERE hasRemittance.collegiate_id IS NULL AND c.typePayment = 1 AND c.active = 1 AND c.exentFee = 0 AND c.approvedBoard = 1 AND IF(notCollegiate, c.collegiate_id NOT IN (notCollegiate), '1=1'); DROP TEMPORARY TABLE IF EXISTS hasRemittance; CREATE TEMPORARY TABLE hasRemittance SELECT collegiate_id FROM collegiateRemittances r INNER JOIN remittances r1 USING(remittance_id) WHERE r1.type_id = 1 AND r1.name = remesa; SELECT COUNT(*) totalCollegiates, SUM(getFee(c.collegiate_id, dateS)) totalMoney FROM collegiates c LEFT JOIN hasRemittance ON hasRemittance.collegiate_id = c.collegiate_id WHERE hasRemittance.collegiate_id IS NULL AND c.typePayment = 1 AND c.active = 1 AND c.exentFee = 0 AND c.approvedBoard = 1 AND IF(notCollegiate, c.collegiate_id NOT IN (notCollegiate), '1=1');
Which will have better performance for a few thousand records?
Best practice for using SQL Server Temp Tables
I have a dashboard web application that involves users logging in to query large amounts of data (MS SQL Server 2016) and view charts of the results. The data also changes frequently (say every hour). Performance is particularly important.
To improve performance I've found that I can create a temp table once, which handles a bulk amount of the processing, then re-query that table multiple times (eg. grouping by different fields) to produce different charts. When the user first logs in I can use this method to quickly pre-caclulate a lot of the chart data for a user in one go. This is much more efficient than doing the whole query each time (ie. doing "temp table + group by" separately for each of the 20 charts).
However, once the user has logged in, there are other actions he can take where he could re-use that temp table data. Ideally i don't want to recreate the temp table on each subsequent request, so i'd like to re-use it for the lifetime of the client login.
I don't think I could use a global table, as I'd need a separate one for each user. Also SQL Server wouldn't know when to delete the table if the user's session timed out on the client, potentially leading to a build-up of lots of old temp tables & massive amount of data.
Ideally I'd like a session temp table that would expire say every hour, so the client could access the data for multiple subsequent requests and it wouldn't have to be recreated that often.
What are my options for doing this?