asked
Inserting values from another table with un-matched columns
Hi... I am having tables with name : Target_Table with columns 20160501,20160601,20160701 and Source_Table with columns 20160401,20160501,20160501 (note: Observe the columns carefully) ![alt text][1] Now.. I want to insert values into Target_Table from Source_Table. But problem is there are unmatched columns in the given tables. how to handle it... my expected o/p is as below... ![alt text][2] [1]: /storage/temp/1409-tables.jpg [2]: /storage/temp/1407-result.jpg
@Squirrel.... I am working on Tabular to Cross tabular converting process. Thanx for Ur reply
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
INSERT INTO Target_Table (Name, ID, [20160501], [20160601], [20160701] ) SELECt Name, ID, [20160501], [20160601], [20160701] = 0 FROM Source_Table
Thanq @squirrel for ur responce. I have given a small example. Actually I am working on Dynamic Columns. For that I need to check whether, dynamically generated columns in target_table are there in Source_table or not??
Are you trying to create a sliding window kind of effect on your table ? those columns looks like a date column. Why not normalize it and keep your table structure like [Name], [ID], [Date] ? Instead of creating one column per month
here is the dynamic sql that does that declare @sql nvarchar(max), @cols nvarchar(max) select @cols = isnull(@cols + ',', '') + quotename([name]) from sys.columns where object_id = object_id('Target_Table') order by column_id select @sql = N'INSERT INTO Target_Table (' + @cols + ')' select @cols = NULL select @cols = isnull(@cols + ',', '') + quotename(t.[name]) + case when quotename(s.[name]) is not null then '' else '= 0' end from sys.columns t left join sys.columns s on s.object_id = object_id('Source_Table') and t.[name] = s.[name] where t.object_id = object_id('Target_Table') order by t.column_id select @sql = @sql + char(13) + char(10) + 'SELECT ' + @cols + char(13) + char(10) + 'FROM Source_Table;' print @sql -- exec (@sql)
16 People are following this question.
