What is best alternate for cursor in T-SQL?

Question

I have a design concern which I need to implement without using cursor.

There is source table 'A' which will have all column in varchar data type. I want to iterate over them and convert each column to destination table data type and if conversion/parsing fails, I need to log that row in extra error table.

Any suggestions to go ahead will be helpful.

Also, please edit your question to include sample data as DDL + DML and desired results. — Zohar Peled, Mar 21 '17 at 11:38
Thanks all for your response. Still my main concern is to avoid cursor to iterate over the table as I have to record error for individual row. — Tukaram Bhosale, Mar 22 '17 at 13:56
Does this answer your question? [Is there a way to loop through a table variable in TSQL without using a cursor?](https://stackoverflow.com/questions/61967/is-there-a-way-to-loop-through-a-table-variable-in-tsql-without-using-a-cursor) — Michael Freidgeim, Dec 11 '19 at 07:13

score 1 · Answer 1 · answered Mar 21 '17 at 11:39

In SQL Server, you would use try_convert():

insert into t2 ( . . . )
    select . . .
    from (select try_convert(?, col1) as col1,
                 try_convert(?, col1) as col2,                
          from staging_t
         ) t
    where col1 is not null and col2 is not null and . . .;

Then run a second query to get the rows where the value is NULL.

If NULL is a permitted value in the staging column, then this is a bit more complex:

insert into t2 ( . . . )
    select new_col1, new_col2, . . .
    from (select try_convert(?, col1) as new_col1, col1,
                 try_convert(?, col1) as new_col2, col2,              
          from staging_t
         ) t
    where (new_col1 is not null or col1 is null) and
          (new_col2 is not null or col2 is null) and
          . . .;

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

In Sql Server 2012 and up: each of these will return null when the conversion fails instead of an error.

Example of all varcharcol that would fail conversion to int:

 select id, varcharcol
 from a
 where try_convert(int,varcharcol) is null

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 21 '17 at 11:39

SqlZim

37,248
6
41
59

score 0 · Answer 3 · answered Mar 21 '17 at 11:46

Use Above Query Using User Define Table Type

Create Type Table - Procedure - User Define Table Type

@UserDefineTypeTable UserDefineTypeTable readonly

insert into table1 col1, col2, col3

(select

type.col1, type.col2, type,col3

from @UserDefineTypeTable as type)

What is best alternate for cursor in T-SQL?

3 Answers3