question

rsheik avatar image
rsheik 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
insert
tables.jpg (37.9 KiB)
result.jpg (29.6 KiB)
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Squirrel.... I am working on Tabular to Cross tabular converting process. Thanx for Ur reply
0 Likes 0 ·
Not very sure what you mean by that, but if you can normalize your table, it is will be much easier to do any query.
0 Likes 0 ·
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.
0 Likes 0 ·
Squirrel avatar image
Squirrel answered
INSERT INTO Target_Table (Name, ID, [20160501], [20160601], [20160701] ) SELECt Name, ID, [20160501], [20160601], [20160701] = 0 FROM Source_Table
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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??
0 Likes 0 ·
what do you mean ? The columns in your table is not fix it is added dynamically ?
0 Likes 0 ·
Squirrel avatar image
Squirrel answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel avatar image
Squirrel answered
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)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.