Solved

Exclude fields after SELECT *

Posted on 2011-09-16
19
500 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
 
LVL 142

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:RQuadling
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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:RQuadling
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:RQuadling
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now