[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Exclude fields after SELECT *

Posted on 2011-09-16
19
Medium Priority
?
528 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

650 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