quote:

---

the records that i retrieved is only 5 rows then the remaining 5 rows is null values.

---

insert into table ( . . .)
select  . . .
from    sometable
where   somecol is not null

select * from table

insert into tblinfo (Customer_First_Name, Customer_Last_Name, Address, Telephone_Number)
select  r.Customer_First_Name, r.Customer_Last_Name, r.Address, r.Telephone_Number
from   master..spt_values n
       left join
       (
           select  row_no = row_number() over (order by somecol),
                   Customer_First_Name, Customer_Last_Name, Address, Telephone_Number
           from    sometable
       ) r on n.number = r.row
where n.type='p'
and n.number between 1 and 10

insert into TEMP_TABLE (CustomerFN, CustomerLN, Address, TelNo)
select  r.Customer_First_Name, r.Customer_Last_Name, r.Address, r.Telephone_Number
from   master..spt_values n
       left join
       (
           select  row_no = row_number() over (order by Customer_First_Name, Customer_Last_Name, Address, Telephone_Number),
                   Customer_First_Name, Customer_Last_Name, Address, Telephone_Number
           from    CustomerDetails
       ) r on n.number = r.row --Error Part
where n.type='p'
and n.number between 1 and 10

quote:

---

Originally posted by visakh16

you can use internal count table if you dont want to give values explicitly

insert into tblinfo (Customer_First_Name, Customer_Last_Name, Address, Telephone_Number)
select  r.Customer_First_Name, r.Customer_Last_Name, r.Address, r.Telephone_Number
from   master..spt_values n
       left join
       (
           select  row_no = row_number() over (order by somecol),
                   Customer_First_Name, Customer_Last_Name, Address, Telephone_Number
           from    sometable
       ) r on n.number = r.row_no
where n.type='p'
and n.number between 1 and 10

---

insert into tblinfo (Customer_First_Name, Customer_Last_Name, Address, Telephone_Number)
select  r.Customer_First_Name, r.Customer_Last_Name, r.Address, r.Telephone_Number
from   master..spt_values n
       left join
       (
           select  row_no = row_number() over (order by somecol),
                   Customer_First_Name, Customer_Last_Name, Address, Telephone_Number
           from    sometable
           CustomerID = @CustomerID
       ) r on n.number = r.row_no
where n.type='p'
and n.number between 1 and 10

insert into TEMP_BIR2307_MOP0 
	(
		ATCCode,SupplierID,
		CVAmount,CreditAmount,		
		Description,SupplierName,
		Address1,Address2,
		TIN,ModeOfPayment
	)
SELECT      r.ATCCode,r.SupplierID, 
	    SUM(r.CVAmount) AS CVAmount, 
	    SUM(r.CreditAmount) AS CreditAmount, 
            r.Description,r.SupplierName, 
            r.Address1,r.Address2, 
            r.TIN,r.ModeOfPayment
from   master..spt_values n
       left join
       (
           select  row_no = row_number() OVER(ORDER BY ATCCode),
					ATCCode,SupplierID, 
					CVAmount,CreditAmount, 
					Description,SupplierName, 
					Address1,Address2, 
					TIN,ModeOfPayment
	FROM dbo.vwGetATCSummary
	WHERE CVDate >= '07/01/2009' AND SupplierID = 22
	GROUP BY ATCCode,SupplierID,
	         CVAmount,CreditAmount,
		 Description,SupplierName, 
	         Address1,Address2, 
                 TIN,ModeOfPayment
		) r on n.number = r.row_no
where n.type='p'
and n.number between 1 and 13

quote:

---

Originally posted by chriztoph

hi everyone!
i have this code but got me error in GROUP BY

BEGIN
insert into TEMP_BIR2307_MOP0 
	(
		ATCCode,
		SupplierID,
		CVAmount,
		CreditAmount,		
		Description,
		SupplierName,
		Address1,
		Address2,
		TIN,
		ModeOfPayment
	)
SELECT      r.ATCCode,
	    r.SupplierID, 
	    SUM(r.CVAmount) AS CVAmount, 
	    SUM(r.CreditAmount) AS CreditAmount, 
            r.Description, 
            r.SupplierName, 
            r.Address1, 
            r.Address2, 
            r.TIN, 
            r.ModeOfPayment
from   master..spt_values n
       left join
       (
           select  row_no = row_number() OVER(ORDER BY ATCCode),
					ATCCode, 
					SupplierID, 
					CVAmount, 
					CreditAmount, 
					Description, 
					SupplierName, 
					Address1, 
					Address2, 
					TIN, 
					ModeOfPayment
		FROM dbo.vwGetATCSummary
		WHERE CVDate >= '07/01/2009' AND SupplierID = 22
		GROUP BY ATCCode,
		SupplierID,
                CVAmount
		Description, 
		SupplierName, 
		Address1, 
		Address2, 
                TIN, 
                ModeOfPayment
		) r on n.number = r.row_no
where n.type='p'
and n.number between 1 and 13
END

But got me an error
Msg 8120, Level 16, State 1, Line 4
Column 'dbo.vwGetATCSummary.CVAmount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Please help..

---