Merge two cursors

Bhashi · Dec 6, 2022

Dear All,

I have two cursors like the ones below.tt1 and tt2

Code:

[b]Index	Col01	Col02 ... col164[/b]
1	Data01	Data03  .........
2	Data02	Data04  .........

Code:

[b]Index	Col165	Col166 ... col324[/b]
1	Data01	Data03   .........
2	Data02	Data04   .........

I want to insert them into the one SQL table(Items) using index.
Item Table

Code:

Id	Col03	Col04	……….	……….	……….	Col324
1	Data01	Data03	……..	……..	……..	…….
2	Data02	Data04	……..	……..	……..	……..

Could someone please guide me on how to do this?
thank you

Mike Lewis · Dec 6, 2022

In theory, you could do this with a simple join:

Code:

SELECT tt1.*, tt2.* FROM tt1 JOIN tt2 ON tt1.ID = tt2.ID INTO CURSOR ttNew

I say "in theory" because you are trying to create a cursor with 324 fields, but a VFP table has a limit of 255 fields.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Chris Miller · Dec 6, 2022

With SQL table you mean an MSSQL Server table?

Well, this would be one way to do it:

Code:

Use tt2.dbf in 0 order index 
Select 0
Use tt1.dbf
Set Relation to index into tt2
* maybe here set a breakpoint and browse to see you get the data row alignment correct.

h = SQLStringConnect(...) && or however you connect to SQL Server

Local lcSQL
[highlight #FCE94F]Text to lcSQL NoShow
Insert Into dbo.Item (id, col03,col04,...,...,col324) values (?tt1.col3, =tt1.col4,...,...,?tt2.col324)
EndText
[/highlight]

Select tt1
SQLPrepare(h,lcSQL)
SCAN
   SQLEXEC(h) 
ENDSCAN

You better use multiple lines for this text..endtext passage, and overall it would be best you program a loop from 3 to 324 to generate the names instead of writing all that by hand.

Chriss

Mike Lewis · Dec 6, 2022

But, whether DBF or a back-end database, I have to question the wisdom of any table with 324 columns.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Chris Miller · Dec 6, 2022

That's true. The need for lengthy SQL to do this points out how SQL incentivises to design "normal" sized tables to also have "normal" sized SQL queries.

We don't know how this table is designed, if it exists, the row size seems to not violate SQL Server limitations, but capabilities of SQL servers are higher in almost any aspect as they need to be.

Chriss

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Merge two cursors

Bhashi

Programmer

Mike Lewis

Programmer

Chris Miller

Programmer

Mike Lewis

Programmer

Chris Miller

Programmer

Similar threads

Part and Inventory Search

Sponsor