?
Solved

Joining tables based on column name

Posted on 2011-11-01
20
Medium Priority
?
364 Views
Last Modified: 2012-05-12
I have a temporary table #tmpTable that will be created dynamically.   - The code to create the temp table will look like the attached code.  

I have another permanent table that will contain the data that I want to insert into my temp table based.  

For example if my temp table has ColumnA, ColumnB and ColumnC then the permanent table would have the same columns plus the data.

How would I join these two tables together to get data from one table to another.  

 createtemptable.txt  PERMANENTTABLEFIELDS.txt
0
Comment
Question by:sherbug1015
  • 6
  • 5
  • 4
  • +3
19 Comments
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37062150
Try this
sample code

declare @sql1 varchar(max)

select @sql1 = 'select 
     fkItemID ' 
			select @sql1 = @sql1 + ',replaced_item_numbers'
		
        select @sql1 = @sql1 + ' into #tmpIPOGItems from tblProfileQuestion '
        print @sql1
		exec(@sql1)

Open in new window

0
 

Author Comment

by:sherbug1015
ID: 37062213
The problem is not how to create the temporary table, that is done.    I need to now insert into this temporary table from the permanent table that will have all the fields plus the data in the temporary table.  
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37062494
like this ?

you intentional have the marketelu column in twice?
you have a potential problem with commas after/due to affiliate in your code..
declare @crsql varchar(max),@selsql varchar(max)

;with cte as (
	 select 'Int' as Type,'Affiliate' as shortdesc, 'Item' as association ,nullif('','') as newname 
		
union all select 'Int' as Type,'DirectTV' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'DSL' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'EMS' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'MobileTV' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'UMTS' as shortdesc, 'Item' as association ,'3G'	
union all select 'Int' as Type,'Zodiac3G' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'iPhone' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'HomeConFloor' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'HomeConWall' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'PhonePlaqueColorDot' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'MarketELU' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'3GMicroCell' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type, 'MarketELU' as shortdesc, 'Item' as association ,'isDMA'	
union all select 'Int' as Type,'isEnclosedMall' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'CompConnectTech' as shortdesc, 'Item' as association ,nullif('','') as newname 
	)
,cte1 as (select  * from cte 
           where exists 
               (SELECT IPOGFeed From tblProfileQuestion as x
                 where x.shortdesc = cte.shortdesc
                    AND x.Association = cte.association) 
         )
select @crsql=coalesce(@crsql+',','')+'['+coalesce(newname,shortdesc)+'] '+Type
      ,@selsql=coalesce(@selsql+',','')+'['+coalesce(newname,shortdesc)+']'
  from cte1

select @crsql='Create Table   #tmpIPOGItems (fkItemID int,' 
             +@crsql
             +',replaced_item_numbers varchar(200)'
             +')'
     ,@selsql='Insert into #tmpipogitems '
             +'select fkitemid,'
             +@selsql
             +' from YOURTABLENAMEHERE '
             +'YOUR WHERE CLAUSE HERE '

--print @crsql			     		    	
exec(@crsql)
--print @selsql
exec(@selsql)

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37062512
sorry
declare @crsql varchar(max),@selsql varchar(max)

;with cte as (
	 select 'Int' as Type,'Affiliate' as shortdesc, 'Item' as association ,nullif('','') as newname 
		
union all select 'Int' as Type,'DirectTV' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'DSL' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'EMS' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'MobileTV' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'UMTS' as shortdesc, 'Item' as association ,'3G'	
union all select 'Int' as Type,'Zodiac3G' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'iPhone' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'HomeConFloor' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'HomeConWall' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'PhonePlaqueColorDot' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'MarketELU' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'3GMicroCell' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type, 'MarketELU' as shortdesc, 'Item' as association ,'isDMA'	
union all select 'Int' as Type,'isEnclosedMall' as shortdesc, 'Item' as association ,nullif('','') as newname 
union all select 'Int' as Type,'CompConnectTech' as shortdesc, 'Item' as association ,nullif('','') as newname 
	)
,cte1 as (select  * from cte 
           where exists 
               (SELECT IPOGFeed From tblProfileQuestion as x
                 where x.shortdesc = cte.shortdesc
                    AND x.Association = cte.association
                    and ipogfeed=1
                ) 
         )
select @crsql=coalesce(@crsql+',','')+'['+coalesce(newname,shortdesc)+'] '+Type
      ,@selsql=coalesce(@selsql+',','')+'['+coalesce(newname,shortdesc)+']'
  from cte1

select @crsql='Create Table   #tmpIPOGItems (fkItemID int,' 
             +@crsql
             +',replaced_item_numbers varchar(200)'
             +')'
     ,@selsql='Insert into #tmpipogitems '
             +'select fkitemid,'
             +@selsql
             +' from YOURTABLENAMEHERE '
             +'YOUR WHERE CLAUSE HERE '

--print @crsql			     		    	
exec(@crsql)
--print @selsql
exec(@selsql)

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 37062539
Hi,

what is the actual purpose of creating table this way?

Creating #table columns from conditions does not make sense.
I mean it will increase coding headache.

If I have to do this code, then I would create #temp table with all possible columns.

Based on requirement execute Insert/Update statement.
Create table #table
(
col1 varchar(20),
col2 varchar(20)
)

Open in new window


insert into #table
values
select col1, col2 from maintable

Open in new window



Or if you can explain more in detail then expert(s) can help you... :-)


-Bhavesh
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 37062545
hi,

my window was open from long, so could not see your comment sir.
0
 

Author Comment

by:sherbug1015
ID: 37062643
LowFatspread - The tempoary table must be dynamically created.  I may not have the same columns each time this stored proc is run.  

My main problem is the Insert into #tmpIpogItems....statement.   That is where I am stuck.  The permanent table will have the data that must go into the temp table where the columns are equal.  

I really need your help.  Thanks.

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37062873
i have shown you a dynamic creation of your table and an insert based on it...

?

please explain what your problem is... ?

the simplest method is to generate the insert at the same time as the table creation....

what is the process for?
why have you decided on this method which is generally considered poor both from a performance, and secuirty point of view?

 
0
 

Author Comment

by:sherbug1015
ID: 37062960
The script that you wrote will not allow for there to be more or less columns in the temp table.  I would have to go into the stored proc and adjust the column list each time.  My problem is not creating the temp table, I merely attached that script as a reference.  

What I need to do is once I have the temp table created then I need to insert into the temp table the data contained in the permanent table.  

For example the temp table will always have a fkItemID column.  Also, if the temp table has an Affiliate column then I want to essential say Insert into #temptable select fkItemID, Affiliate from tblItemAttributes where ???? .  I will not know which columns are in the temp table unless I read from syscolumns.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37063208
that was my point just as you would adjust the set of columns in your original example do the same to mine...

the point is generate both sets of code from the same set of data.!

alternatively as you suggest... join the columns on the system tables and generate from there ... but that potyentially gives you problems with situations like the marketelu scenario...
0
 
LVL 25

Expert Comment

by:jogos
ID: 37063293
sys.syscolumns or better information_schema.columns is the way to collect the column-names
What's the use of creating a #table with unkown columns? How will you use it later when you don't know their names.  Do you realize what happens when suddenly your table gets extendend with a very large column (images,....) which probably do not have any use in that #table
0
 

Author Comment

by:sherbug1015
ID: 37063337
Lowfatspread - I have a set of requirements that I am working to so I cannot re-engineer this.  

Can you give me the code for using syscolumns?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37063474
since the code you provided for the temp table isn't robust and contains  potential errors

you probably should use that as a mechanism to force a redesign...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37064433
I see 2 options ...
>Create Table   #tmpIPOGItems

instead of just creating the table, you could do a  

SELECT <based on the conditions you used to define the table>
  INTO #tmpIOPGItems
 FROM ...
 WHERE ...

Open in new window


OR, in the code you define the CREATE TABLE statements, you also define the INSERT INTO / SELECT statement as needed ...
do you need help there?
0
 

Author Comment

by:sherbug1015
ID: 37078349
I've requested that this question be deleted for the following reason:

No acceptable solutions
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37078350
I have to disagree, especially as my comment did not get any specific response about as to why it would not be acceptable ...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37078358
so, please just tell me what you more info you need to get your problem solved
0
 

Author Comment

by:sherbug1015
ID: 37078439
angelll

You focused only on creating the temporary table which I stated above was not the problem.  I could not get any of the experts to take the focus off the temp table and focus on the issue of inserting the data into the temp table.  
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 37078769
well, I posted this in my comment:
OR, in the code you define the CREATE TABLE statements, you also define the INSERT INTO / SELECT statement as needed ...
do you need help there?


so, the code would look like this:

declare @sql1 varchar(max)
declare @insert varchar(max)
declare @select varchar(max)

set @sql1 = 'Create Table   #tmpIPOGItems (
     fkItemID int ' 
set @insert = 'insert into #tmpIPOGItems ( fkItemID '
set @select = 'select fkItemID ' 

	 If (Select IPOGFeed from tblProfileQuestion where shortdesc = 'Affiliate' and Association = 'Item') = 1 
         begin
		set @sql1 = @sql1 + ', Affiliate int'
                set @insert = @insert + ', Affiliate '
                set @select = @select + ', 123 as Affiliate '
	 end

  ... etc etc


       set @sql1 = @sql1 + ',replaced_item_numbers varchar(200)'
       set @insert = @insert + ' , replaced_item_numbers '
       set @select = @select ', ''abc'' as replaced_item_numbers '

       set @sql1 = @sql1 + ')'			     		    	
       set @insert = @insert + ' )'
      set @select = @select + ' from source_table where ... and ... '

     --- create the table
	exec(@sql1)

     --- insert the data
     exec(@insert + @select)

Open in new window


so, you have to "couple" the construction of the create table and the insert/select statements in the same way
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month4 days, 9 hours left to enroll

601 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