SELECT * or Column Names
-
I'm running a stored procedure that truncates a table, and then inserts rows from another table with the same structure. The table has a lot of fields (about 50). I understand the danger of running the following if one of the table structures changes:
INSERT table1 SELECT * FROM table2
but is it any more efficient to list all the column names in the INSERT and SELECT statements? Or is SELECT * regarded as lazy and potentially dangerous?
I've got several stored procedures to change, and it's hard top make the code look tidy and readable listing 50 column names twice.
Any help appreciated.
Alan
-
Alan,
As you said then INSERT INTO SELECT * is not a good option i agree with that, you need to write all the column names in your select & insert list, for better look you can keep all the columns in one line.
------------
Prakash Sawanthttp://psawant.blogspot.com
-
The short answer is Yes, it's regarded as lazy and potentionally dangerous.
Why? Well, 'efficient' isn't all about 'performance' - ie there's not really any difference in the immediate performance here - but there's also a long term side of 'efficient'.
Efficient code is easy maintained, debugged and changed.
Consider stuff like 'select * from ....' vs 'select col1, col2 from...'
It's not that hard to see which is the most 'efficient' considering all aspects.
/Kenneth
-
Thanks guys!
I totally understand that it's better from a support/maintenance point of view. I was mainly interested in whether listing column names makes a query or procedure run faster.
AlanB
-
Just a quick tip that may help to get all those column names easily and without typos; when working in query analyzer you can get a complete list of the field names by dragging and dropping the "Columns" object for the table listed in the Object Browser.
Steve
-
Thanks Steve!
That is an EXTREMELY useful tip!
Alan
-
Actually, you can get ALL the column names at once with the commas at the end with the added benefit of carriage returns in just the right places... run this in the "Results in Text" mode...
SELECT Column_Name + ','
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = 'puttablenamehere'
ORDER BY Ordinal_Position
Just don't forget to delete the last comma
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Declare @ColumnList nvarchar(4000)
SELECT @ColumnList = ISNULL(@ColumnList + ',', '') + Column_Name
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = 'puttablenamehere'
ORDER BY Ordinal_Position
PRINT @ColumnList
Don't worry about last comma
_____________
Code for TallyGenerator -
Thanks guys! This is all useful stuff!
Alan
-
Declare @ColumnList nvarchar(4000)
SELECT @ColumnList = ISNULL(@ColumnList + ',', '') + Column_Name+char(13)
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = 'tablename'
ORDER BY Ordinal_Position
print @ColumnList
Add a char(13) if you want a carriage return after
each column name.
-
I want to add that your code is fail-safe and always runs without error, irrespective of the source table structure changes.
-
That's very true.
Alan
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply