T-SQL Insert into table without having to specify every column

Question

In our db there is a table that has a little over 80 columns. It has a primary key and Identity insert is turned on. I'm looking for a way to insert into this table every column EXCEPT the primary key column from an identical table in a different DB.

Is this possible?

Solution 1

You can do this quite easily actually:

-- Select everything into temp table
Select * Into 
    #tmpBigTable
    From [YourBigTable]

-- Drop the Primary Key Column from the temp table  
Alter Table #tmpBigTable Drop Column [PrimaryKeyColumn]

-- Insert that into your other big table
Insert Into [YourOtherBigTable]
    Select * From #tmpBigTable

-- Drop the temp table you created
Drop Table #tmpBigTable

Provided you have Identity Insert On in "YourOtherBigTable" and columns are absolutely identical you will be okay.

Solution 2

CREATE TABLE Tests
(
    TestID int IDENTITY PRIMARY KEY,
    A int,
    B int,
    C int
)

INSERT INTO dbo.Tests
VALUES (1,2,3)

SELECT * FROM Tests

This works in SQL2012

Solution 3

You could query Information_Schema to get a list of all the columns and programatically generate the column names for your query. If you're doing this all in t-sql it would be cumbersome, but it could be done. If you're using some other client language, like C# to do the operation, it would be a little less cumbersome.

Solution 4

No, that's not possible. You could be tempted to use

INSERT INTO MyLargeTable SELECT * FROM OtherTable

But that would not work, because your identity column would be included in the *.

You could use

SET IDENTITY_INSERT MyLargeTable ON
INSERT INTO MyLargeTable SELECT * FROM OtherTable
SET IDENTITY_INSERT MyLargeTable OFF

first you enable inserting identity values, than you copy the records, then you enable the identity column again.

But this won't work neither. SQL server won't accept the * in this case. You have to explicitly include the Id in the script, like :

SET IDENTITY_INSERT MyLargeTable ON
INSERT INTO MyLargeTable (Id, co1, col2, ...., col80) SELECT Id, co1, col2, ...., col80 FROM OtherTable
SET IDENTITY_INSERT MyLargeTable OFF

So we're back from where we started.

The easiest way is to right click the table in Management Studio, let it generate the INSERT and SELECT scripts, and edit them a little to let them work together.

Solution 5

Why not just create a VIEW of the original data, removing the unwanted fields? Then 'Select * into' your hearts desire.

• Localized control within a single view
• No need to modify SPROC
• Add/change/delete fields easy
• No need to query meta-data
• No temporary tables

View more solutions

Comments

[JNK]

You need to specify the columns...

[Nick Vaccaro]

Sounds like a case of lazy programming. If you right click on the table name, you can "script as -> insert to", and all the non-identity columns will be written for you. Shazam.

[dburges]

YoOu should be benefiting the database performance for the long run not yourself. You accepted the worst answer of the bunch becasue it doubles the work every time it is run over what the correct query would take and all to save yourself a bit of time.

[John Smith]

HLGEM, you're correct. I was looking for a quick fix now and in the future without having to use the object browser. But his answer was correct according to the question which is why i'm going with his answer.

[onedaywhen]

possible duplicate of SELECT * EXCEPT

[KWallace]

Having to resort to scripting the object properties, copy them into the clipboard, and paste them (twice) into the query text (and clean them up) seems to be a lot of unnecessary work for the developer if the database is perfectly capable doing this work itself. We use these tools to save us OUR work, not the database's work. Unless these tables are gargantuan in size, the extra database work is insignificant compared to the additional work put on the developer to do something that we made tools to do for us. Just my opinion.

[Kibbee]

Also let's not forget that in this case you're copying the entire table to a temporary table. If you only have a few rows this might work, but it could really bog down the system for a table with a lot of rows.

[Ta01]

I had no idea that the OP wanted to use for something for production, originally stated he just wanted to copy. There were no specifics around where he wanted to use it, rather he just wanted to know if it could be done. Kind of ironic that I'm downvoted for providing a solution to the original question, but hey, whatever rocks your boat - Reminder: The question was "IS IT is Possible", I like how assumptions on how big the table is, what the contrived use is, are giving me downvotes, instead of answering the question. Anyway..

[John Smith]

I chose this answer because he answered my question. It's not the best solution and the points above are valid.

[onedaywhen]

But what if the OP's is a quick ad hoc query? ;)

[Teun D]

+1 Yes, 'Select *' is often not a great idea, but in this case the explicit question was a solution without having to specify all columns. There are valid reasons to want this. Example: wanting to archive all data to a separate table and do this for many different tables. I also doubt that the overhead is all that big. If you fire many different small 'select *' queries, then yes, but with one large select or a single query reused over and over, I would think that the difference is really small.

[Teun D]

What if you want to do the same thing for all of your 1000 tables? And what if the list of columns in the table is frequently appended to and you don't want to have to update the query every time?

[dburges]

You should update the query every time as you may not need the new columns. It is irresponsible to do anything else. You might end up showing users fields that they not only don't care about but shouldn't see. Or you might end up with inserts that put data in the wrong columns because someone changed the column order in one table but not the other or inserts that break because a new column was not added to the table that is taking the data from a select. You might need to adjust updates for new columns as well. You might need to figure out what the intial data is going to be for inserts.

[dburges]

If you are making changes to thousands of tables, then use the sql server metadata to create a script to do so. It will use the columns names.

[Milan]

...just to point out that sometimes [PrimaryKeyColumn] is not just auto-increment or sequence. If the PK is generated by other means (trigger, SP, etc.) this may break the dependency.

[Konrad Viltersten]

Why is creating temporary tables listed as a disadvantage versus using a view? I'm not questioning the claim, just wondering about the reason.

[davidWazy]

TEMP table (may) use a large amount of memory / disk, where the VIEW is basically filtering the production table to the fields you desire. This method would perform quicker, not have any long-lasting overhead and can be changed as desired.