Merge two cursors

[Bhashi]

Dear All,

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

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

[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

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

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

 

[Mike Lewis]

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

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]

With SQL table you mean an MSSQL Server table?

Well, this would be one way to do it:

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]

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]

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