Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

Exclude fields after SELECT *

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
Tra71
Asked:
Tra71
  • 7
  • 4
  • 3
  • +3
1 Solution
 
dsackerContract ERP Admin/ConsultantCommented:
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
 
mmr159Commented:
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
 
Lee SavidgeCommented:
SQL Server doesn't have this facility.

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

Lee
0
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.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
mmr159Commented:
Of course, if you have a boatload of columns, up the @columns declaration.
0
 
Tra71Author Commented:
Thanks, I'm trying the @colmns declaration, I need to do this through an asp page...
0
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
Tra71Author Commented:
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
 
Tra71Author Commented:
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
 
dsackerContract ERP Admin/ConsultantCommented:
>>> 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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
Tra71Author Commented:
The table is not the information_schema.  The above resopnses, did not resolve my issue.  
0
 
dsackerContract ERP Admin/ConsultantCommented:
Where did the information that filled the table come from?

information_schema?
systables, sysobjects, etc?
0
 
Tra71Author Commented:
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
 
dsackerContract ERP Admin/ConsultantCommented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
Objection removed by me also.
0
 
Tra71Author Commented:
Thank you :)
0
 
Tra71Author Commented:
As per previous comments.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now