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
>