Joining tables based on column name

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
sherbug1015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sachinpatil10dCommented:
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
sherbug1015Author Commented:
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
LowfatspreadCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

LowfatspreadCommented:
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
Bhavesh ShahLead AnalysistCommented:
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
Bhavesh ShahLead AnalysistCommented:
hi,

my window was open from long, so could not see your comment sir.
0
sherbug1015Author Commented:
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
LowfatspreadCommented:
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
sherbug1015Author Commented:
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
LowfatspreadCommented:
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
jogosCommented:
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
sherbug1015Author Commented:
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
LowfatspreadCommented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
sherbug1015Author Commented:
I've requested that this question be deleted for the following reason:

No acceptable solutions
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have to disagree, especially as my comment did not get any specific response about as to why it would not be acceptable ...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, please just tell me what you more info you need to get your problem solved
0
sherbug1015Author Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.