|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
loop though records and CONCATENATE resultsi am writing stored procedure that will allow me to create a flat file
to be send to another company. in order to do this I need to write a query, create a cursor or temp table for each main record meeting criteria, then looping through child records and concatenate the data in one of these child records fields so i can output it to the flat file and meet the requirements of the file formatting. help will be greatly appreciated. thanks mdscorp wrote:
> i am writing stored procedure that will allow me to create a flat file Take a look at DTS (SQL Server 2000) or Integration Services (2005). A> to be send to another company. in order to do this I need to write a > query, create a cursor or temp table for each main record meeting > criteria, then looping through child records and concatenate the data > in one of these child records fields so i can output it to the flat > file and meet the requirements of the file formatting. > > help will be greatly appreciated. > > thanks T-SQL proc can't write directly to a file. You could write some CLR code to do it but DTS/SSIS seems like a more natural option. You could always invoke the package from the proc if that's essential to you. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- David Portas wrote:
Show quote > mdscorp wrote: I worte the code already to create and write to file from a SP using> > i am writing stored procedure that will allow me to create a flat file > > to be send to another company. in order to do this I need to write a > > query, create a cursor or temp table for each main record meeting > > criteria, then looping through child records and concatenate the data > > in one of these child records fields so i can output it to the flat > > file and meet the requirements of the file formatting. > > > > help will be greatly appreciated. > > > > thanks > > Take a look at DTS (SQL Server 2000) or Integration Services (2005). A > T-SQL proc can't write directly to a file. You could write some CLR > code to do it but DTS/SSIS seems like a more natural option. You could > always invoke the package from the proc if that's essential to you. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- t-sql, i don't know why are you saying that cannot be done. I am not having errors or problems i was only tryng to get some sample code of looping throughr ecords and concatenate the value of a field to get a formated output. If you really want/need to use t_SQL as a procedural language, here are some
tips. Create a #Temp table (or table variable -depending upon size), include a column RowID int IDENTITY. Declare the following variables: @CurrentRow int, @TotalRows int, @Value varchar(8000) to hold theconcatenated column Initialize @CurrentRow = 1 INSERT Your data to concatenate in the #Temp table Capture the @@ROWCOUNT in the @TotalRows Start a WHILE loop (@CurrentRow <= @TotalRows USE BEGIN-END block for the 'looped' steps Add the colunm to concatenate from the @Temp table where RowID = @CurrentRow the variable (You can concatenate chr(13)+chr(10) for a linefeed if needed.) Increment the @CurrentRow (loop) When the loop is finished, update the table with the @value -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "mdscorp" <l***@mds-corp.com> wrote in message news:1152824686.583193.195150@h48g2000cwc.googlegroups.com... > > David Portas wrote: >> mdscorp wrote: >> > i am writing stored procedure that will allow me to create a flat file >> > to be send to another company. in order to do this I need to write a >> > query, create a cursor or temp table for each main record meeting >> > criteria, then looping through child records and concatenate the data >> > in one of these child records fields so i can output it to the flat >> > file and meet the requirements of the file formatting. >> > >> > help will be greatly appreciated. >> > >> > thanks >> >> Take a look at DTS (SQL Server 2000) or Integration Services (2005). A >> T-SQL proc can't write directly to a file. You could write some CLR >> code to do it but DTS/SSIS seems like a more natural option. You could >> always invoke the package from the proc if that's essential to you. >> >> -- >> David Portas, SQL Server MVP >> >> Whenever possible please post enough code to reproduce your problem. >> Including CREATE TABLE and INSERT statements usually helps. >> State what version of SQL Server you are using and specify the content >> of any error messages. >> >> SQL Server Books Online: >> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx >> -- > > I worte the code already to create and write to file from a SP using > t-sql, i don't know why are you saying that cannot be done. > I am not having errors or problems i was only tryng to get some sample > code of looping throughr ecords and concatenate the value of a field to > get a formated output. > For concatenating the values in a column, you may check this link...
http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html mdscorp wrote:
> What I said was that there's no way to write a file from T-SQL (no> I worte the code already to create and write to file from a SP using > t-sql, i don't know why are you saying that cannot be done. > I am not having errors or problems i was only tryng to get some sample > code of looping throughr ecords and concatenate the value of a field to > get a formated output. documented method anyway). Just to be clear, you certainly could do it by other methods: either with CLR code or by executing DOS commands using xp_cmdshell or by using the sp_OA automation procs to call external code or maybe by creating an extended proc. For a variety of reasons I wouldn't generally recommend those methods but they are possible. The best answer to your question may be highly dependent on what means you are using to write to the file. If you are using a method that can utlise a result set for the output then it would be better to use a SELECT statement to concatenate the results rather than trying to loop through row by row. For example, you could use xp_cmdshell to call the BCP utility based on a query you specify. If you need more help then please explain how you are writing the file so that we can suggest some solutions. Hope this helps. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- In general, a recommended approach is to extract the resultset outside the
server and massage the data to appropriate display format using some client. Regarding the workarounds for doing it at the server, you can check out the following links: ( For SQL 2005 only ) http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/7e5b4c8a9b9b968a ( For SQL 2000 & 2005 ) http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e -- Anith |
|||||||||||||||||||||||
Other interesting topics