?
Solved

Joining two tables into one table

Posted on 2013-05-28
7
Medium Priority
?
336 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 668 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 668 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 664 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

777 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