Solved

Exclude fields after SELECT *

Posted on 2011-09-16
19
513 Views
Last Modified: 2012-05-12
Hi,

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)?

Something like this?

INSERT INTO [table]
SELECT table.*, EXCEPT or EXCLUDE [Table ID]  <<<Primary key
FROM Table
WHERE Table.[ID]=1;

thanks
0
Comment
Question by:Tra71
  • 7
  • 4
  • 3
  • +3
19 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 36549624
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;
0
 
LVL 7

Expert Comment

by:mmr159
ID: 36549636
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')

Open in new window

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36549649
SQL Server doesn't have this facility.

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

Lee
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36549652
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...
0
 
LVL 7

Expert Comment

by:mmr159
ID: 36549655
Of course, if you have a boatload of columns, up the @columns declaration.
0
 

Author Comment

by:Tra71
ID: 36549743
Thanks, I'm trying the @colmns declaration, I need to do this through an asp page...
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36549752
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!!!

0
 

Author Comment

by:Tra71
ID: 36574468
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.
0
 

Author Comment

by:Tra71
ID: 36574502
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
0
 
LVL 20

Expert Comment

by:dsacker
ID: 36574503
>>> 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.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36574522
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.
0
 

Author Comment

by:Tra71
ID: 36574579
The table is not the information_schema.  The above resopnses, did not resolve my issue.  
0
 
LVL 20

Expert Comment

by:dsacker
ID: 36574589
Where did the information that filled the table come from?

information_schema?
systables, sysobjects, etc?
0
 

Accepted Solution

by:
Tra71 earned 0 total points
ID: 36574655
This is a table that's filled in manually.  It manages what the user sees for various forms.  However, what I have done could have worked looking up the record table also.  
strSQL = "SELECT * FROM PageDefs_Record WHERE [PageName] = '" & "Clients_Sub" & "' AND [FieldName] <> '" & "Added By" & "' AND [FieldName] <> '" & "Connection Type" & "'"
	strSQL = strSQL & " AND [FieldName] <> '" & "Date Added " & "' AND [FieldName] <> '" & "Diary Date" & "' AND [FieldName] <> '" & "Notes" & "' AND [FieldName] <> '" & "Last Updated" & "'"
	strSQL = strSQL & " AND [FieldName] <> '" & "Updated By" & "';"
	objRS.Open strSQL, objConn
	
'Creates the field list ready for the query...
	strQuery = ""
	Do Until objRS.EOF
    	strQuery = strQuery & "[" & objRS("FieldName") & "],"
       	objRS.MoveNext
	Loop	
	
	'Remove the trailing "]," form strQuery...
		strQuery =  Left(strQuery, Len(strQuery) - 2)
	
	'Remove the first "[" from strQuery...
		strQuery = Right(strQuery, Len(strQuery) - 1)
	
	'Create new records...
		arrIDs = Split(strMobileNos, ";")
			
		For n = 0 To UBound(arrIDs)
	
	'Adds the new records...			
		strSQL = "INSERT INTO [Client Mobiles] ( [" & strQuery & "],[Added By], [Connection Type],[SubID] ) SELECT [" & strQuery & "], '" & Replace(Session("UserName"),"'","''") & "', '" & "Renewing" & "', "
		strSQL = strSQL & "" & Request.Form("SubID") & " FROM [Client Mobiles] WHERE ID = " & arrIDs(n) & ";"		
	
		objRS2.Open strSQL, objConn

Open in new window

0
 
LVL 20

Expert Comment

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

I remove my objection.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36574907
Objection removed by me also.
0
 

Author Comment

by:Tra71
ID: 36574921
Thank you :)
0
 

Author Closing Comment

by:Tra71
ID: 36597847
As per previous comments.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
alert(innerHTML); 8 38
return false must be hit after calling certain command 10 43
IIS 7 and executing pages using localhost 16 24
Update one rows based on previous row 5 28
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question