Solved

Insert Blank Line in Output

Posted on 2007-11-29
9
782 Views
Last Modified: 2008-09-20
I have created a stored procedure in SQL Server 2005 that queries data and creates a .csv file.  My client needs a blank space inserted after a group of companies.  I was told to use a while loop instead of a cursor.
0
Comment
Question by:ifaber601
  • 4
  • 3
9 Comments
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20376256
Could you post some of your stored procedure, mainly the section(s) that create/write to the file.
0
 

Author Comment

by:ifaber601
ID: 20376615
Here's the basics of what the stored procedure does.  Usually there is a company with many items.  Will need to group by company and when it goes to the next company, a blank row needs to be inserted before going to the next company.

For example,

Disney  1
Disney  2
Disney 3

Starbucks 21
Starbucks 199

Target 1
Target 14
Target 17


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go
 

ALTER Procedure [dbo].[TestReport] AS
 

set transaction isolation level read uncommitted 

set nocount on

set concat_null_yields_null off
 

------------------------------------------------------------------------------------------

Create table #TestReport (CompanyID varchar(20), CompanyName varchar(100), Items varchar(20))

insert into #TestReport (CompanyID, CompanyName, Items)
 

select  

    CompanyID, 

    CompanyName,

    Items

	from company c 

    join items i on i.companyid= c.companyid     

order by Company, ItemNumber
 

-------------------------------------------------------------------------------------------

create table #TestReport_Rpt (ID int identity(1,1),ReportOutput varchar(1000))	

   insert into #TestReport_Rpt (ReportOutput) 

   values ('CompanyID, CompanyName, Items')

   Insert into #TestReport_Rpt (ReportOutput)

		Select	--distinct

            cast(CompanyID as varchar(20)) + char(44) +

            cast(CompanyName as varchar(100)) + char(44) +

            cast(Items as varchar(20)) 

		from #TestReport 
 

select ID, ReportOutput from #TestReport_Rpt order by id
 

drop table #TestReport

drop table #TestReport_Rpt

Open in new window

0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20377038
Ok, but what is actuallly writing out to the file?
0
 

Author Comment

by:ifaber601
ID: 20377258
The file writes out a comma delimited file as .csv.

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20377385
Right, but I mean the piece of code you have shown above that basically builds the data you output, but there's not actually anything that does the file write...
0
 

Author Comment

by:ifaber601
ID: 20377421
The process the actually writes the file is huge.  That's a total separate entity.  The select statement at the bottom is picked up and then a bcp command is used to output the data.  There is a lot of reports that runs through this big process.

I wanted to know in the select statement, if a blank row could be inserted after the company name changes.
0
 

Author Comment

by:ifaber601
ID: 20377768
I decided that I would just create the first temp table.  Then after it's created, loop through the temp table looking for the change in the company names, and when there is a change, insert a blank row before the new company.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22530268
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Having clause with Case 2 32
Stored procedure query with if 27 36
T-SQL to Update Table Dynamically 2 41
Isolation level in SQL server 3 47
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now