SQL Insert all records from one table to another table without specific the columns

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

You could try this   SELECT   INTO foo FROM foo bk

User · Answer

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

User · Answer

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

User · Answer

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    You wish to manage a backup audit process for a table  You probably have a few of those and wish to avoid altering dependent objects on every column addition removal  The backup table may contain additional columns for auditing purposes    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

User · Answer

Use this   SELECT   INTO new table name FROM current table name

[sql] SQL: Insert all records from one table to another table without specific the columns

Examples related to sql

Examples related to sql-server

Examples related to syntax