Cursor returns Multiple Rows & Columns

Transact-SQL
1

My SQL is limited and I have inherited the schema so please go easy!

Working%20ID%20Schema
Working ID Schema.png1371×704 26.7 KB

The upper tables are populated, the lower section is to be completed.

The customer wants a simple reference, C_SCAFFOLD_DOMAIN_COMBO, for every C_SEQUENCE_RESULT of the same TYPE having the same C_PROTEIN_REGIONs (only ever two) of the same REGION_NAME.

My intention is to read each C_SEQUENCE_RESULT getting the (2) C_PROTEIN_REGION.SEQUENCEs with their REGION_NAME using a cursor.

I can then check if they are referenced by a C_SCAFFOLD_DOMAIN_COMBO already.

If so, then simply add the correct C_SCAFFOLD_DOMAIN_NAME FK to C_SEQUENCE_RESULT.

If not, create a new C_SCAFFOLD_DOMAIN_NAME entry with the appropriate C_PROTEIN_REGION refs and add the FK to C_SEQUENCE_RESULT.

My SQL query returns two results for each C_SEQUENCE_RESULT e.g.

REGION_NAME SEQUENCE
Loop 1 ABCDEFG
Loop 2 HIJKLMN

Running as a simple query and can INSERT INTO a temp table and get the values. As a cursor I don’t seem to be able. How can I read the data returned above using a cursor?

Thanks

2

Doesn't sound like you need a cursor. You can run a simple select. But, without ddl and sample data, it is difficult to provide a solution

3

Thanks for the response. DDL?

Unfortunately my data is confidential, my company's IP in fact! Mocking something up would be a painful task.

I have swerved the problem by 'splitting' the SQL cursor query over the two (and only two) REGION_NAMEs in the data. I run the cursor using one value then query using the second value.

I am sure that someone with suitable experience could solve this 'properly' using datasets- it's just beyond my ken!