| Author |
Topic |
|
RichardSteele
Posting Yak Master
160 Posts |
 Posted - 2003-08-07 : 13:17:40
|
| I have two tables one with a varchar(8000) column and the other with an ntext column. I want to update the varchar column in the first table with the data in the second. How do I convert the ntext so that it will update the table with the varchar data? Thanks in advance. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-07 : 13:20:27
|
| update tbl2set varcharfld = convert(varchar(8000),tbl1.ntxtfld)from tbl2 join tbl1on tbl1.flc = tbl2.fld==========================================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. |
 |
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2003-08-07 : 13:32:02
|
| How would I code this if one of the fields in UpdateProducts is this ntext field that has to be converted before inserting into Inventory? Many Thanks!INSERT INTO Inventory SELECT * FROM UpdateProducts |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-07 : 13:34:15
|
| INSERT INTO Inventory (fld1, fld2, varcharfld ) SELECT fld1, fld2, convert(varchar(8000),ntxtfld)FROM UpdateProducts==========================================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. |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2003-08-07 : 13:37:02
|
| Ok, so I have to list all columns in the select in order to do this? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-07 : 13:38:08
|
| Yes. And you should do that in your code always anyways as it is a good programming practice. SELECT * should never be used except ad-hoc in Query Analyzer.Tara |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2003-08-07 : 13:39:03
|
| Right! Thanks. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-07 : 13:40:47
|
| You canINSERT INTO Inventory SELECT fld1, fld2, convert(varchar(8000),ntxtfld)FROM UpdateProductsBut you shouldn't==========================================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. |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-09 : 08:10:55
|
| Just wondering.... Do any of your records contain more than 8000 characters in this ntext field ?Kevin |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-09 : 11:55:24
|
quote:
Originally posted by tduggan
Yes. And you should do that in your code always anyways as it is a good programming practice. SELECT * should never be used except ad-hoc in Query Analyzer.Tara
Why is SELECT * bad if you want to copy all the columns ?Kevin the Newbie |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-09 : 12:26:57
|
Because if you have a particular structure or number of columns you need to return in an application, it will return any newly added columns when you may not want them. Worse is if your application is expecting a column that was dropped from the table, you'll get runtime errors that are usually fatal. The point is to always be explicit about the columns you want from a table or query.And to be honest, mostly it's people complaining about lazy coding/typing habits.  I know I use SELECT * a lot, but I definitely keep it out of production code. Best way to lose the habit is never to get into it. |
|
|
|
|
Converting nText to varchar