Solved

Exclude fields after SELECT *

Posted on 2011-09-16
19
517 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is this datetime? 1 33
SQL- GROUP BY 4 51
Ajax on ASP 2 66
SP result not being displayed 5 68
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 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