quote:

---

Originally posted by Peso

How does your query like like today for exporting the data?


Peter Larsson
Helsingborg, Sweden

---

ALTER VIEW EXPORT_ORDERS
AS

SELECT		1 AS ROW_ORDER, 
		'HEADER ' + CONVERT(char(25),GetDate()) AS Data_Line,
		NULL AS Cust_ID
UNION ALL
SELECT		2,
		COALESCE(CONVERT(char(15),OrderID),'')
			+ COALESCE(CustomerID,'')
			+ COALESCE(CONVERT(char(15),EmployeeID),'')
			+ COALESCE(CONVERT(char(25),OrderDate),'')
			+ COALESCE(CONVERT(char(25),RequiredDate),'')
			+ COALESCE(CONVERT(char(25),ShippedDate),'')
			+ COALESCE(CONVERT(char(15),ShipVia),'')
			+ COALESCE(CONVERT(char(15),Freight),'')
			+ COALESCE(CONVERT(char(80),ShipName),'')
			+ COALESCE(CONVERT(char(120),ShipAddress),'')
			+ COALESCE(CONVERT(char(30),ShipCity),'')
			+ COALESCE(CONVERT(char(30),ShipRegion),'')
			+ COALESCE(CONVERT(char(20),ShipPostalCode),'')
			+ COALESCE(CONVERT(char(30),ShipCountry),''),
		CustomerID
FROM		Orders
UNION ALL
SELECT		3,
		'TRAILER ' 
			+ CONVERT(char(25), GetDate())
			+ CONVERT(char(15), q.cnt),
		q.CustomerID
FROM		(
			SELECT		CustomerID, 
					COUNT(*) cnt
			FROM		Orders
		) q

DECLARE	@Customers TABLE (CustomerID INT)

INSERT	@Customers
SELECT	DISTINCT CustomerID
FROM	Orders
---WHERE	bla bla bla

DECLARE @CustID INT

SELECT	@CustID = MIN(CustomerID)
FROM	@Customers

WHILE @CustID IS NOT NULL
	BEGIN
		EXEC master..xp_cmdshell 'bcp "SELECT Data_Line FROM EXPORT_ORDERS WHERE CustomerID = ' + cast(@CustID as varchar) + '" queryout C:\Orders' + cast(@CustID as varchar) + '.txt -S<servername> -c -T'

		SELECT	@CustID = MIN(CustomerID)
		FROM	@Customers
		WHERE	CustomerID > @CustID
	END