Get data from three table and insert it in new table  
Author Message
alisonh





PostPosted: Fri Nov 10 05:12:00 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table

Hi,

I am using ASP with SQL Server200. I am getting data from three tables
then showing result. Its performance is not good. I want to use store
procedure for this. IWhat I am doing in ASP

SQL = "Select Custid, BranchId, purchord, PCode, description, " & _
deliverydate, itemgroup, itemgroupdesc, category, deladdrno " & _
"From table1 Where invoicedate BETWEEN "&DtAddFrom&" AND
"&DtAddTo&" ORDER BY invoicedate"

Set rs = Server.CreateObject("ADODB.RecordSet")
rs.open SQL, conn, 3,1,1
totalRecord = rs.RecordCount

For i = 1 to totalRecord
LocSQL = "SELECT branchName FROM table2 WHERE branchNumber="&
rs.fields("Custid") &" and branchContactNo ='"& rs.fields("deladdrno")
&"' and branchParent = "& companyID &""
Set LocRs = LocConn.Execute(LocSQL)

ItemSQL = "SELECT productCustomerRef FROM table3 WHERE
productMacfRef = '"& rs.fields ("pcode") &"' AND productCompanyID = "&
companyID &""
Set ItemRs = LocConn.Execute(ItemSQL)


rs.MoveNext 'Move to the next record
Next

In SQL Server I want in store procedure select data from above three
tables and insert it in new table and access it in ASP.
What I did in SQL Server2000

CREATE PROCEDURE sp_einvoice
@comp int,
@invoicedate1 varchar(8),
@invoicedate2 varchar(8)
AS
SELECT productcode, description, quantity, cost, unitmeasure,
cctimestamp, purchord, invoicedate
FROM table1


ORDER BY invoicedate
GO

It works fine and get required data but to get date from table2 and
table3 I don't know how to loop and use table1 data to get it.
am writing first time store procedure please help me.

Thanks

SQL Server159  
 
 
vt





PostPosted: Fri Nov 10 05:12:00 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table
Developr,

If you don't bother about performance You could use cursor, read BOL.



vt







> Hi,
>
> I am using ASP with SQL Server200. I am getting data from three tables
> then showing result. Its performance is not good. I want to use store
> procedure for this. IWhat I am doing in ASP
>
> SQL = "Select Custid, BranchId, purchord, PCode, description, " & _
> deliverydate, itemgroup, itemgroupdesc, category, deladdrno " & _
> "From table1 Where invoicedate BETWEEN "&DtAddFrom&" AND
> "&DtAddTo&" ORDER BY invoicedate"
>
> Set rs = Server.CreateObject("ADODB.RecordSet")
> rs.open SQL, conn, 3,1,1
> totalRecord = rs.RecordCount
>
> For i = 1 to totalRecord
> LocSQL = "SELECT branchName FROM table2 WHERE branchNumber="&
> rs.fields("Custid") &" and branchContactNo ='"& rs.fields("deladdrno")
> &"' and branchParent = "& companyID &""
> Set LocRs = LocConn.Execute(LocSQL)
>
> ItemSQL = "SELECT productCustomerRef FROM table3 WHERE
> productMacfRef = '"& rs.fields ("pcode") &"' AND productCompanyID = "&
> companyID &""
> Set ItemRs = LocConn.Execute(ItemSQL)
>
>
> rs.MoveNext 'Move to the next record
> Next
>
> In SQL Server I want in store procedure select data from above three
> tables and insert it in new table and access it in ASP.
> What I did in SQL Server2000
>
> CREATE PROCEDURE sp_einvoice



> AS
> SELECT productcode, description, quantity, cost, unitmeasure,
> cctimestamp, purchord, invoicedate
> FROM table1


> ORDER BY invoicedate
> GO
>
> It works fine and get required data but to get date from table2 and
> table3 I don't know how to loop and use table1 data to get it.
> am writing first time store procedure please help me.
>
> Thanks
>


 
 
Developer





PostPosted: Fri Nov 10 06:47:44 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table Only for performance i am using store procedure. I tried it like
CREATE PROCEDURE sp_einvoice
@comp int,
@invoicedate1 varchar(8),
@invoicedate2 varchar(8)
AS
BEGIN
INSERT INTO newtable( cctimestamp, custid, custbranchid,
aswdescription, productcode, quantity, cost, unitmeasure, purchord,
invnumber, custitem,
itemfamily, orderdate, deliverydate, itemgroup,
itemgroupdesc, category, deladdrno)
SELECT cctimestamp, custid, custbranchid, aswdescription,
productcode, quantity, cost, unitmeasure, purchord, invnumber,
custitem,
itemfamily, orderdate, deliverydate, itemgroup, itemgroupdesc,
category, deladdrno



but i don't know how to get values from other two tables and insert
them in new table.
I am totally new in SQL Server store procedure, please please help me
like sample code.
Thanks
Developer


> Developr,
>
> If you don't bother about performance You could use cursor, read BOL.
>
>
>
> vt
>
>
>
>
>


> > Hi,
> >
> > I am using ASP with SQL Server200. I am getting data from three tables
> > then showing result. Its performance is not good. I want to use store
> > procedure for this. IWhat I am doing in ASP
> >
> > SQL = "Select Custid, BranchId, purchord, PCode, description, " & _
> > deliverydate, itemgroup, itemgroupdesc, category, deladdrno " & _
> > "From table1 Where invoicedate BETWEEN "&DtAddFrom&" AND
> > "&DtAddTo&" ORDER BY invoicedate"
> >
> > Set rs = Server.CreateObject("ADODB.RecordSet")
> > rs.open SQL, conn, 3,1,1
> > totalRecord = rs.RecordCount
> >
> > For i = 1 to totalRecord
> > LocSQL = "SELECT branchName FROM table2 WHERE branchNumber="&
> > rs.fields("Custid") &" and branchContactNo ='"& rs.fields("deladdrno")
> > &"' and branchParent = "& companyID &""
> > Set LocRs = LocConn.Execute(LocSQL)
> >
> > ItemSQL = "SELECT productCustomerRef FROM table3 WHERE
> > productMacfRef = '"& rs.fields ("pcode") &"' AND productCompanyID = "&
> > companyID &""
> > Set ItemRs = LocConn.Execute(ItemSQL)
> >
> >
> > rs.MoveNext 'Move to the next record
> > Next
> >
> > In SQL Server I want in store procedure select data from above three
> > tables and insert it in new table and access it in ASP.
> > What I did in SQL Server2000
> >
> > CREATE PROCEDURE sp_einvoice



> > AS
> > SELECT productcode, description, quantity, cost, unitmeasure,
> > cctimestamp, purchord, invoicedate
> > FROM table1


> > ORDER BY invoicedate
> > GO
> >
> > It works fine and get required data but to get date from table2 and
> > table3 I don't know how to loop and use table1 data to get it.
> > am writing first time store procedure please help me.
> >
> > Thanks
> >

 
 
vt





PostPosted: Fri Nov 10 07:05:39 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table post table1, table2 and table3 structure
vt




> Only for performance i am using store procedure. I tried it like
> CREATE PROCEDURE sp_einvoice



> AS
> BEGIN
> INSERT INTO newtable( cctimestamp, custid, custbranchid,
> aswdescription, productcode, quantity, cost, unitmeasure, purchord,
> invnumber, custitem,
> itemfamily, orderdate, deliverydate, itemgroup,
> itemgroupdesc, category, deladdrno)
> SELECT cctimestamp, custid, custbranchid, aswdescription,
> productcode, quantity, cost, unitmeasure, purchord, invnumber,
> custitem,
> itemfamily, orderdate, deliverydate, itemgroup, itemgroupdesc,
> category, deladdrno


>
> but i don't know how to get values from other two tables and insert
> them in new table.
> I am totally new in SQL Server store procedure, please please help me
> like sample code.
> Thanks
> Developer
>

>> Developr,
>>
>> If you don't bother about performance You could use cursor, read BOL.
>>
>>
>>
>> vt
>>
>>
>>
>>
>>


>> > Hi,
>> >
>> > I am using ASP with SQL Server200. I am getting data from three tables
>> > then showing result. Its performance is not good. I want to use store
>> > procedure for this. IWhat I am doing in ASP
>> >
>> > SQL = "Select Custid, BranchId, purchord, PCode, description, " & _
>> > deliverydate, itemgroup, itemgroupdesc, category, deladdrno " & _
>> > "From table1 Where invoicedate BETWEEN "&DtAddFrom&" AND
>> > "&DtAddTo&" ORDER BY invoicedate"
>> >
>> > Set rs = Server.CreateObject("ADODB.RecordSet")
>> > rs.open SQL, conn, 3,1,1
>> > totalRecord = rs.RecordCount
>> >
>> > For i = 1 to totalRecord
>> > LocSQL = "SELECT branchName FROM table2 WHERE branchNumber="&
>> > rs.fields("Custid") &" and branchContactNo ='"& rs.fields("deladdrno")
>> > &"' and branchParent = "& companyID &""
>> > Set LocRs = LocConn.Execute(LocSQL)
>> >
>> > ItemSQL = "SELECT productCustomerRef FROM table3 WHERE
>> > productMacfRef = '"& rs.fields ("pcode") &"' AND productCompanyID = "&
>> > companyID &""
>> > Set ItemRs = LocConn.Execute(ItemSQL)
>> >
>> >
>> > rs.MoveNext 'Move to the next record
>> > Next
>> >
>> > In SQL Server I want in store procedure select data from above three
>> > tables and insert it in new table and access it in ASP.
>> > What I did in SQL Server2000
>> >
>> > CREATE PROCEDURE sp_einvoice



>> > AS
>> > SELECT productcode, description, quantity, cost, unitmeasure,
>> > cctimestamp, purchord, invoicedate
>> > FROM table1


>> > ORDER BY invoicedate
>> > GO
>> >
>> > It works fine and get required data but to get date from table2 and
>> > table3 I don't know how to loop and use table1 data to get it.
>> > am writing first time store procedure please help me.
>> >
>> > Thanks
>> >
>


 
 
Developer





PostPosted: Fri Nov 10 07:52:19 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table Main table that has huge data.
TABLE1
[companyid]
[cctimestamp]
[custid]
[branchContactNo]
[custbranchid]
[macfcostcentre]
[productcode]
[aswdescription]
[quantity]
[cost]
[unitmeasure]
[orderstatus]
[purchord] L
[invnumber]
[custitem]
[itemfamily]
[volume]
[weight]
[invoicedate]
[orderdate]
[deliverydate]
[stockqty]
[itemgroup]
[itemgroupdesc]
[category]
[deladdrno]
[recycledcont]
[recyclablecont]
[stockunit] [nvarchar]
[stockunitweightgross]
[cconnectid]


TABLE2
[branchID]
[companyid]
[branchNumber]
[branchName] -- NEED TO ACCESS THIS
[branchContactNo] --NEED TO ACCESS THIS

(Comparision condition is
WHERE branchnumber = TABLE1.custid and branchContactNo =
TABLE1.branchContactNo AND TABLE2.companyid = TABLE1.companyid)

table3
[productID]
[companyid]
[productMacfRef]
[productCustomerRef] --NEED TO ACCESS THIS

(Comparision condition is
WHERE productMacfRef = TABLE1.[productcode] AND TABLE3.companyid =
TABLE1.companyid)

hope this will help to understand.
Thanks



> post table1, table2 and table3 structure
> vt
>
>


> > Only for performance i am using store procedure. I tried it like
> > CREATE PROCEDURE sp_einvoice



> > AS
> > BEGIN
> > INSERT INTO newtable( cctimestamp, custid, custbranchid,
> > aswdescription, productcode, quantity, cost, unitmeasure, purchord,
> > invnumber, custitem,
> > itemfamily, orderdate, deliverydate, itemgroup,
> > itemgroupdesc, category, deladdrno)
> > SELECT cctimestamp, custid, custbranchid, aswdescription,
> > productcode, quantity, cost, unitmeasure, purchord, invnumber,
> > custitem,
> > itemfamily, orderdate, deliverydate, itemgroup, itemgroupdesc,
> > category, deladdrno


> >
> > but i don't know how to get values from other two tables and insert
> > them in new table.
> > I am totally new in SQL Server store procedure, please please help me
> > like sample code.
> > Thanks
> > Developer
> >

> >> Developr,
> >>
> >> If you don't bother about performance You could use cursor, read BOL.
> >>
> >>
> >>
> >> vt
> >>
> >>
> >>
> >>
> >>


> >> > Hi,
> >> >
> >> > I am using ASP with SQL Server200. I am getting data from three tables
> >> > then showing result. Its performance is not good. I want to use store
> >> > procedure for this. IWhat I am doing in ASP
> >> >
> >> > SQL = "Select Custid, BranchId, purchord, PCode, description, " & _
> >> > deliverydate, itemgroup, itemgroupdesc, category, deladdrno " & _
> >> > "From table1 Where invoicedate BETWEEN "&DtAddFrom&" AND
> >> > "&DtAddTo&" ORDER BY invoicedate"
> >> >
> >> > Set rs = Server.CreateObject("ADODB.RecordSet")
> >> > rs.open SQL, conn, 3,1,1
> >> > totalRecord = rs.RecordCount
> >> >
> >> > For i = 1 to totalRecord
> >> > LocSQL = "SELECT branchName FROM table2 WHERE branchNumber="&
> >> > rs.fields("Custid") &" and branchContactNo ='"& rs.fields("deladdrno")
> >> > &"' and branchParent = "& companyID &""
> >> > Set LocRs = LocConn.Execute(LocSQL)
> >> >
> >> > ItemSQL = "SELECT productCustomerRef FROM table3 WHERE
> >> > productMacfRef = '"& rs.fields ("pcode") &"' AND productCompanyID = "&
> >> > companyID &""
> >> > Set ItemRs = LocConn.Execute(ItemSQL)
> >> >
> >> >
> >> > rs.MoveNext 'Move to the next record
> >> > Next
> >> >
> >> > In SQL Server I want in store procedure select data from above three
> >> > tables and insert it in new table and access it in ASP.
> >> > What I did in SQL Server2000
> >> >
> >> > CREATE PROCEDURE sp_einvoice



> >> > AS
> >> > SELECT productcode, description, quantity, cost, unitmeasure,
> >> > cctimestamp, purchord, invoicedate
> >> > FROM table1


> >> > ORDER BY invoicedate
> >> > GO
> >> >
> >> > It works fine and get required data but to get date from table2 and
> >> > table3 I don't know how to loop and use table1 data to get it.
> >> > am writing first time store procedure please help me.
> >> >
> >> > Thanks
> >> >
> >

 
 
vt





PostPosted: Fri Nov 10 08:06:29 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table whats the structure of the table you trying to insert to

vt





> Main table that has huge data.
> TABLE1
> [companyid]
> [cctimestamp]
> [custid]
> [branchContactNo]
> [custbranchid]
> [macfcostcentre]
> [productcode]
> [aswdescription]
> [quantity]
> [cost]
> [unitmeasure]
> [orderstatus]
> [purchord] L
> [invnumber]
> [custitem]
> [itemfamily]
> [volume]
> [weight]
> [invoicedate]
> [orderdate]
> [deliverydate]
> [stockqty]
> [itemgroup]
> [itemgroupdesc]
> [category]
> [deladdrno]
> [recycledcont]
> [recyclablecont]
> [stockunit] [nvarchar]
> [stockunitweightgross]
> [cconnectid]
>
>
> TABLE2
> [branchID]
> [companyid]
> [branchNumber]
> [branchName] -- NEED TO ACCESS THIS
> [branchContactNo] --NEED TO ACCESS THIS
>
> (Comparision condition is
> WHERE branchnumber = TABLE1.custid and branchContactNo =
> TABLE1.branchContactNo AND TABLE2.companyid = TABLE1.companyid)
>
> table3
> [productID]
> [companyid]
> [productMacfRef]
> [productCustomerRef] --NEED TO ACCESS THIS
>
> (Comparision condition is
> WHERE productMacfRef = TABLE1.[productcode] AND TABLE3.companyid =
> TABLE1.companyid)
>
> hope this will help to understand.
> Thanks
>
>

>> post table1, table2 and table3 structure
>> vt
>>
>>


>> > Only for performance i am using store procedure. I tried it like
>> > CREATE PROCEDURE sp_einvoice



>> > AS
>> > BEGIN
>> > INSERT INTO newtable( cctimestamp, custid, custbranchid,
>> > aswdescription, productcode, quantity, cost, unitmeasure, purchord,
>> > invnumber, custitem,
>> > itemfamily, orderdate, deliverydate, itemgroup,
>> > itemgroupdesc, category, deladdrno)
>> > SELECT cctimestamp, custid, custbranchid, aswdescription,
>> > productcode, quantity, cost, unitmeasure, purchord, invnumber,
>> > custitem,
>> > itemfamily, orderdate, deliverydate, itemgroup, itemgroupdesc,
>> > category, deladdrno


>> >
>> > but i don't know how to get values from other two tables and insert
>> > them in new table.
>> > I am totally new in SQL Server store procedure, please please help me
>> > like sample code.
>> > Thanks
>> > Developer
>> >

>> >> Developr,
>> >>
>> >> If you don't bother about performance You could use cursor, read BOL.
>> >>
>> >>
>> >>
>> >> vt
>> >>
>> >>
>> >>
>> >>
>> >>


>> >> > Hi,
>> >> >
>> >> > I am using ASP with SQL Server200. I am getting data from three
>> >> > tables
>> >> > then showing result. Its performance is not good. I want to use
>> >> > store
>> >> > procedure for this. IWhat I am doing in ASP
>> >> >
>> >> > SQL = "Select Custid, BranchId, purchord, PCode, description, " &
>> >> > _
>> >> > deliverydate, itemgroup, itemgroupdesc, category, deladdrno " &
>> >> > _
>> >> > "From table1 Where invoicedate BETWEEN "&DtAddFrom&" AND
>> >> > "&DtAddTo&" ORDER BY invoicedate"
>> >> >
>> >> > Set rs = Server.CreateObject("ADODB.RecordSet")
>> >> > rs.open SQL, conn, 3,1,1
>> >> > totalRecord = rs.RecordCount
>> >> >
>> >> > For i = 1 to totalRecord
>> >> > LocSQL = "SELECT branchName FROM table2 WHERE branchNumber="&
>> >> > rs.fields("Custid") &" and branchContactNo ='"&
>> >> > rs.fields("deladdrno")
>> >> > &"' and branchParent = "& companyID &""
>> >> > Set LocRs = LocConn.Execute(LocSQL)
>> >> >
>> >> > ItemSQL = "SELECT productCustomerRef FROM table3 WHERE
>> >> > productMacfRef = '"& rs.fields ("pcode") &"' AND productCompanyID =
>> >> > "&
>> >> > companyID &""
>> >> > Set ItemRs = LocConn.Execute(ItemSQL)
>> >> >
>> >> >
>> >> > rs.MoveNext 'Move to the next record
>> >> > Next
>> >> >
>> >> > In SQL Server I want in store procedure select data from above three
>> >> > tables and insert it in new table and access it in ASP.
>> >> > What I did in SQL Server2000
>> >> >
>> >> > CREATE PROCEDURE sp_einvoice



>> >> > AS
>> >> > SELECT productcode, description, quantity, cost, unitmeasure,
>> >> > cctimestamp, purchord, invoicedate
>> >> > FROM table1



>> >> > ORDER BY invoicedate
>> >> > GO
>> >> >
>> >> > It works fine and get required data but to get date from table2 and
>> >> > table3 I don't know how to loop and use table1 data to get it.
>> >> > am writing first time store procedure please help me.
>> >> >
>> >> > Thanks
>> >> >
>> >
>


 
 
Developer





PostPosted: Fri Nov 10 08:23:47 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table Sorry i forgot.
Structure for insert table is
TABLE newTable
[companyid]
[cconnectid]
[branchcontactno]
[branchname] --fromtable2
[productcustomerref] --from table3 and all other are from table1
[usersessionid]
[cctimestamp]
[custid]
[custbranchid]
[productcode]
[aswdescription]
[quantity]
[cost] [float]
[unitmeasure]
[purchord]
[invnumber]
[custitem]
[itemfamily]
[orderdate]
[deliverydate]
[itemgroup]
[itemgroupdesc]
[category]

Thanks



> whats the structure of the table you trying to insert to
>
> vt
>
>
>


> > Main table that has huge data.
> > TABLE1
> > [companyid]
> > [cctimestamp]
> > [custid]
> > [branchContactNo]
> > [custbranchid]
> > [macfcostcentre]
> > [productcode]
> > [aswdescription]
> > [quantity]
> > [cost]
> > [unitmeasure]
> > [orderstatus]
> > [purchord] L
> > [invnumber]
> > [custitem]
> > [itemfamily]
> > [volume]
> > [weight]
> > [invoicedate]
> > [orderdate]
> > [deliverydate]
> > [stockqty]
> > [itemgroup]
> > [itemgroupdesc]
> > [category]
> > [deladdrno]
> > [recycledcont]
> > [recyclablecont]
> > [stockunit] [nvarchar]
> > [stockunitweightgross]
> > [cconnectid]
> >
> >
> > TABLE2
> > [branchID]
> > [companyid]
> > [branchNumber]
> > [branchName] -- NEED TO ACCESS THIS
> > [branchContactNo] --NEED TO ACCESS THIS
> >
> > (Comparision condition is
> > WHERE branchnumber = TABLE1.custid and branchContactNo =
> > TABLE1.branchContactNo AND TABLE2.companyid = TABLE1.companyid)
> >
> > table3
> > [productID]
> > [companyid]
> > [productMacfRef]
> > [productCustomerRef] --NEED TO ACCESS THIS
> >
> > (Comparision condition is
> > WHERE productMacfRef = TABLE1.[productcode] AND TABLE3.companyid =
> > TABLE1.companyid)
> >
> > hope this will help to understand.
> > Thanks
> >
> >

> >> post table1, table2 and table3 structure
> >> vt
> >>
> >>


> >> > Only for performance i am using store procedure. I tried it like
> >> > CREATE PROCEDURE sp_einvoice



> >> > AS
> >> > BEGIN
> >> > INSERT INTO newtable( cctimestamp, custid, custbranchid,
> >> > aswdescription, productcode, quantity, cost, unitmeasure, purchord,
> >> > invnumber, custitem,
> >> > itemfamily, orderdate, deliverydate, itemgroup,
> >> > itemgroupdesc, category, deladdrno)
> >> > SELECT cctimestamp, custid, custbranchid, aswdescription,
> >> > productcode, quantity, cost, unitmeasure, purchord, invnumber,
> >> > custitem,
> >> > itemfamily, orderdate, deliverydate, itemgroup, itemgroupdesc,
> >> > category, deladdrno


> >> >
> >> > but i don't know how to get values from other two tables and insert
> >> > them in new table.
> >> > I am totally new in SQL Server store procedure, please please help me
> >> > like sample code.
> >> > Thanks
> >> > Developer
> >> >

> >> >> Developr,
> >> >>
> >> >> If you don't bother about performance You could use cursor, read BOL.
> >> >>
> >> >>
> >> >>
> >> >> vt
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>


> >> >> > Hi,
> >> >> >
> >> >> > I am using ASP with SQL Server200. I am getting data from three
> >> >> > tables
> >> >> > then showing result. Its performance is not good. I want to use
> >> >> > store
> >> >> > procedure for this. IWhat I am doing in ASP
> >> >> >
> >> >> > SQL = "Select Custid, BranchId, purchord, PCode, description, " &
> >> >> > _
> >> >> > deliverydate, itemgroup, itemgroupdesc, category, deladdrno " &
> >> >> > _
> >> >> > "From table1 Where invoicedate BETWEEN "&DtAddFrom&" AND
> >> >> > "&DtAddTo&" ORDER BY invoicedate"
> >> >> >
> >> >> > Set rs = Server.CreateObject("ADODB.RecordSet")
> >> >> > rs.open SQL, conn, 3,1,1
> >> >> > totalRecord = rs.RecordCount
> >> >> >
> >> >> > For i = 1 to totalRecord
> >> >> > LocSQL = "SELECT branchName FROM table2 WHERE branchNumber="&
> >> >> > rs.fields("Custid") &" and branchContactNo ='"&
> >> >> > rs.fields("deladdrno")
> >> >> > &"' and branchParent = "& companyID &""
> >> >> > Set LocRs = LocConn.Execute(LocSQL)
> >> >> >
> >> >> > ItemSQL = "SELECT productCustomerRef FROM table3 WHERE
> >> >> > productMacfRef = '"& rs.fields ("pcode") &"' AND productCompanyID =
> >> >> > "&
> >> >> > companyID &""
> >> >> > Set ItemRs = LocConn.Execute(ItemSQL)
> >> >> >
> >> >> >
> >> >> > rs.MoveNext 'Move to the next record
> >> >> > Next
> >> >> >
> >> >> > In SQL Server I want in store procedure select data from above three
> >> >> > tables and insert it in new table and access it in ASP.
> >> >> > What I did in SQL Server2000
> >> >> >
> >> >> > CREATE PROCEDURE sp_einvoice



> >> >> > AS
> >> >> > SELECT productcode, description, quantity, cost, unitmeasure,
> >> >> > cctimestamp, purchord, invoicedate
> >> >> > FROM table1



> >> >> > ORDER BY invoicedate
> >> >> > GO
> >> >> >
> >> >> > It works fine and get required data but to get date from table2 and
> >> >> > table3 I don't know how to loop and use table1 data to get it.
> >> >> > am writing first time store procedure please help me.
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >
> >

 
 
Tracy





PostPosted: Fri Nov 10 08:37:38 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table
> Hi,
>
> I am using ASP with SQL Server200. I am getting data from three tables
> then showing result. Its performance is not good. I want to use store
> procedure for this. IWhat I am doing in ASP
>
> SQL = "Select Custid, BranchId, purchord, PCode, description, " & _
> deliverydate, itemgroup, itemgroupdesc, category, deladdrno " & _
> "From table1 Where invoicedate BETWEEN "&DtAddFrom&" AND
> "&DtAddTo&" ORDER BY invoicedate"
>
> Set rs = Server.CreateObject("ADODB.RecordSet")
> rs.open SQL, conn, 3,1,1
> totalRecord = rs.RecordCount
>
> For i = 1 to totalRecord
> LocSQL = "SELECT branchName FROM table2 WHERE branchNumber="&
> rs.fields("Custid") &" and branchContactNo ='"& rs.fields("deladdrno")
> &"' and branchParent = "& companyID &""
> Set LocRs = LocConn.Execute(LocSQL)
>
> ItemSQL = "SELECT productCustomerRef FROM table3 WHERE
> productMacfRef = '"& rs.fields ("pcode") &"' AND productCompanyID = "&
> companyID &""
> Set ItemRs = LocConn.Execute(ItemSQL)
>
>
> rs.MoveNext 'Move to the next record
> Next
>
> In SQL Server I want in store procedure select data from above three
> tables and insert it in new table and access it in ASP.
> What I did in SQL Server2000
>
> CREATE PROCEDURE sp_einvoice



> AS
> SELECT productcode, description, quantity, cost, unitmeasure,
> cctimestamp, purchord, invoicedate
> FROM table1


> ORDER BY invoicedate
> GO
>
> It works fine and get required data but to get date from table2 and
> table3 I don't know how to loop and use table1 data to get it.
> am writing first time store procedure please help me.
>
> Thanks
>

This will get the combined resultset from the three tables:

SELECT
t1.CustID,
t1.BranchID,
t1.PurchOrd,
t1.PCode,
t1.Description,
t1.DeliveryDate,
t1.ItemGroup,
t1.ItemGroupDesc,
t1.Category,
t1.DelAddrNo,
t2.BranchName,
t3.ProductCustomerRef
FROM table1 AS t1
INNER JOIN table2 AS t2
ON t1.CustID = t2.BranchNumber
AND t1.DelAddrNo = t2.BranchContactNo
INNER JOIN table3 AS t3
ON t1.PCode = t3.ProductMacRef
AND t1.CompanyID = t3.ProductCompanyID


ORDER BY t1.invoicedate

Assuming this resultset matches the structure of your destination table,
you can perform the insert as follows:

INSERT INTO table4
SELECT
t1.CustID,
t1.BranchID,
t1.PurchOrd,
t1.PCode,
t1.Description,
t1.DeliveryDate,
t1.ItemGroup,
t1.ItemGroupDesc,
t1.Category,
t1.DelAddrNo,
t2.BranchName,
t3.ProductCustomerRef
FROM table1 AS t1
INNER JOIN table2 AS t2
ON t1.CustID = t2.BranchNumber
AND t1.DelAddrNo = t2.BranchContactNo
INNER JOIN table3 AS t3
ON t1.PCode = t3.ProductMacRef
AND t1.CompanyID = t3.ProductCompanyID


ORDER BY t1.invoicedate



--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
 
 
vt





PostPosted: Fri Nov 10 08:47:46 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table Seeing the table structure you don't need to use cursor. The necessory data
can be retrieved by joining these 3 tables.



insert into
newTable(companyid,cconnectid,branchcontactno,branchname,productcustomerref,cctimestamp,custid,custbranchid,productcode,aswdescription,quantity
cost,unitmeasure,purchord,invnumber,custitem,itemfamily,orderdate,deliverydate,itemgroup,itemgroupdesc,category
)
select
t1.companyid,t1.cconnectid,t1.branchContactNo,t2.branchNumber,t3.productCustomerRef,t1.cctimestamp,t1.custid,t1.custbranchid,t1.productcode,t1.aswdescription,t1.quantity,
t1.cost,t1.unitmeasure,t1.purchord,t1.invnumber,t1.custitem,t1.itemfamily,t1.orderdate,t1.deliverydate,t1.itemgroup,t1.itemgroupdesc,t1.category
from table1 t1
inner join table2 t2 on t2.branchnumber =t1.custid and
t2.branchContactNo =t1.branchContactNo AND t2.companyid = t1.companyid
inner join table3 t3 on t3.productMacfRef = t1.productcode AND
T3.companyid =t1.companyid

@invoicedate2

vt




companyid,cconnectid,branchcontactno,branchname,productcustomerref,usersessionid,cctimestamp,custid,custbranchid,productcode,aswdescription,quantity
cost,unitmeasure,purchord,invnumber,custitem,itemfamily,orderdate,deliverydate,itemgroup,itemgroupdesc,category



> Sorry i forgot.
> Structure for insert table is
> TABLE newTable
> [companyid]
> [cconnectid]
> [branchcontactno]
> [branchname] --fromtable2
> [productcustomerref] --from table3 and all other are from table1
> [usersessionid]
> [cctimestamp]
> [custid]
> [custbranchid]
> [productcode]
> [aswdescription]
> [quantity]
> [cost] [float]
> [unitmeasure]
> [purchord]
> [invnumber]
> [custitem]
> [itemfamily]
> [orderdate]
> [deliverydate]
> [itemgroup]
> [itemgroupdesc]
> [category]
>
> Thanks
>
>

>> whats the structure of the table you trying to insert to
>>
>> vt
>>
>>
>>


>> > Main table that has huge data.
>> > TABLE1
>> > [companyid]
>> > [cctimestamp]
>> > [custid]
>> > [branchContactNo]
>> > [custbranchid]
>> > [macfcostcentre]
>> > [productcode]
>> > [aswdescription]
>> > [quantity]
>> > [cost]
>> > [unitmeasure]
>> > [orderstatus]
>> > [purchord] L
>> > [invnumber]
>> > [custitem]
>> > [itemfamily]
>> > [volume]
>> > [weight]
>> > [invoicedate]
>> > [orderdate]
>> > [deliverydate]
>> > [stockqty]
>> > [itemgroup]
>> > [itemgroupdesc]
>> > [category]
>> > [deladdrno]
>> > [recycledcont]
>> > [recyclablecont]
>> > [stockunit] [nvarchar]
>> > [stockunitweightgross]
>> > [cconnectid]
>> >
>> >
>> > TABLE2
>> > [branchID]
>> > [companyid]
>> > [branchNumber]
>> > [branchName] -- NEED TO ACCESS THIS
>> > [branchContactNo] --NEED TO ACCESS THIS
>> >
>> > (Comparision condition is
>> > WHERE branchnumber = TABLE1.custid and branchContactNo =
>> > TABLE1.branchContactNo AND TABLE2.companyid = TABLE1.companyid)
>> >
>> > table3
>> > [productID]
>> > [companyid]
>> > [productMacfRef]
>> > [productCustomerRef] --NEED TO ACCESS THIS
>> >
>> > (Comparision condition is
>> > WHERE productMacfRef = TABLE1.[productcode] AND TABLE3.companyid =
>> > TABLE1.companyid)
>> >
>> > hope this will help to understand.
>> > Thanks
>> >
>> >

>> >> post table1, table2 and table3 structure
>> >> vt
>> >>
>> >>


>> >> > Only for performance i am using store procedure. I tried it like
>> >> > CREATE PROCEDURE sp_einvoice



>> >> > AS
>> >> > BEGIN
>> >> > INSERT INTO newtable( cctimestamp, custid, custbranchid,
>> >> > aswdescription, productcode, quantity, cost, unitmeasure, purchord,
>> >> > invnumber, custitem,
>> >> > itemfamily, orderdate, deliverydate, itemgroup,
>> >> > itemgroupdesc, category, deladdrno)
>> >> > SELECT cctimestamp, custid, custbranchid, aswdescription,
>> >> > productcode, quantity, cost, unitmeasure, purchord, invnumber,
>> >> > custitem,
>> >> > itemfamily, orderdate, deliverydate, itemgroup, itemgroupdesc,
>> >> > category, deladdrno


>> >> >
>> >> > but i don't know how to get values from other two tables and insert
>> >> > them in new table.
>> >> > I am totally new in SQL Server store procedure, please please help
>> >> > me
>> >> > like sample code.
>> >> > Thanks
>> >> > Developer
>> >> >

>> >> >> Developr,
>> >> >>
>> >> >> If you don't bother about performance You could use cursor, read
>> >> >> BOL.
>> >> >>
>> >> >>
>> >> >>
>> >> >> vt
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>


>> >> >> > Hi,
>> >> >> >
>> >> >> > I am using ASP with SQL Server200. I am getting data from three
>> >> >> > tables
>> >> >> > then showing result. Its performance is not good. I want to use
>> >> >> > store
>> >> >> > procedure for this. IWhat I am doing in ASP
>> >> >> >
>> >> >> > SQL = "Select Custid, BranchId, purchord, PCode, description, "
>> >> >> > &
>> >> >> > _
>> >> >> > deliverydate, itemgroup, itemgroupdesc, category, deladdrno "
>> >> >> > &
>> >> >> > _
>> >> >> > "From table1 Where invoicedate BETWEEN "&DtAddFrom&" AND
>> >> >> > "&DtAddTo&" ORDER BY invoicedate"
>> >> >> >
>> >> >> > Set rs = Server.CreateObject("ADODB.RecordSet")
>> >> >> > rs.open SQL, conn, 3,1,1
>> >> >> > totalRecord = rs.RecordCount
>> >> >> >
>> >> >> > For i = 1 to totalRecord
>> >> >> > LocSQL = "SELECT branchName FROM table2 WHERE branchNumber="&
>> >> >> > rs.fields("Custid") &" and branchContactNo ='"&
>> >> >> > rs.fields("deladdrno")
>> >> >> > &"' and branchParent = "& companyID &""
>> >> >> > Set LocRs = LocConn.Execute(LocSQL)
>> >> >> >
>> >> >> > ItemSQL = "SELECT productCustomerRef FROM table3 WHERE
>> >> >> > productMacfRef = '"& rs.fields ("pcode") &"' AND productCompanyID
>> >> >> > =
>> >> >> > "&
>> >> >> > companyID &""
>> >> >> > Set ItemRs = LocConn.Execute(ItemSQL)
>> >> >> >
>> >> >> >
>> >> >> > rs.MoveNext 'Move to the next record
>> >> >> > Next
>> >> >> >
>> >> >> > In SQL Server I want in store procedure select data from above
>> >> >> > three
>> >> >> > tables and insert it in new table and access it in ASP.
>> >> >> > What I did in SQL Server2000
>> >> >> >
>> >> >> > CREATE PROCEDURE sp_einvoice



>> >> >> > AS
>> >> >> > SELECT productcode, description, quantity, cost, unitmeasure,
>> >> >> > cctimestamp, purchord, invoicedate
>> >> >> > FROM table1



>> >> >> > ORDER BY invoicedate
>> >> >> > GO
>> >> >> >
>> >> >> > It works fine and get required data but to get date from table2
>> >> >> > and
>> >> >> > table3 I don't know how to loop and use table1 data to get it.
>> >> >> > am writing first time store procedure please help me.
>> >> >> >
>> >> >> > Thanks
>> >> >> >
>> >> >
>> >
>


 
 
Developer





PostPosted: Fri Nov 10 09:38:43 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table You are champ.
Your query really open my mind and fix whole thing.
Can you please guide me which way is best to extract that data on
excel. I am using ASP and thinking about to use DTS to populate data on
excel for reporting.
Many thanks Tracy.




> > Hi,
> >
> > I am using ASP with SQL Server200. I am getting data from three tables
> > then showing result. Its performance is not good. I want to use store
> > procedure for this. IWhat I am doing in ASP
> >
> > SQL = "Select Custid, BranchId, purchord, PCode, description, " & _
> > deliverydate, itemgroup, itemgroupdesc, category, deladdrno " & _
> > "From table1 Where invoicedate BETWEEN "&DtAddFrom&" AND
> > "&DtAddTo&" ORDER BY invoicedate"
> >
> > Set rs = Server.CreateObject("ADODB.RecordSet")
> > rs.open SQL, conn, 3,1,1
> > totalRecord = rs.RecordCount
> >
> > For i = 1 to totalRecord
> > LocSQL = "SELECT branchName FROM table2 WHERE branchNumber="&
> > rs.fields("Custid") &" and branchContactNo ='"& rs.fields("deladdrno")
> > &"' and branchParent = "& companyID &""
> > Set LocRs = LocConn.Execute(LocSQL)
> >
> > ItemSQL = "SELECT productCustomerRef FROM table3 WHERE
> > productMacfRef = '"& rs.fields ("pcode") &"' AND productCompanyID = "&
> > companyID &""
> > Set ItemRs = LocConn.Execute(ItemSQL)
> >
> >
> > rs.MoveNext 'Move to the next record
> > Next
> >
> > In SQL Server I want in store procedure select data from above three
> > tables and insert it in new table and access it in ASP.
> > What I did in SQL Server2000
> >
> > CREATE PROCEDURE sp_einvoice



> > AS
> > SELECT productcode, description, quantity, cost, unitmeasure,
> > cctimestamp, purchord, invoicedate
> > FROM table1


> > ORDER BY invoicedate
> > GO
> >
> > It works fine and get required data but to get date from table2 and
> > table3 I don't know how to loop and use table1 data to get it.
> > am writing first time store procedure please help me.
> >
> > Thanks
> >
>
> This will get the combined resultset from the three tables:
>
> SELECT
> t1.CustID,
> t1.BranchID,
> t1.PurchOrd,
> t1.PCode,
> t1.Description,
> t1.DeliveryDate,
> t1.ItemGroup,
> t1.ItemGroupDesc,
> t1.Category,
> t1.DelAddrNo,
> t2.BranchName,
> t3.ProductCustomerRef
> FROM table1 AS t1
> INNER JOIN table2 AS t2
> ON t1.CustID = t2.BranchNumber
> AND t1.DelAddrNo = t2.BranchContactNo
> INNER JOIN table3 AS t3
> ON t1.PCode = t3.ProductMacRef
> AND t1.CompanyID = t3.ProductCompanyID


> ORDER BY t1.invoicedate
>
> Assuming this resultset matches the structure of your destination table,
> you can perform the insert as follows:
>
> INSERT INTO table4
> SELECT
> t1.CustID,
> t1.BranchID,
> t1.PurchOrd,
> t1.PCode,
> t1.Description,
> t1.DeliveryDate,
> t1.ItemGroup,
> t1.ItemGroupDesc,
> t1.Category,
> t1.DelAddrNo,
> t2.BranchName,
> t3.ProductCustomerRef
> FROM table1 AS t1
> INNER JOIN table2 AS t2
> ON t1.CustID = t2.BranchNumber
> AND t1.DelAddrNo = t2.BranchContactNo
> INNER JOIN table3 AS t3
> ON t1.PCode = t3.ProductMacRef
> AND t1.CompanyID = t3.ProductCompanyID


> ORDER BY t1.invoicedate
>
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

 
 
Developer





PostPosted: Fri Nov 10 09:45:20 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table Many thanks vt.
I appreciate your help.

Developer


> Seeing the table structure you don't need to use cursor. The necessory data
> can be retrieved by joining these 3 tables.
>
>
>
> insert into
> newTable(companyid,cconnectid,branchcontactno,branchname,productcustomerref,cctimestamp,custid,custbranchid,productcode,aswdescription,quantity
> cost,unitmeasure,purchord,invnumber,custitem,itemfamily,orderdate,deliverydate,itemgroup,itemgroupdesc,category
> )
> select
> t1.companyid,t1.cconnectid,t1.branchContactNo,t2.branchNumber,t3.productCustomerRef,t1.cctimestamp,t1.custid,t1.custbranchid,t1.productcode,t1.aswdescription,t1.quantity,
> t1.cost,t1.unitmeasure,t1.purchord,t1.invnumber,t1.custitem,t1.itemfamily,t1.orderdate,t1.deliverydate,t1.itemgroup,t1.itemgroupdesc,t1.category
> from table1 t1
> inner join table2 t2 on t2.branchnumber =t1.custid and
> t2.branchContactNo =t1.branchContactNo AND t2.companyid = t1.companyid
> inner join table3 t3 on t3.productMacfRef = t1.productcode AND
> T3.companyid =t1.companyid


>
> vt
>
>
>
>
> companyid,cconnectid,branchcontactno,branchname,productcustomerref,usersessionid,cctimestamp,custid,custbranchid,productcode,aswdescription,quantity
> cost,unitmeasure,purchord,invnumber,custitem,itemfamily,orderdate,deliverydate,itemgroup,itemgroupdesc,category
>


> > Sorry i forgot.
> > Structure for insert table is
> > TABLE newTable
> > [companyid]
> > [cconnectid]
> > [branchcontactno]
> > [branchname] --fromtable2
> > [productcustomerref] --from table3 and all other are from table1
> > [usersessionid]
> > [cctimestamp]
> > [custid]
> > [custbranchid]
> > [productcode]
> > [aswdescription]
> > [quantity]
> > [cost] [float]
> > [unitmeasure]
> > [purchord]
> > [invnumber]
> > [custitem]
> > [itemfamily]
> > [orderdate]
> > [deliverydate]
> > [itemgroup]
> > [itemgroupdesc]
> > [category]
> >
> > Thanks
> >
> >

> >> whats the structure of the table you trying to insert to
> >>
> >> vt
> >>
> >>
> >>


> >> > Main table that has huge data.
> >> > TABLE1
> >> > [companyid]
> >> > [cctimestamp]
> >> > [custid]
> >> > [branchContactNo]
> >> > [custbranchid]
> >> > [macfcostcentre]
> >> > [productcode]
> >> > [aswdescription]
> >> > [quantity]
> >> > [cost]
> >> > [unitmeasure]
> >> > [orderstatus]
> >> > [purchord] L
> >> > [invnumber]
> >> > [custitem]
> >> > [itemfamily]
> >> > [volume]
> >> > [weight]
> >> > [invoicedate]
> >> > [orderdate]
> >> > [deliverydate]
> >> > [stockqty]
> >> > [itemgroup]
> >> > [itemgroupdesc]
> >> > [category]
> >> > [deladdrno]
> >> > [recycledcont]
> >> > [recyclablecont]
> >> > [stockunit] [nvarchar]
> >> > [stockunitweightgross]
> >> > [cconnectid]
> >> >
> >> >
> >> > TABLE2
> >> > [branchID]
> >> > [companyid]
> >> > [branchNumber]
> >> > [branchName] -- NEED TO ACCESS THIS
> >> > [branchContactNo] --NEED TO ACCESS THIS
> >> >
> >> > (Comparision condition is
> >> > WHERE branchnumber = TABLE1.custid and branchContactNo =
> >> > TABLE1.branchContactNo AND TABLE2.companyid = TABLE1.companyid)
> >> >
> >> > table3
> >> > [productID]
> >> > [companyid]
> >> > [productMacfRef]
> >> > [productCustomerRef] --NEED TO ACCESS THIS
> >> >
> >> > (Comparision condition is
> >> > WHERE productMacfRef = TABLE1.[productcode] AND TABLE3.companyid =
> >> > TABLE1.companyid)
> >> >
> >> > hope this will help to understand.
> >> > Thanks
> >> >
> >> >

> >> >> post table1, table2 and table3 structure
> >> >> vt
> >> >>
> >> >>


> >> >> > Only for performance i am using store procedure. I tried it like
> >> >> > CREATE PROCEDURE sp_einvoice



> >> >> > AS
> >> >> > BEGIN
> >> >> > INSERT INTO newtable( cctimestamp, custid, custbranchid,
> >> >> > aswdescription, productcode, quantity, cost, unitmeasure, purchord,
> >> >> > invnumber, custitem,
> >> >> > itemfamily, orderdate, deliverydate, itemgroup,
> >> >> > itemgroupdesc, category, deladdrno)
> >> >> > SELECT cctimestamp, custid, custbranchid, aswdescription,
> >> >> > productcode, quantity, cost, unitmeasure, purchord, invnumber,
> >> >> > custitem,
> >> >> > itemfamily, orderdate, deliverydate, itemgroup, itemgroupdesc,
> >> >> > category, deladdrno


> >> >> >
> >> >> > but i don't know how to get values from other two tables and insert
> >> >> > them in new table.
> >> >> > I am totally new in SQL Server store procedure, please please help
> >> >> > me
> >> >> > like sample code.
> >> >> > Thanks
> >> >> > Developer
> >> >> >

> >> >> >> Developr,
> >> >> >>
> >> >> >> If you don't bother about performance You could use cursor, read
> >> >> >> BOL.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> vt
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>


> >> >> >> > Hi,
> >> >> >> >
> >> >> >> > I am using ASP with SQL Server200. I am getting data from three
> >> >> >> > tables
> >> >> >> > then showing result. Its performance is not good. I want to use
> >> >> >> > store
> >> >> >> > procedure for this. IWhat I am doing in ASP
> >> >> >> >
> >> >> >> > SQL = "Select Custid, BranchId, purchord, PCode, description, "
> >> >> >> > &
> >> >> >> > _
> >> >> >> > deliverydate, itemgroup, itemgroupdesc, category, deladdrno "
> >> >> >> > &
> >> >> >> > _
> >> >> >> > "From table1 Where invoicedate BETWEEN "&DtAddFrom&" AND
> >> >> >> > "&DtAddTo&" ORDER BY invoicedate"
> >> >> >> >
> >> >> >> > Set rs = Server.CreateObject("ADODB.RecordSet")
> >> >> >> > rs.open SQL, conn, 3,1,1
> >> >> >> > totalRecord = rs.RecordCount
> >> >> >> >
> >> >> >> > For i = 1 to totalRecord
> >> >> >> > LocSQL = "SELECT branchName FROM table2 WHERE branchNumber="&
> >> >> >> > rs.fields("Custid") &" and branchContactNo ='"&
> >> >> >> > rs.fields("deladdrno")
> >> >> >> > &"' and branchParent = "& companyID &""
> >> >> >> > Set LocRs = LocConn.Execute(LocSQL)
> >> >> >> >
> >> >> >> > ItemSQL = "SELECT productCustomerRef FROM table3 WHERE
> >> >> >> > productMacfRef = '"& rs.fields ("pcode") &"' AND productCompanyID
> >> >> >> > =
> >> >> >> > "&
> >> >> >> > companyID &""
> >> >> >> > Set ItemRs = LocConn.Execute(ItemSQL)
> >> >> >> >
> >> >> >> >
> >> >> >> > rs.MoveNext 'Move to the next record
> >> >> >> > Next
> >> >> >> >
> >> >> >> > In SQL Server I want in store procedure select data from above
> >> >> >> > three
> >> >> >> > tables and insert it in new table and access it in ASP.
> >> >> >> > What I did in SQL Server2000
> >> >> >> >
> >> >> >> > CREATE PROCEDURE sp_einvoice



> >> >> >> > AS
> >> >> >> > SELECT productcode, description, quantity, cost, unitmeasure,
> >> >> >> > cctimestamp, purchord, invoicedate
> >> >> >> > FROM table1



> >> >> >> > ORDER BY invoicedate
> >> >> >> > GO
> >> >> >> >
> >> >> >> > It works fine and get required data but to get date from table2
> >> >> >> > and
> >> >> >> > table3 I don't know how to loop and use table1 data to get it.
> >> >> >> > am writing first time store procedure please help me.
> >> >> >> >
> >> >> >> > Thanks
> >> >> >> >
> >> >> >
> >> >
> >

 
 
Tracy





PostPosted: Fri Nov 10 09:45:41 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table
> You are champ.
> Your query really open my mind and fix whole thing.
> Can you please guide me which way is best to extract that data on
> excel. I am using ASP and thinking about to use DTS to populate data on
> excel for reporting.
> Many thanks Tracy.
>

It's actually quite easy to produce an Excel spreadsheet from an ASP
page, by setting the proper ContentType. See here for more info:
http://www.15seconds.com/Issue/970515.htm


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
 
 
Developer





PostPosted: Fri Nov 10 10:02:29 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table I am using changing Mime type <%
Response.ContentType = "application/vnd.ms-excel"
%>
then writen data to table in loop. but take ages because some time data
arouond 10K.
What i want, transfer data from SQL Server to Excel using SQL Server
(might be Data transforamtion Service DTS) then provide link for user
to view report in excel.
If there is any other approach/idea please tell.
Thanks



> > You are champ.
> > Your query really open my mind and fix whole thing.
> > Can you please guide me which way is best to extract that data on
> > excel. I am using ASP and thinking about to use DTS to populate data on
> > excel for reporting.
> > Many thanks Tracy.
> >
>
> It's actually quite easy to produce an Excel spreadsheet from an ASP
> page, by setting the proper ContentType. See here for more info:
> http://www.15seconds.com/Issue/970515.htm
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

 
 
Tracy





PostPosted: Fri Nov 10 10:07:47 CST 2006 Top

SQL Server Developer >> Get data from three table and insert it in new table
> I am using changing Mime type <%
> Response.ContentType = "application/vnd.ms-excel"
> %>
> then writen data to table in loop. but take ages because some time data
> arouond 10K.
> What i want, transfer data from SQL Server to Excel using SQL Server
> (might be Data transforamtion Service DTS) then provide link for user
> to view report in excel.
> If there is any other approach/idea please tell.
> Thanks
>

In that case, just take the SELECT statement that I gave you, and use
that as the source for a DTS package that writes to an Excel spreadsheet.


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com