November 16, 2014 at 9:48 pm
I have four tables.
Table1 has four columns, Table2 has 3 columns, Table3 has eight columns and Table4 has 10 columns.
Table3 and Table4 have a same column called Year. The datatype for the Year column is nvarchar(255). The data in the Year column in Table4 is coded as 2001-02, 2002-03 and so forth.
All four tables have one common column called as schoolCode dataype is nvarchar(255)
I want to extract all columns in all four tables based on the year and then save it into a new table.
Please suggest how can I do this and if possible explain it too.
November 16, 2014 at 10:46 pm
you can use join for that...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 17, 2014 at 5:29 am
As stated here you need to first devise your query in form of a JOIN. Something like below. Note that this is just an example to give you a start. You might need to use LEFT\RIGHT JOINS for example if you have schoolcodes that do not exist in all tables
SELECT T1.*, T2.*, T3.*, T4.*
FROM Table 1 T1
INNER JOIN Table2 T2 on T1.schoolcode = T2.schoolcode
INNER JOIN Table2 T3 on T1.schoolcode = T3.schoolcode
INNER JOIN Table2 T4 on T1.schoolcode = T4.schoolcode
WHERE T3.Year = 'XXXX-02'
Once you have your code retrieving the correct data then you can simply generate your table using an SELECT - INTO command.
SELECT T1.*, T2.*, T3.*, T4.*
INTO New_Table
FROM Table 1 T1
INNER JOIN Table2 T2 on T1.schoolcode = T2.schoolcode
INNER JOIN Table2 T3 on T1.schoolcode = T3.schoolcode
INNER JOIN Table2 T4 on T1.schoolcode = T4.schoolcode
WHERE T3.Year = 'XXXX-02'
Or you can define your new table first and then do a simple insert
i.e
INSERT INTO New_Table
(col1,
col2,
....
)
SELECT T1.col1, T1.col2,..., T2.colx,...,, T3.colx,...,, T4.colx,...,
FROM Table 1 T1
INNER JOIN Table2 T2 on T1.schoolcode = T2.schoolcode
INNER JOIN Table2 T3 on T1.schoolcode = T3.schoolcode
INNER JOIN Table2 T4 on T1.schoolcode = T4.schoolcode
WHERE T3.Year = 'XXXX-02'
hope this helps
November 17, 2014 at 7:26 am
Ashish Dutt (11/16/2014)
I have four tables.Table1 has four columns, Table2 has 3 columns, Table3 has eight columns and Table4 has 10 columns.
Table3 and Table4 have a same column called Year. The datatype for the Year column is nvarchar(255). The data in the Year column in Table4 is coded as 2001-02, 2002-03 and so forth.
All four tables have one common column called as schoolCode dataype is nvarchar(255)
I want to extract all columns in all four tables based on the year and then save it into a new table.
Please suggest how can I do this and if possible explain it too.
Two big red flags pop up when I read your post. First is you have a column named Year with a datatype of nvarchar(255). You should avoid using reserved words for object and column names. Second, which is possibly more of an issue, why are you using nvarchar(255) to hold year? However, you then state that the column Year is not Year data but year and month. You certainly don't need to support unicode for that. You should look at using the date datatype for this. It seems you have a habit if just tossing nvarchar(255) as your default datatype. This is going to cause you nothing but pain in the long run.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 18, 2014 at 7:49 pm
Thank you all for your prompt responses. I'm sorry I was not able to revert back sooner, was tied up in trying to implement the suggestion as posted by SQL Baby. Special thanks to SQL Baby for being so descriptive about the post.
Continuing further, The problem is not yet resolved. It so happens that these four tables have massive amounts of data in them and if I write a query that uses "Select statement or Insert into statement", it takes more than 2 hours for the query to run. The number of rows in each table is more than a million.
Following is my query
insert into BiYearlyData(acYear,School_Code,District_name,School_name,
Building_Status,Tot_Clrooms,Classrooms_in_Good_Condition,
Rural_Urban,Medium_of_Instruction,Distance_BRC,Distance_CRC,Yeur_Estd,
Pre_Pry_YN,Residential_Sch_YN,Sch_Management,Lowest_Class,
Class1_Total_Enr_Boys,Class2_Total_Enr_Boys,Class3_Total_Enr_Boys,
Class4_Total_Enr_Boys,Class5_Total_Enr_Boys,Class6_Total_Enr_Boys,
Class7_Total_Enr_Boys,Class8_Total_Enr_Boys,Class1_Total_Enr_Girls,
Class2_Total_Enr_Girls
)
select E.acyear,B.School_code,B.District_name,B.School_name,
F.Building_Status,F.Tot_Clrooms,F.Classrooms_in_Good_Condition,
G.Rural_Urban,G.Medium_of_Instruction,G.Distance_BRC,G.Distance_CRC,G.Yeur_Estd,
G.Pre_Pry_YN,G.Residential_Sch_YN,G.Sch_Management,G.Lowest_Class
e.Class1_Total_Enr_Boys,e.Class2_Total_Enr_Boys,e.Class3_Total_Enr_Boys,
e.Class4_Total_Enr_Boys,e.Class5_Total_Enr_Boys,e.Class6_Total_Enr_Boys,
e.Class7_Total_Enr_Boys,e.Class8_Total_Enr_Boys,e.Class1_Total_Enr_Girls,
e.Class2_Total_Enr_Girls
from BasicData B
inner join ERData E on B.School_Code=E.School_Code
inner join FacilityData F on B.School_Code=F.School_Code
inner join GeneralData G on B.School_Code=G.School_Code
where E.acyear='2006-07'
Yesterday, when I tried executing the aforementioned query, It took more than 2 hours and finally I had to stop it because the "tempdb" was full causing a OS crash!
Is there any other way of accomplishing this task in lesser amount of query execution time?
Please suggest. Eagerly awaiting your conducive response.
November 19, 2014 at 2:10 am
Ashish Dutt (11/18/2014)
...Is there any other way of accomplishing this task in lesser amount of query execution time?Please suggest. Eagerly awaiting your conducive response.
Yes there is. You mentioned that two tables contain year/month, but your query only filters one of them. Add the second filter to your WHERE clause.
Test your query bit by bit before running it. Start with this:
SELECT TOP 1000
b.*, e.*
FROM BasicData b
INNER JOIN ERData e ON e.School_Code = b.School_Code
--INNER JOIN FacilityData f ON f.School_Code = b.School_Code
--INNER JOIN GeneralData g ON g.School_Code = b.School_Code
WHERE e.acyear = '2006-07'
ORDER BY e.acyear, b.School_code
If the output looks correct or can be tested to be correct, then introduce the next table and test again, and so on.
Use the estimated execution plan to get a rough idea of the number of rows which would be returned by the query with the TOP clause commented out. When you have corrected your query, you would benefit from recasting the output columns to the correct datatype. For instance, [year] will always be 7 numbers and a hyphen, so it's CHAR(7). No need for unicode. Disk space might very well be cheap, but filling up your tempdb with data taking up twice as much space as it should is not.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 19, 2014 at 2:51 am
Hey,
Another suggestion you do as much filtering as you can of your data before you do you JOIN. So filter each table into Temporary variables and do the JOIN on the Temporary variables. Only retrieve the columns that you need to satisfy the join. School_Code will need to be retrieved for all tables and obviously you can filter on date on two of the tables. The other two tables (without the date) you can filter on based on the School_Code retrieved from the previous filtered query.
E.g
SELECT School_Code, acyear INTO #tmpERData
FROM ERData WHERE acyear = '2006-07'
-- Do the same for BasicData filtering on acyear
-- in the next temporary table i have filterered further based on School_codes from the previous filtered table as thats what you are going to be joining on anyway
SeLECT School_Code, Building_status, Tot_Clrooms, Classrooms_in_good_condition INTO #tmpFacilityData
FRM FacilityData
WHRE School_Code IN (Select School_Code from #tmpERData)
...
Then you should have your data filtered and you can use the temporary tables as a basis for your JOIN.
...
FROM #tmpBasicData B
INNER JOIN #tmpERData E ON B.School_Code = E.School_Code
INNER JOIN #tmpFacilityData F ON F.School_Code = E.School_Code
INNER JOIN #tmpGeneralData G ON G.School_Code = E.School_Code
Hope that makes sense. You can then wrap your whole code in a proc and just execute that Proc Passing in your Date as a parameter.
However going back to basics how is your TempDB configured? Maybe its too small anyway? Check there's enough space to grow on the disk its on and that there is no cap on it. A million rows in each table is not that much data unless they are very wide table. Also as stated in a previous post with regards to the use of your data types such as nvarchar(255) for acyear is certainly making them much more inefficient.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply