March 16, 2010 at 8:13 am
Hi,
Can anybody help me.
My name is Mathew, im in a big trouble to execute a query.
My requirement is that, i want to insert recordset from SQL server to my local computer's mdb file without any loop inside front-end application.
This technique im using for reporting purpose.
:discuss:
The main highlighted part is that, any loop should not be added from front end application (like do,for,for each...)
I will execute a query which will looks like
Insert into localtable1 (f1,f2) select f1,f2 from sqltable
If anybody could help me, that would be highly appreciate.
Thanks !
March 16, 2010 at 1:05 pm
Have you tried to execute that query from within SQL?
There is no need to loop thru anything. SQL Server is a "set based" dataase. It works on sets of data. Not like Oracle or MySQL. They work on cursors.
Run the query and see what happens.
you can also pump the data to a text file and then pump it into your other database. No front-end needed for either really
Andrew SQLDBA
March 16, 2010 at 2:44 pm
yes i tried in sql server query is executing fine, but i want that recordset in mdb file which is located in local computer (clients).
Query will execute if mdb file is located in server.
March 16, 2010 at 3:21 pm
Only option which allows you to read recordsets from files with no looping is BULK INSERT (bcp).
But to use it you need to expose the file location to the SQL Server instance.
What you are trying to do (I guess) is to create "DTS-like" command to read files on local machine and send it to remote server.
DTS use ODBC drivers to access both text files and SQL Server tables. ODBC drivers use loops internally.
So, there is no point really in avoiding loops in this kind of tasks - they are inbuilt in the drivers you're gonna use.
_____________
Code for TallyGenerator
March 16, 2010 at 9:53 pm
this is the actual query which i need to execute...
this query will read reacordset from the sql server.
SELECT FM.CLSCOD,ST.STDNAM, ST.ADMNUM, FH.HEDNAM, FD.HEDAMT, FD.CONAMT, FD.BALAMT, FD.MONCOD, ST.STSRID, FD.PYMTDT, FD.PYTDATDT FROM FEDTLTBL FD INNER JOIN FEMSTTBL FM ON FD.MSTNUM = FM.MSTNUM INNER JOIN FEHEDTBL FH ON FD.HEDCOD = FH.HEDCOD AND FM.ACASES = FH.ACASES INNER JOIN STUDNTBL ST ON FM.STSRID = ST.STSRID GROUP BY ST.STDNAM, FM.CLSCOD, ST.ADMNUM, FH.HEDNAM, FD.HEDAMT, FM.ACASES, FD.CONAMT, FD.BALAMT, FD.MONCOD, ST.STSRID, FM.SCHCOD,FD.PYMTDT, FD.PYTDATDT HAVING(FM.CLSCOD = 'III') AND (FM.ACASES = 20102011) AND (FM.SCHCOD = 'SIA') AND (FD.PYMTDT >= 20100301 AND FD.PYMTDT <= 20100317) ORDER BY FD.PYMTDT, ST.STDNAM
and i need this recordset has to insert into a local mdb file
and i tried by useing OpenDatasource method, but it execute only in where the server resides.
This is the way how i did it.
INSERT INTO OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=F:\datamanger.mdb;User ID=Admin;Password=;')...DLYFEREP (CLSCOD,STDNAM,ADMNUM,HEDNAM,HEDAMT,CONAMT,BALAMT,MONCOD,STSRID,PYMTDT,PYMTDATDT)SELECT FM.CLSCOD,ST.STDNAM, ST.ADMNUM, FH.HEDNAM, FD.HEDAMT, FD.CONAMT, FD.BALAMT, FD.MONCOD, ST.STSRID, FD.PYMTDT, FD.PYTDATDT FROM FEDTLTBL FD INNER JOIN FEMSTTBL FM ON FD.MSTNUM = FM.MSTNUM INNER JOIN FEHEDTBL FH ON FD.HEDCOD = FH.HEDCOD AND FM.ACASES = FH.ACASES INNER JOIN STUDNTBL ST ON FM.STSRID = ST.STSRID GROUP BY ST.STDNAM, FM.CLSCOD, ST.ADMNUM, FH.HEDNAM, FD.HEDAMT, FM.ACASES, FD.CONAMT, FD.BALAMT, FD.MONCOD, ST.STSRID, FM.SCHCOD,FD.PYMTDT, FD.PYTDATDT HAVING(FM.CLSCOD = 'III') AND (FM.ACASES = 20102011) AND (FM.SCHCOD = 'SIA') AND (FD.PYMTDT >= 20100301 AND FD.PYMTDT <= 20100317) ORDER BY FD.PYMTDT, ST.STDNAM
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply