Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
misterraj
Yak Posting Veteran
94 Posts |
 Posted - 2007-11-22 : 04:23:43
|
| HI all,I have a table (eg) having two columns col1 and col2 having 100 rows/values.i want to write a select statement, which when executed would result in Insert statements for the program, here in the case as tehre are 100 rows it should give 100 insert statements. select 'Insert into table (' ??????????any help would be appreciated. [maroon](Note one column is of type char and another is ntext)[/maroon] |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-22 : 04:42:37
|
| Can you convert the ntext to a varchar(8000)? |
 |
|
|
misterraj
Yak Posting Veteran
94 Posts |
Posted - 2007-11-22 : 04:44:02
|
| yes i can convert |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-22 : 04:47:11
|
| [code]select 'Insert into <TableName> (Col1,Col2) Values (' + Col1 + ',' + convert(varchar(8000),Col2) + ')'From Table[/code] |
|
|
|
misterraj
Yak Posting Veteran
94 Posts |
Posted - 2007-11-22 : 05:10:56
|
| great that worked but i had problems where i had Null values in columns the Insert statement was not generated. if i didnot have NUll values in the rows, the insert stmt was generated properly, but if the rows had one or two null value columns the Insert stmt was not generaed :-( |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-11-22 : 05:13:38
|
| Use ISNULL() or COALESCE() function to handle Nulls.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
misterraj
Yak Posting Veteran
94 Posts |
Posted - 2007-11-22 : 05:38:41
|
| i tried using like this : isnull(responsibility_id,'') but still not getting the output |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-22 : 06:13:34
|
| As long as you have values in one of the fields for each row, the output should exist? Can you please post your select statement as it is now? |
|
|
|
|
|
|
nText + Char