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?

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. — Nick Vaccaro, Mar 05 '12 at 21:01
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. — HLGEM, Mar 05 '12 at 21:38
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. — Eric, Mar 05 '12 at 21:45
possible duplicate of [SELECT * EXCEPT](http://stackoverflow.com/questions/413819/select-except) — onedaywhen, Mar 06 '12 at 08:30
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. — KWallace, Nov 30 '19 at 19:05

Ta01 · Accepted Answer · 2012-03-05T21:04:23.417

60

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.

edited Mar 05 '12 at 21:04

answered Mar 05 '12 at 20:57

Ta01

31,040
13
70
99

1

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. – Kibbee Mar 05 '12 at 21:37
32

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.. – Ta01 Mar 05 '12 at 21:40
3

I chose this answer because he answered my question. It's not the best solution and the points above are valid. – Eric Mar 05 '12 at 21:47
1

+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 Aug 19 '13 at 07:19
...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. – Milan Apr 01 '15 at 16:17

score 2 · Answer 2 · answered Oct 08 '13 at 11:55

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

answered Oct 08 '13 at 11:55

Dave Mateer

6,588
15
76
125

score 2 · Answer 3 · answered Mar 05 '12 at 20:44

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.

score 2 · Answer 4 · answered Mar 05 '12 at 20:57

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.

score 1 · Answer 5 · answered Oct 15 '14 at 17:46

1

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

answered Oct 15 '14 at 17:46

davidWazy

61
6

Why is creating temporary tables listed as a disadvantage versus using a view? I'm not questioning the claim, just wondering about the reason. – Konrad Viltersten Jul 02 '15 at 13:43
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. – davidWazy Mar 23 '16 at 18:32

score 0 · Answer 6 · edited Mar 06 '12 at 08:33

0

Really, honestly it takes ten seconds or less to pull all of the columns over from the object browser and then delete the identity column from the list. It is a bad idea to use select * for anything but quick ad hoc query.

edited Mar 06 '12 at 08:33

onedaywhen

55,269
12
100
138

answered Mar 05 '12 at 21:36

HLGEM

94,695
15
113
186

2

But what if the OP's is a quick ad hoc query? ;) – onedaywhen Mar 06 '12 at 08:35
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? – Teun D Aug 19 '13 at 07:21
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. – HLGEM Aug 19 '13 at 14:24
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. – HLGEM Aug 19 '13 at 14:24

score -1 · Answer 7 · edited May 23 '17 at 12:32

In answer to a related question (SELECT * EXCEPT), I point out the truly relational language Tutorial D allows projection to be expressed in terms of the attributes to be removed instead of the ones to be kept e.g.

my_relvar { ALL BUT description }

However its INSERT syntax requires tuple value constructors to include attribute name / value pairs e.g.

INSERT P
   RELATION 
   {
      TUPLE { PNO PNO ( 'P1' ) , PNAME CHARACTER ( 'Nut' ) }, 
      TUPLE { PNO PNO ( 'P2' ) , PNAME CHARACTER ( 'Bolt' ) }
   };

Of course, using this syntax there is no column ordering (because it is truly relational!) e.g. this is semantically equivalent:

INSERT P
   RELATION 
   {
      TUPLE { PNO PNO ( 'P1' ) , PNAME CHARACTER ( 'Nut' ) }, 
      TUPLE { PNAME CHARACTER ( 'Bolt' ) , PNO PNO ( 'P2' ) }
   };

The alternative would be to rely fully on attribute ordering, which SQL does partially e.g. this is a close SQL equivalent to the the above:

INSERT INTO P ( PNO , PNAME ) 
   VALUES        
      ( PNO ( 'P1' ) , CAST ( 'Nut'  AS VARCHAR ( 20 ) ) ) , 
      ( PNO ( 'P2' ) , CAST ( 'Bolt' AS VARCHAR ( 20 ) ) );

Once the commalist of columns has been specified the VALUES row constructors have the maintain this order, which is not ideal. But at least the order is specified: your proposal would rely on some default order which may be possibly non-deterministic.

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

7 Answers7

Linked