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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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