loop through informationSchema.Columns table

Well, I came up with one loop structure that isn't dying,
but my table is not getting updated. Here is what I
tried - any suggestions appreciated if anyone spots what
is wrong: can I say this?
Declare @.fldName varchar(20)
Select @.fldName = 'fld1'
Update tbl1 Set @.fldName = 'test'
--here is my effort
DECLARE @.iNextRowId int,
@.iCurrentRowId int,
@.iLoopControl int,
@.fldName nvarchar(255)
-- Initialize variables!
SELECT @.iLoopControl = 1
SELECT @.iNextRowId = MIN(ordinal_position)
FROM information_schema.columns
where table_name = 'tbl1'
-- Make sure the table has data.
IF ISNULL(@.iNextRowId,0) = 0
BEGIN
SELECT 'No data in found in table!'
END
-- Retrieve the first row
SELECT @.iCurrentRowId = ordinal_position,
@.fldName = column_Name
FROM information_schema.columns
where table_name = 'tbl1' AND Ordinal_Position =
@.iNextRowId
print @.fldName --OK to here
-- start the main processing loop.
WHILE @.iLoopControl = 1
BEGIN
-- perform detailed row-by-row processing.
Update tbl1 Set @.fldName = ltrim(rtrim(@.fldName))
-- Reset looping variables.
SELECT @.iNextRowId = NULL
-- get the next iRowId
SELECT @.iNextRowId = MIN(Ordinal_Position)
From information_schema.columns
where table_name = 'tbl1' AND Ordinal_Position >
@.iCurrentRowId
-- did we get a valid next row id?
IF ISNULL(@.iNextRowId,0) = 0
BEGIN
BREAK
END
-- get the next row.
SELECT @.iCurrentRowId = Ordinal_Position,
@.fldName = Column_Name
From information_schema.columns
where table_name = 'tbl1' AND Ordinal_Position >
@.iNextRowId
END

>--Original Message--
>Hello,
>I have to import a dataset from a mainframe to Sql Server
>which contains about 100 fields, and about 90 of the
>fields have leading and trailing spaces in the data.
>After I import the data to a table in Sql Server I need
to
>update this table to remove the leading/trailing spaces.
>But rather that writing a query like
>Update tbl1 Set fld1 = ltrim(rtrim(fld1)), fld2 = ...
>I was hoping to loop through the
>Information_Schema.Columns table and use a loop to loop
>through all the fields in tbl1 to update each field. Is
>this a good approach? If so, what kind of loop do I want
>to use? How to set that up?
>Thanks,
>Ron
>.
>--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You can't use a variable for a column/table name. This:
Update tbl1 Set @.fldName = ltrim(rtrim(@.fldName))
won't work. 1) because the SET clause needs a column name, not a
variable. 2) because the LTrim(RTrim(@.fldName)) will trim the actual
column name, not the value of the column.
You should use a DTS package to Transform (the T in DTS) the data to an
acceptable format while importing it. Read the Books on Line (BOL)
articles on the Data Transformation Services (DTS) for more info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQkjsCYechKqOuFEgEQKC6ACgtgkTdhZXm3bp
pTmsBUjBhP3xFKsAoLUU
hvG1jAtvQgKL3ulV49gzZQCG
=IF7t
--END PGP SIGNATURE--
Ron wrote:
> Well, I came up with one loop structure that isn't dying,
> but my table is not getting updated. Here is what I
> tried - any suggestions appreciated if anyone spots what
> is wrong: can I say this?
> Declare @.fldName varchar(20)
> Select @.fldName = 'fld1'
> Update tbl1 Set @.fldName = 'test'
> --here is my effort
> DECLARE @.iNextRowId int,
> @.iCurrentRowId int,
> @.iLoopControl int,
> @.fldName nvarchar(255)
> -- Initialize variables!
> SELECT @.iLoopControl = 1
> SELECT @.iNextRowId = MIN(ordinal_position)
> FROM information_schema.columns
> where table_name = 'tbl1'
> -- Make sure the table has data.
> IF ISNULL(@.iNextRowId,0) = 0
> BEGIN
> SELECT 'No data in found in table!'
> END
> -- Retrieve the first row
> SELECT @.iCurrentRowId = ordinal_position,
> @.fldName = column_Name
> FROM information_schema.columns
> where table_name = 'tbl1' AND Ordinal_Position =
> @.iNextRowId
> print @.fldName --OK to here
> -- start the main processing loop.
> WHILE @.iLoopControl = 1
> BEGIN
> -- perform detailed row-by-row processing.
> Update tbl1 Set @.fldName = ltrim(rtrim(@.fldName))
> -- Reset looping variables.
> SELECT @.iNextRowId = NULL
> -- get the next iRowId
> SELECT @.iNextRowId = MIN(Ordinal_Position)
> From information_schema.columns
> where table_name = 'tbl1' AND Ordinal_Position >
> @.iCurrentRowId
> -- did we get a valid next row id?
> IF ISNULL(@.iNextRowId,0) = 0
> BEGIN
> BREAK
> END
> -- get the next row.
> SELECT @.iCurrentRowId = Ordinal_Position,
> @.fldName = Column_Name
> From information_schema.columns
> where table_name = 'tbl1' AND Ordinal_Position >
> @.iNextRowId
> END
>
>
> to
>

looking to tune query...

If anyone is able to provide advice for tuning the below query in sql
server, it is much appreciated. In addition any index suggestions are
also appreciated as I have access to the tables. Thank you.

select a.id, isnull(b.advisement_satisfaction_yes, 0) as
advisement_satisfaction_yes,
isnull(c.advisement_satisfaction_no, 0) as advisement_satisfaction_no,
case
when isnull(b.advisement_satisfaction_yes, 0) >
isnull(c.advisement_satisfaction_no, 0) then 'YES'
when isnull(b.advisement_satisfaction_yes, 0) <
isnull(c.advisement_satisfaction_no, 0) then 'NO'
when isnull(b.advisement_satisfaction_yes, 0) =
isnull(c.advisement_satisfaction_no, 0) then 'TIE'
end as Satisfied_With_Advisement

from a
left Join

(select id, count(answer_text) as Advisement_Satisfaction_yes from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'yes'
GROUP BY id) b

on a.id = b.id

left join

(select id, count(answer_text) as Advisement_Satisfaction_NO from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'NO'
GROUP BY id) c

on a.id = b.id

where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:

SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x

Razvan|||AMAZING...THANK YOU SO MUCH!

Razvan Socol wrote:

Quote:

Originally Posted by

The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:
>
SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x
>
Razvan