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.
You should not ever want to do this. Select * should not be used as the basis for an insert as the columns may get moved around and break your insert (or worse not break your insert but mess up your data. Suppose someone adds a column to the table in the select but not the other table, you code will break. Or suppose someone, for reasons that surpass understanding but frequently happen, decides to do a drop and recreate on a table and move the columns around to a different order. Now your last_name is is the place first_name was in originally and select * will put it in the wrong column in the other table. It is an extremely poor practice to fail to specify columns and the specific mapping of one column to the column you want in the table you are interested in.
Right now you may have several problems, first the two structures don't match directly or second the table being inserted to has an identity column and so even though the insertable columns are a direct match, the table being inserted to has one more column than the other and by not specifying the database assumes you are going to try to insert to that column. Or you might have the same number of columns but one is an identity and thus can't be inserted into (although I think that would be a different error message).
Use this
SELECT *
INTO new_table_name
FROM current_table_name
Per this other post: Insert all values of a..., you can do the following:
INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
SELECT Foo, Bar, Fizz, Buzz
FROM initial_table
It's important to specify the column names as indicated by the other answers.
You need to have at least the same number of columns and each column has to be defined in exactly the same way, i.e. a varchar column can't be inserted into an int column.
For bulk transfer, check the documentation for the SQL implementation you're using. There are often tools available to bulk transfer data from one table to another. For SqlServer 2005, for example, you could use the SQL Server Import and Export Wizard. Right-click on the database you're trying to move data around in and click Export to access it.
SQL 2008 allows you to forgo specifying column names in your SELECT if you use SELECT INTO rather than INSERT INTO / SELECT:
SELECT *
INTO Foo
FROM Bar
WHERE x=y
The INTO clause does exist in SQL Server 2000-2005, but still requires specifying column names. 2008 appears to add the ability to use SELECT *.
See the MSDN articles on INTO (SQL2005), (SQL2008) for details.
The INTO clause only works if the destination table does not yet exist, however. If you're looking to add records to an existing table, this won't help.
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.
You could try this:
SELECT * INTO foo FROM foo_bk
This is a valid question for example when wanting to append newly imported rows from an imported csv file of the same raw structure into an existing table which may have DB constraints set up such as PKs and FKs.
I would simply do the following, for example:
INSERT INTO roles select * from new_imported_roles_from_csv_file
I also like this when if any new rows violate uniqueness during this operation, the INSERT will fail, not insert anything and in away 'protect' the target table from bad inbound data.
As you probably understood from previous answers, you can't really do what you're after. I think you can understand the problem SQL Server is experiencing with not knowing how to map the additional/missing columns.
That said, since you mention that the purpose of what you're trying to here is backup, maybe we can work with SQL Server and workaround the issue. Not knowing your exact scenario makes it impossible to hit with a right answer here, but I assume the following:
I wish to suggest two options for you:
The efficient practice (IMO) for this can be to detect schema changes using DDL triggers and use them to alter the backup table accordingly. This will enable you to use the 'select * from...' approach, because the column list will be consistent between the two tables.
I have used this approach successfully and you can leverage it to have DDL triggers automatically manage your auditing tables. In my case, I used a naming convention for a table requiring audits and the DDL trigger just managed it on the fly.
Another option that might be useful for your specific scenario is to create a supporting view for the tables aligning the column list. Here's a quick example:
create table foo (id int, name varchar(50))
create table foo_bk (id int, name varchar(50), tagid int)
go
create view vw_foo as select id,name from foo
go
create view vw_foo_bk as select id,name from foo_bk
go
insert into vw_foo
select * from vw_foo_bk
go
drop view vw_foo
drop view vw_foo_bk
drop table foo
drop table foo_bk
go
I hope this helps :)
