Building Insert stmt from passed data

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

Transact-SQL (2000)

Building Insert stmt from passed data

Author

Topic

dtsig
Starting Member

5 Posts

Posted - 2005-02-28 : 14:25:31

I am having to build a sp to parse data passed and create an insert. It is not possible to use bulkcopy or any other 'import' as it is on a constant basis (until i can get the rest of their system over).

I am being passed 2 params .. the first is the ID and the second is a large string (upto 8k) which contains a delimited 'record'. For instance it might be TAB delimited.

My basic idea is to loop through the string using CHARINDEX to find the nTH position and then use SUBSTRING to extract the 'field' of data.

Does this make sense (not the orginal problem .. it is a given)? Would there be a better / faster way? Possibly CHARINDEX, LEFT, REPLACE combination.

Ideas would be appreciated as I work through this.

Thanks
DSig

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-28 : 16:33:16

You could mod this to accomodate for TAB etc iof only comma:
[url]http://sqlteam.com/forums/topic.asp?TOPIC_ID=46188[/url]

rockmoose

dtsig
Starting Member

5 Posts

Posted - 2005-03-01 : 11:27:01

While waiting for a reply .. I attempted this as follows ..

CREATE procedure dbo.sp_tempwrite @id varchar(10), @record varchar(8000)
as

declare @sqlInsert varchar(8000) -- big sql string building
declare @colValue varchar(5000) -- current @record value
declare @maxCols int -- we should have this many fields/columns
declare @fm char(1)
declare @pos int
declare @temprecord varchar(8000)
declare @rtnErrorCode int
declare @rtnErrorTxt varchar(8000)

set @rtnErrorTxt = ''
set @rtnErrorCode = 0
set @maxCols=300
set @sqlInsert = "insert into how_new values('" + @id + "'"

print @sqlInsert

set @fm = ','
--set @fm = CHAR(254)

WHILE (CHARINDEX(@fm, @record) > 0) OR @record <> ''
BEGIN
set @pos = CHARINDEX(@fm, @record)
if @pos > 0
begin
set @colValue=LEFT(@record, @pos-1)
-- now remove the 'field'
set @temprecord = STUFF(@record, 1, @pos, '')
set @record = @temprecord
end
else
begin
set @colValue=@record
set @record = '' -- it was the last bit ..
end

set @sqlInsert = @sqlInsert + ",'" + @colValue + "'"
print @sqlInsert

END

set @sqlInsert = @sqlInsert + ")"
print @sqlInsert
exec @sqlInsert

if @@ERROR <> 0
begin
set @rtnErrorCode = @@ERROR
set @rtnErrorTxt = @rtnErrorCode + @sqlInsert

print @rtnErrorTxt
end

--RETURN @rtnErrorCode
GO

If i attempt "exec sp_tempWrite '1234','this is the big one'" i get the following ..

insert into how_new values('1234'
insert into how_new values('1234','this is the big one'
insert into how_new values('1234','this is the big one')
Server: Msg 2812, Level 16, State 62, Line 50
Could not find stored procedure 'insert into how_new values('1234','this is the big one')'.
Server: Msg 245, Level 16, State 1, Procedure sp_tempwrite, Line 55
Syntax error converting the varchar value 'insert into how_new values('1234','this is the big one')' to a column of data type int.

it seems to build correctly (two columns .. no comma data). But then the execute of the 'insert' causes and error. Not sure why

Any ideas ..

dtsig
Starting Member

5 Posts

Posted - 2005-03-01 : 16:09:59

it turns out that this line

exec @sqlInsert

needs to be changed to

exec (@sqlInsert)

Something about it being a string? Now just to finish up the routine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources