Solved

Joining two tables into one table

Posted on 2013-05-28
7
322 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 41
SQL 2008 R2 syntax 11 29
SQL BULK INSERT Comma Delimited Issue 8 48
any adverse effect when modifying data type on a busy table. 4 11
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…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

786 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