| Author |
Topic |
|
M.E.
Aged Yak Warrior
539 Posts |
 Posted - 2002-11-04 : 13:01:52
|
| I run a few scripts on a daily basis that I don't think much of. Simple things that shorten the amount of typing I have to do. like select name + ',' + char(13) from syscolumns where id = object_id('TableName')order by colidselects all the coulmns in a table and puts a comma between them. Since I write a large number of insert statments, that little piece of code saves me quite a bit of time. Is this worth posting on the scripts board? most things there seem a little more fancy then that (and quite a bit more complicated/harder to write). These are just little scripts that make my life as a developer a bit easier.-----------------------SQL isn't just a hobby, It's an addictionEdited by - m.e. on 11/04/2002 13:02:16 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-04 : 13:07:08
|
| I suppose, but it might be better for people to figure them out on their own. And frankly, sometimes I think people should be forced to put a little more elbow grease into writing SQL; it makes them stop and think. Saving time unfortunately can lead to saving understanding as well. I'd hate to think that this cool little time-saver script leads to someone unable to write a full-length INSERT statement. |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-11-04 : 13:16:22
|
| Heh, I don't see how copying and pasting the column list will make you forget how to write an insert statement. You still have to write the insert statement, this just makes it so you don't have to type out all 500 column names from a table your inserting too. You just run this and copy/paste. If anything it should promote including column names in your insert statements rather then leaving it blankAlthough you are right... maybe its better off leaving a little script like this up to them to discover how to write. Learning about the syscolumns table and others along those lines (which I'm still learning about) was a major step in my SQL learning curve-----------------------SQL isn't just a hobby, It's an addictionEdited by - m.e. on 11/04/2002 13:18:06 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-04 : 13:47:07
|
| I do itselect '<tab><tab>' + name + ' ,' from syscolumns where id = object_id('TableName')order by colid(dunno how to get tabs to come out on the site).Or when I forget about the new fangled functionwhere id = (select id from sysobjects where name = 'tablename')==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-04 : 14:19:15
|
quote:
Heh, I don't see how copying and pasting the column list will make you forget how to write an insert statement.
Well, that's not exactly what I wanted to say. I'm thinking more along the lines of the fact that if the machine does the work, the user isn't forced to learn it as deeply, and doesn't get enough exercise doing it. Just like calculators have reduced the need to maintain simple arithmetic skills; word processors have made us lousy spellers, writers, typists, and grammarians. Probably the most SQL Server-specific example would be all of the Maintenance Plan questions people post...none of them know that the BACKUP and DBCC commands exist. When we suggest that they write their own maintenance plan they react like we're describing an alien autopsy or faster-than-light travel.  The same applies to DTS: god forbid we ever answer someone with "why don't you just modify the package in the DTS designer?"; they've never ever done that, and have always used the Import/Export Wizard. And don't get me started on data entry using Enterprise Manager...quote:
You still have to write the insert statement, this just makes it so you don't have to type out all 500 column names from a table your inserting too. You just run this and copy/paste. If anything it should promote including column names in your insert statements rather then leaving it blank
OTOH having to enter 500 column names could make you ask why you have 500 columns in the table, and if they really are necessary, why all 500 need to have specific values added (SOME of them have to have default values defined, right?) I know 500 is an exaggeration (God, I hope it is!), but by the same token I can't see how it's a bad thing to be familiar enough with a table structure that you can pull the column list off the top of your head. And before anyone says that that is totally unreasonable, it's not: I do it all the time, at least I know enough about the table to know which columns to include or not. I could never work on a database project where I couldn't take or have the time to learn how it's designed; how exactly am I supposed to work with it? Blindfolded?This is just one of my usual curmudgeonly opinions, feel free to ignore it.  |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-11-04 : 15:28:55
|
| I, on the other hand, am excited about any script that will save me from typing. Even one character saved is a benefit. I say post 'em!===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-11-06 : 12:15:02
|
quote:
I know 500 is an exaggeration (God, I hope it is!),
Actually, its somewhat close. I've memorized all table keys and a few important columns, but theres just too many for me to remember all of them (for all 72 tables in this data model and the 112 reference tables that go along with). And yes, one table has something silly 450 columns plus some. Its geological data on wells, I didn't get to design the database, so I just go along with it... Someone who's been designing databases for longer than I have been alive seems to think thats the way it should be (I told him only in an IDMS world ). The same table I have to run mass field updates on it. Kinda aupdate table set all coloumns = different table. all columnswhere id = 54To get a list of all columns along with the differenttable. in front of them, something simple like :select (name +'=t1.' +name',' + char(13)) from syscolumns where id = object_id('tablename')order by colid Works so nicely. Then I just remove the could column I don't want.-----------------------SQL isn't just a hobby, It's an addictionEdited by - m.e. on 11/06/2002 12:17:26 |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-11-06 : 17:19:20
|
quote:
Its geological data on wells
Do you get to do some data mining ?Damian |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-11-06 : 18:41:03
|
| or only data extraction ?----Nancy Davolio: Best looking chick at Northwind 1992-2000 |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-11-06 : 22:18:48
|
| I had a statement involving drill-down, but it seems to be buried at the moment.----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-11-06 : 23:04:09
|
| I can dig itDamian |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-11-07 : 12:23:42
|
Oh god shoot me now  You won't believe how often I've been asked thatIt's extraction only... mining for oil Merkin? common Heh, besides... it's government data, so most of it was randomly created when the database was first made-----------------------SQL isn't just a hobby, It's an addictionEdited by - m.e. on 11/07/2002 12:23:58Edited by - m.e. on 11/07/2002 15:42:37 |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-11-07 : 18:18:23
|
| Lavos did your statement have an exclusive ore ?----Nancy Davolio: Best looking chick at Northwind 1992-2000 |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-12-01 : 20:48:01
|
| well, well, well - I can't believe I've missed this hole post...--I hope that when I die someone will say of me "That guy sure owed me a lot of money"Edited by - rrb on 12/01/2002 22:13:09 |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-12-01 : 22:02:56
|
| Just when I thought this thread was buried.....Damian |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-12-01 : 22:17:35
|
quote:
it's government data, most of it was randomly created when the database was first made
can't argue with that logic--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
Scripts worth posting?