Exclude fields after SELECT *

You'll have to explicitly list them.

INSERT INTO [table]
( field1, field2, field4, field 5) -- skipping field3
SELECT field1, field2, field4, field 5
FROM Table
WHERE Table.[ID] = 1;

Using dynamic SQL:

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
-- One method of selecting 'all colums but...'
-- Replace t_name,c_name
-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

DECLARE @columns VARCHAR(255)

SELECT @columns = ISNULL(@columns + ',','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 't_name'
	AND column_name != 'c_name'
	-- AND column_name NOT IN ('c_name_1','c_name_2','c_name_x')

PRINT 'SELECT ' + @columns + ' FROM t_name'
EXEC ('SELECT ' + @columns + ' FROM t_name')

Select allOpen in new window

SQL Server doesn't have this facility.

It would be good to have select *, -mycol1, -mycol2 from mytable but sadly not.

Lee

I confirm: you have to list those columns you want, there is no way (in simple SQL) to "skip" some.
the dynamic sql above is just 1 way to "pseudo-automate" it...

Of course, if you have a boatload of columns, up the @columns declaration.

Thanks, I'm trying the @colmns declaration, I need to do this through an asp page...

If you do this sort of thing a lot, then write a stored procedure that will create the stored procedures for you.

If you make this run as a Database Trigger when the table layouts change, then you add a column to the DB (or remove one) and the sp_InsertIntoTableWithoutId stored procedure could be automatically produced.

So, then, in your main app, rather than building a long SQL statement dynamically, you use something like ...

'CALL server.database.owner.sp_InsertIntoTableWithoutId(' . concat(',', repeat('?', count(columns_collection_which_has_to_match_the_table))) . ')'

as your prepared statement and then bind the columns_collection to the statement.

No need to manually name anything as long as you use a consistent timeframe for updating the code and the schema - i.e. NOT on your live server!!!

I have found a work around.  I have a table where the field names are stored and I loop through this to pick the fields that I want to include.  If the fields change now in that table, I don't need to edit my page :)

Thanks for the responses.

I've requested that this question be closed as follows:

Accepted answer: 0 points for Tra71's comment http:/Q_27312212.html#36574468

for the following reason:

Closed as per above note

>>> I'm trying to avoid writing a long sql string.  Is there anyway of inserting into a table all fields exlcuding one column (primary key)?

Sounds like in the long run, you wrote a series of sql strings, some of which build your table information, other of which convert that table information into a dynamic string.

In other words, you would have to have factored in my and others suggestions. I would think points should be merited to the participants of this question.

The table containing the list of columns is the INFORMATION_SCHEMA. That's its job. The columns, tables, views, etc. are actually defined in a database.

If nothing else, mmr159's comment http://www.experts-exchange.com/Q_27312212.html#36549636 is the most common approach.

Using a database trigger (rather than a table trigger) is another option that I suggested.

The table is not the information_schema.  The above resopnses, did not resolve my issue.  

Where did the information that filled the table come from?

information_schema?
systables, sysobjects, etc?

Very kind of you to even answer my concern, since you really owed me nothing, and that is genuinely appreciated.

I remove my objection.

Objection removed by me also.

Thank you :)

As per previous comments.