copy ntext from one table to another

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)

copy ntext from one table to another

Author

Topic

Razzmatazz
Starting Member

7 Posts

Posted - 2005-10-11 : 12:43:44

I'm currently working on a new version of a system. This new version has a largely altered datamodel. Using batch scripts I create the new tables, fill them with the values of the ald tables and then delete the old tables.

This is all working fine, except for one part, copying ntext fields.

Here is a part of the code I use, description is the ntext field


DECLARE cur_roomtype CURSOR FAST_FORWARD FOR SELECT roomtype_id FROM [dbo].[RoomType]
OPEN cur_roomtype
FETCH NEXT FROM cur_roomtype INTO @roomtype_id
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @hotel_id = ( SELECT hotel_id FROM [dbo].[RoomType] WHERE roomtype_id = @roomtype_id )
	SET @code = ( SELECT code FROM [dbo].[RoomType] WHERE roomtype_id = @roomtype_id )
	SET @tmpsmallint = ( SELECT capacity FROM [dbo].[RoomType] WHERE roomtype_id = @roomtype_id )

	INSERT INTO [dbo].[PhysicalUnit] (hotel_id,code,capacity,fixavailabilityon_datestamp)
		VALUES (@hotel_id,@code,@tmpsmallint,0)
	SET @id = @@IDENTITY

	INSERT INTO [dbo].[Unit] (unitcode,hotel_id,physicalunit_id,description)
		VALUES (@code,@hotel_id,@id,' ')
		
	SELECT @ptrfrom = TEXTPTR(description)
		FROM [dbo].[RoomType] WHERE roomtype_id = @roomtype_id
	SELECT @ptrto = TEXTPTR(description)
		FROM [dbo].[Unit] WHERE unitcode=@code AND hotel_id=@hotel_id
		
	WRITETEXT [dbo].[Unit].description @ptrto @ptrfrom

	FETCH NEXT FROM cur_roomtype INTO @roomtype_id
END
CLOSE cur_roomtype
DEALLOCATE cur_roomtype

It breaks down at the WRITETEXT statement. Of course this doesn't work correctly. The @ptrfrom is a pointer, so the description field is filled with some unreadable characters, and thats it. Sadly this problem is hard to google if you have no cleu how to solve it. I'm at a loss.

I would greatly appreciate any help.

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 13:43:44

I haven't looked closely at what you've posted, so sorry if this is a naive question, but can't you do some

INSERT INTO NewTable
SELECT *
FROM OldTable

type stuff to avoid having to use CURSORs?

Maybe you will have to INSERT into a temporary table first, to be able to allocate ID numbers for hotel_id and then use that ID number in some other INSERTs, but the CURSOR stuff looks jolly convoluted, and I expect its pretty slow too - if you have lots of rows to process.

Kristen

Razzmatazz
Starting Member

7 Posts

Posted - 2005-10-11 : 14:37:10

Performance is not really an issue for me, as this is ultimately a one-off. I only have to do this conversion once and I don't really case if it takes 15 seconds or 5 minutes.

The problem is that this database has about 50 heavilly related tables, and I have to keep track af all the old an new identity values. Using cursors is easy and saves me a lot in development time. I might be able to do it with full table inserts, but I have a feeling I am facing a lot of heavily joined / subqueried selects. I'm not really looking forward to that...

But if I have to, I have to.

Razzmatazz
Starting Member

7 Posts

Posted - 2005-10-12 : 05:10:37

I've come to a new conclusion.

The tricky part about the inserting lots of rows at once into a table with an identity field is making temporory tables linking the old and the new identities. I have to have these tables to fill in the other tables. I thougth these would be very difficult to generate.

So I took another good hard look at the datamodel, and I found out that all tables with identity fields also have another couple of fields with a unique constraint. So I can join the new and the old tables explicitly, and I can make a temporory table containing the ald and new values.

Still a lot of tricky work, but I think I'll manage.

Kristen
Test

22859 Posts

Posted - 2005-10-12 : 15:10:44

When I've come unstuck like this in the past I've tended to do something like:


SELECT MyPK,
       CONVERT(int, NULL) AS [hotel_id],
       CONVERT(int, NULL) AS [MyOtherID],
       ...
INTO #MyTempTable
FROM MyRealTable

and then done a bunch of updates to store the appropriate IDs as they are allocated.

Then when I need to reference those IDs I can just JOIN the #MyTempTable table as appropriate. (Store any additional column data you need to enable it to be useful in joining to anything necessary)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources