new program - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

new program

Author

Topic

bblosse1
Starting Member

4 Posts

Posted - 2006-07-18 : 13:36:30

Hello! I'm new to the site, can you help me?
I am programming so that SQL provides a flat file to be imported into another system. I am struggling between the data specification and what is possible with SQL.
I have a script example:

USE Northwind
GO

SELECT Employees.EmployeeID, Employees.LastName,
Employees.FirstName, EmployeeTerritories.TerritoryID,
Territories.TerritoryDescription
FROM EmployeeTerritories
INNER JOIN Employees ON
EmployeeTerritories.EmployeeID = Employees.EmployeeID
INNER JOIN Territories ON
EmployeeTerritories.TerritoryID = Territories.TerritoryID
WHERE (Employees.EmployeeID = 1) OR
(Employees.EmployeeID = 4)

[Results]
1 Davolio Nancy______06897 Wilton
1 Davolio Nancy______19713 Neward
4 Peacock Margaret___20852 Rockville
4 Peacock Margaret___27403 Greensboro
4 Peacock Margaret___27511 Cary

I really need it to provide results like this (per specification):

1 Davolio Nancy
______19713 Neward
______06897 Wilton
4 Peacock Margare
______20852 Rockville
______27403 Greensboro
______27511 Cary

Any ideas?
I'm told this is at the presentation layer. What programs are being used to automate\schedule this type of data transfer?

Thanks,
Brad

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-18 : 18:41:36

You could do it in DTS if you wanted - but you won't find any of the inbuilt transformation components that do this because they generally output in a flat format. If you want to pursue DTS, then you'll probably have to create an ActiveX task to do it.
(after thinking about it...) you could probably write a stored proc that output this, but methinks it would be quite nasty.
How much data is there to output?
If there isn't much data I would have a look at using XSL. It means creating a stored proc (or similar) to output the data as XML, then using an XSLT to transform it into your format. The advantages are that the templates are easily maintainable. But if you're outputting lots of data it will probably be slower than a 'pure' SQL solution.

HTH,

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources