July 6, 2007 at 7:06 am
There is a column in a table which contains pipe delimited values. I want to manipulate these values individually, so I thought I would create a temp or work table to store the values and use a series of TSQL statements to extract the column to a text file then insert the values to this work table. Make sense?
I am able to do as I describe except that I have to manually edit the output file before I can bulk insert.
When I use the Query Analyzer to create the file, I'm stuck with text like "(4 rows affected)" at the bottom. This annoys the bulk insert--even if I tell it first row/last row. I've tried various switches on the command line using osql but I cannot supress that message.
So I ask how do you supress that "# rows affected" message? Also, I seem to end up with an empty first row, so I have to tell bulk insert FIRSTROW=2--can that be avoided?
Also, I may want to do this from a stored procedure--or even to do it in the QA--is there TSQL to output to a file from within a stored proc or the QA?
Thanks for any help.
Gina
July 6, 2007 at 7:28 am
To suppress the # rows affected message just include this before your query.
SET NOCOUNT ON
This will take care of the trailing message from the query results.
July 6, 2007 at 7:39 am
A better way would be to use a function to separate the text column by the delimeter so that you can avoid exporting directly.
CREATE FUNCTION dbo.FAQ_ListToSingleColumn
(
@cslist VARCHAR(8000)
, @delim char(1)
, @offset smallint
)
RETURNS varchar (255)
BEGIN
DECLARE @spot SMALLINT, @STR VARCHAR(8000), @sql VARCHAR(8000), @count int, @returnstr varchar(255)
set @count = 0
WHILE @cslist <> ''
BEGIN
set @count = @count + 1
SET @spot = CHARINDEX(@delim, @cslist)
IF @spot>0
BEGIN
SET @STR = LEFT(@cslist, @spot-1)
SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot)
END
ELSE
BEGIN
SET @STR = @cslist
SET @cslist = ''
END
if @count = @offset begin
set @returnstr = @STR
break
end
END
RETURN (@returnstr)
END
GO
select dbo.FAQ_ListToSingleColumn(@t,'|',3) will return the 3rd element of the string. You probably know how many different columns you will end up with so you could create statement to select all the columns:
select dbo.FAQ_ListToSingleColumn(@t,'|',1) as COL1
, dbo.FAQ_ListToSingleColumn(@t,'|',2) as COL2
, dbo.FAQ_ListToSingleColumn(@t,'|',3) as COL3
, dbo.FAQ_ListToSingleColumn(@t,'|',4) as COL4
...
from <table>
This would be a lot simpler and avoid problems of blank rows etc. I have not tested the function exhaustively so it may have a bug in it.
J
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply