Showing posts with label inserting. Show all posts
Showing posts with label inserting. Show all posts

Wednesday, March 28, 2012

How to use variables as a Counter?

While inserting data into a target table, I'm trying to populate a primary key ID field sequentially. For each record, the value of the primary key field needs to be incremented by one (a counter).

I've tried to use the RowCount transformation to store the values in a variable. I'm able to successfully do that; however, I don't know how to read or update the variable incrementally.

If someone knows how to perform this task, please let me know. I would greatly appreciate it.

I think this: http://www.sqlis.com/default.aspx?37 should do what you need.


As will this: http://www.sqlis.com/default.aspx?93

They both do the same thing. One uses script, one is a custom component.

-Jamie

|||

I used the script component and it does exactly what I need it to do.

Thanks, Jamie.

Monday, March 26, 2012

How to use UPDATETEXT within the context of inserting a new row

All -

I'm trying to insert a row into a table. One of the columns in this table is a text field. I can't figure out how to use UPDATETEXT to insert data into this text field for a brand new row. The examples I see all require a text pointer to the column. However - I haven't even inserted the row - so how would I create a text pointer?

Here's the example from Books Online -

DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(pr_info) FROM pub_info pr, publishers p WHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @.ptrval 88 1 'b' GO

How would I create the text pointer (TEXTPTR) for a record that hasn't even been inserted yet?

Incidentally - my issues would be solved if the data for this text column was always less than 8K (then I could use varchar(8000) - but that isn't always the case.

Help!

I'm assuming that you're running SS2000.

UPDATETEXT operates on tables.

If the data you want to manipulate isn't in a table, where is it?

|||

Note that UPDATETEXT is going away in a future version of SQL Server. Use varchar(max) if this is a 2005 database and then use the .WRITE construct in the UPDATE statement.

Well, you can just use a text parameter to a stored procedure and just pass in all of the text... That is the simplest answer. You will have to create the row first, and create the text pointer:

drop table test
go
create table test
(
testId int,
value text
)
go
insert into test(testId)
values (1)
go

Update test
set value = 'foo'
where testId = 1
and value is null

DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(value)
FROM test
WHERE testId = 1


UPDATETEXT test.value @.ptrval null null 'b'
GO

select *
from test

Returns:

testId value

--

1 foob

|||Thanks so much - this seemed to work. And yes I am stuck with SQL Server 2000 right now. It seems to me so clunky to update text fields this way - so I'll be real happy once I migrate to 2005.

Thanks again for the help.