Solved

Joining two tables into one table

Posted on 2013-05-28
7
332 Views
Last Modified: 2013-06-02
Hi, I have two tables tableA and tableB and below are the schemas. Now, I want to get rid of the one
of these tables as I have to combine them into one table to improve the performance in my
stored procedure's and udf's where these both tables are joined... The joining key for relating these 2 tables is the questiondependencyid.... some questiondependencyid’s can have more than one row in the table B....
Can anyone please give me that tsql script to do this ? what is the best method to do this ?

TableA Schema with sample data

QuestionDependencyID questionid   createdby         createddate
32                                            97                  1                 2007-12-06 16:24:00
33                                            98                  1                 2007-12-08 16:24:00

TableB Schema with sample data

Dependentruleid    QuestionDependencyID    Value    
10                                     32                                       1
11                                    32                                        2
12                                    32                                        4
13                                    33                                       9

Many Thanks
0
Comment
Question by:gvamsimba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 9

Expert Comment

by:sarabhai
ID: 39200858
Did you want to combine two table or need query on these two table.
Please tell something with e.g.
0
 

Author Comment

by:gvamsimba
ID: 39200890
no, Basically, I need to get rid of one of these two tables by combining the relevant columns
and data into only one table.

so, I need some one to suggest which is the best table to combine all the relevant columns from
these tables and how to do it..

Thanks
0
 
LVL 9

Accepted Solution

by:
sarabhai earned 167 total points
ID: 39200893
If you want to combine these two table then in any one table add columns of other table fire that update command.

ALTER tableB add (questionid    int,  createdby int, createddate int)

now need to update these new created columns from tableB.

Update tableB
SET questionid = A.questionid
,createdby = A.createdby
,createddate = A.createddate
FROM tableB B INNER JOIN tableA A ON B.QuestionDependencyID = A.QuestionDependencyID
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39200929
please provide the indexes that exist on these tables also.

(and, they most probably are not really called TableA and TableB, so I suggest you use the real names of all items - this will be easier for you as you won't have to "translate")

It seems that the 2 tables are normalized to some degree, and what you are proposing is to de-normalize them for performance gains. This might not be the best course to follow.

are you suggesting the use of temporary tables? or proposing to permanently de-normalize?
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 167 total points
ID: 39201412
The structure of the 2 tables is narrow so any denormalization will not bring to much benefit to insignificant. I strongly advice against that. You should not have any performance issues with joining the 2 table if both of them have indexes on the joining column, QuestionDependencyID . Most likely the SQL code in your procedure and function needs to be addressed. Maybe you should start there.
0
 

Author Comment

by:gvamsimba
ID: 39201648
permanently de-normalize
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 166 total points
ID: 39203104
a join between 2 tables, if they are properly indexed, is almost certainly not the cause of performance issues.

Please provide the full definitions of these tables. (incl. indexes)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question