Insert into select * for huge columns lists

19. July 2012 20:26 by Mrojas in SQL Server  //  Tags: , , ,   //   Comments (0)

It is very easy to insert rows from one table to the other using an insert into and select * statement but to use this statement you need sometimes to provide the columns list and that is boring :(

So I quick way to do that is use a T-SQL snipped like this:

DECLARE @columns varchar(max)

SELECT @columns = Coalesce(@columns + ', ', '') +  column_name
FROM   information_schema.columns
WHERE  table_schema = 'dbo'
AND    table_name = 'Account'
    BY ordinal_position

PRINT @columns