Solved

Joining two tables into one table

Posted on 2013-05-28
7
312 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
permanently de-normalize
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 166 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

9 Experts available now in Live!

Get 1:1 Help Now