September 24, 2005 at 12:01 am
i was trying to select all data except the ID which is my primary key, i was using the result to fill my virtual table from a stored procedure but since the ID from my table and the virtual table im filling might have the same value (my virtual table is used for adding/editing new entry) im having a conflict, so i thought of maybe selecting the fields except for ID might do the trick, but since i have a lot of tables with lots of fields i might need another stored procedure to handle this
so far i have this for listing all my fields without the ID
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TEST_TABLE'
AND COLUMN_NAME <> 'ID'
but i couldnt get it right returning the fields values
i tried this but it only works if it returns 1 field and the value gives the column name
SELECT (SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TEST_TABLE'
AND COLUMN_NAME <> 'ID') FROM TEST_TABLE
slow down when you need to hurry, stop when you need to move on,
look back when you need to forget, or you might slip and leave sanity
September 25, 2005 at 2:49 am
What you are trying to do is a little more complex than this - though this gets a bit closer, I think:
declare @table varchar(50)
declare @str1 nvarchar(1000)
set @table = 'test_table'
set @str1 = ''
SELECT @str1 = @str1 + ', ' + Column_Name
FROM Information_Schema.Columns
WHERE Table_Name = @table and column_name <> 'ID'
ORDER BY Ordinal_Position
--Remove the first three characters (the surplus comma)
set @str1 = right(@str1, len(@str1) - 2)
--Now add in the rest of the select text
set @str1 = 'select ' + @str1 + ' from ' + @table
exec sp_executesql @str1
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 25, 2005 at 7:58 pm
tnx Mr. or Ms. 500, it working now, heheh, now i dont have to type all those fields, tnx again!
cheers!
slow down when you need to hurry, stop when you need to move on,
look back when you need to forget, or you might slip and leave sanity
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply