SQLEXEC (INSERT - SQL )

[Joriz]

I have like 40 fields in one table of vfp and i have created a
identical table in sql server. I wanna create a procedure that do
incremental inserts to sql server. When i do sqlexec("dsn", insert
into customer (f1..) values (m.f1..), it takes a lot of coding to
enumerate all fields. Is there a fastest way to do this? something
like sqlexec("dsn", insert into customer from memvar) ..

please help!

TIA

[Fred Taylor]

Sorry, no. With SQLEXEC, you're limited to the backend syntax. If you're
inserting all fields, you may be able to drop the field list and just
provide the values, as long as you provide them in the proper order.

INSERT INTO customer VALUES ("val1","val2",5,...,"val40")

Fred
Microsoft Visual FoxPro MVP

"Joriz" <goo...@joriz.is-a-geek.net> wrote in message
news:7d49ea6b.04032...@posting.google.com...

[toylet]

you may want to code 2 functions: quotewithcomma() and quote(), which
format the input values based on data type.

> into customer (f1..) values (m.f1..), it takes a lot of coding to
> enumerate all fields. Is there a fastest way to do this? something
> like sqlexec("dsn", insert into customer from memvar) ..
>
> please help!
>
> TIA

--
.~. Might, Courage, Vision. In Linux We Trust.
/ v \ http://www.linux-sxs.org
/( _ )\ Linux 2.4.22-xfs
^ ^ 9:38pm up 2 days 13:42 load average: 1.00 1.00 1.00

[Samir R. Ibrahim]

For the lot of coding you are talking about, go to query analyzer , right
click on the table you want to do insert for it, select "script object to
new window as ---> Insert"
this will create all the insert into structure with the field name, it will
save you a little time.

Samir R. Ibrahim

"Joriz" <goo...@joriz.is-a-geek.net> wrote in message
news:7d49ea6b.04032...@posting.google.com...

[Anders Altberg]

USE myTable ALIAS table
x=""
y=""
FOR i = 1 to FCOUNT('table')
y = y+FIELD(i,'table')+" "
x = x+"?"+FIELD(i,'table')+" "
NEXT

x = CHRTRAN(TRIM(x)," ",",")
y= CHRTRAN(TRIM(y)," ",",")

TEXT TO sql NoSHOW TextMerge
INSERT INTO Table (<<y>>) VALUES (<<x>>)
ENDTEXT

* ? sql
SCAN
SCATTER MEMVAR
SQLEXEC( handle, sql)
ENDSCAN

Ok for smallish jobs. For bigger tables, appending from a textfile is
faster:
#Define TAB '\t'
#Define LF '\n'
?SQLEXEC(handle,[BULK INSERT SQLServerTable FROM "myTable.TXT" WITH
;(DATAFILETYPE='char',FIELDTERMINATOR=TAB,ROWTERMINATOR=LF)])

-Anders

"Joriz" <goo...@joriz.is-a-geek.net> wrote in message
news:7d49ea6b.04032...@posting.google.com...

[news.comcast.giganews.com]

I believe the easites way to do this is using dts package in sql server..you
may try insert from openrowset.

~MK

"Joriz" <goo...@joriz.is-a-geek.net> wrote in message
news:7d49ea6b.04032...@posting.google.com...

[Joriz]

Thanks guys. I went for the one that anders suggested. actually i
thought abt it right after i posted the message..it took awhile for
the message to get posted.. Anders code was almost exactly the same as
i wrote it. only without the textmerge...hmmmm...but come to think of
it, textmerge is even a better approach...

and actually its not only 40 fields..its about 138 fields the most. I
had a few bumps putting them all in one string. Its just way too long.
I just created a table with memo fields that will contain my entire
insert statement.

thanks again for all your suggestions.

"news.comcast.giganews.com" <kjh...@jhg.las> wrote in message news:<zqmdnRtgyNs...@comcast.com>...