Solved

Joining two tables into one table

Posted on 2013-05-28
7
319 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 48

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 26

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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now