Trying to avoid a cursor - SQL Server Forums

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)

Trying to avoid a cursor

Author

Topic

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-10-08 : 12:05:07

I am writing some T-SQL code to copy some data from a data back into itself. This is easy to do except that any data in a child table represented by a foreign key need to be copied as well. Is there anyway to do this without using a cursor.


CREATE TABLE Workspaces (
	WORKSPACE_ID int IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	TITLE varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	ACTIVE bit NOT NULL 
)

CREATE TABLE WorkspacePages (
	PAGE_ID int IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	WORKSPACE_ID int NOT NULL ,
	PAGE_NUMBER int NOT NULL ,
	ACTIVE bit NOT NULL 
)

CREATE TABLE WorkspacePageComponents (
	INSTANCE_ID int IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	PAGE_ID int NOT NULL ,
	TITLE varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-08 : 12:30:16

The copy to WorkSpacePages is easy
insert WorkspacePages (WORKSPACE_ID, PAGE_NUMBER, ACTIVE)
select 4, PAGE_NUMBER, ACTIVE
from WorkspacePages where WORKSPACE_ID = 1

Now to WorkspacePageComponents

insert WorkspacePageComponents (PAGE_ID, TITLE)
select w1.PAGE_ID, c1.TITLE
from WorkSpacePages w1, WorkSpacePages w2, WorkspacePageComponents c1
where w1.WORKSPACE_ID = 4 and w2.WORKSPACE_ID = 1
and w1.PAGE_NUMBER = w2.PAGE_NUMBER
and c1.PAGE_ID = w2.PAGE_ID

This only works (if it does) if WORKSPACE_ID, PAGE_NUMBER is unique on WorkSpacePages
If not you will have to use a temp table to find the IDs.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-10-08 : 12:37:59

it wont work that way. You cannot specify 4 on an identity field, since it is auto-generated.

I think I will have to go with a cursor implementation since I will need to know the new value of the workspaceID before I start copying over the workspace pages.

Got SQL?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-08 : 12:56:29

Oops - thought you'd already done that bit.

declare @id int, @oldid int
select @oldid = 1

insert Workspaces (TITLE, ACTIVE)
select TITLE, ACTIVE
from Workspaces
where WORKSPACE_ID = @oldid

select @id = @@identity

insert WorkspacePages (WORKSPACE_ID, PAGE_NUMBER, ACTIVE)
select @id, PAGE_NUMBER, ACTIVE
from WorkspacePages where WORKSPACE_ID = @oldid

Now to WorkspacePageComponents

insert WorkspacePageComponents (PAGE_ID, TITLE)
select w1.PAGE_ID, c1.TITLE
from WorkSpacePages w1, WorkSpacePages w2, WorkspacePageComponents c1
where w1.WORKSPACE_ID = @id and w2.WORKSPACE_ID = @oldid
and w1.PAGE_NUMBER = w2.PAGE_NUMBER
and c1.PAGE_ID = w2.PAGE_ID

This only works (if it does) if WORKSPACE_ID, PAGE_NUMBER is unique on WorkSpacePages
If not you will have to use a temp table to find the IDs.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources