SQL: Insert all records from one table to another

Question

I want to insert all the record from the back up table foo_bk into foo table without specific the columns.

if i try this query

INSERT INTO foo 
SELECT *
FROM foo_bk

i'll get error "Insert Error: Column name or number of supplied values does not match table definition."

Is it possible to do bulk insert from one table to another without supply the column name? I've google it but can't seem to find an answer. all the answer require specific the columns.

Answer 1

All the answers above, for some reason or another, did not work for me on SQL Server 2012. My situation was I accidently deleted all rows instead of just one row. After our DBA restored the table to dbo.foo_bak, I used the below to restore. NOTE: This only works if the backup table (represented by dbo.foo_bak) and the table that you are writing to (dbo.foo) have the exact same column names.

This is what worked for me using a hybrid of a bunch of different answers:

USE [database_name];
GO
SET IDENTITY_INSERT dbo.foo ON;
GO
INSERT INTO [dbo].[foo]
           ([rown0]
           ,[row1]
           ,[row2]
           ,[row3]
           ,...
           ,[rown])
     SELECT * FROM [dbo].[foo_bak];
GO
SET IDENTITY_INSERT dbo.foo OFF;
GO

This version of my answer is helpful if you have primary and foreign keys.

Answer 2

Use this

SELECT *
INTO new_table_name
FROM current_table_name