Unanswered: INSERT INTO SELECT to table with more columns
This one might be simple one but I can not find a fix anywhere on the web. I have two tables...TableA with 65 columns and TableB with only 60 columns. The two tables are the same as for column headers except for the additional 5 columns in TableA. I was trying:
INSERT INTO TableA SELECT * FROM TableB WHERE Item = 'Something'
but I error out because of the additional 5 columns. I have NO control of the design of the tables.
Anyone have any Ideas?
PS. I also tried:
INSERT INTO (TableA.Column1,TableA.Column2,....) Value (TableB.Column1, TableB.Column2,...)
but it would not let me being that was too may lines.
You can write a little script utility to produce your column names using the system tables. Or you can try using something like SQL Prompt that will write that stuff out for you (as well as other useful time savers).
DECLARE @columns varchar(max)
SELECT @columns = Coalesce(@columns + ', ', '') + column_name
WHERE table_schema = 'dbo'
AND table_name = 'people'
SET @columns = NULL
SELECT @columns = Coalesce(@columns + ', ', '') + name
WHERE object_id = Object_ID('dbo.people')