August 11, 2009 at 4:16 pm
Hi:
I've a stored procdure which inserting data from one table to another table in my db.
I use cursor for that. I read several articles where writes adviced not to use cursor.
Then what would be the best way to do the inserting job?
My code is:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Add_TagName]
--@fiscalYear int
as
Declare
C1 CURSOR READ_ONLY
FOR
SELECT distinct AlertName,AlertPriority from SiteDataTemp where
AlertName not in(select TagName from [dbo].[TagDetails] )
Declare
@TagNameID int,
@TagName nvarchar(255),
@SeverityID int
OPEN C1
FETCH NEXT FROM C1
INTO @TagName,@SeverityID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TagNameID=isnull(MAX(TagNameID),0)+1 from TagNameTemp
insert into TagTemp(TagNameID,TagName,SeverityID)
values (@TagNameID,@TagName,@SeverityID)
FETCH NEXT FROM C1
INTO @TagName,@SeverityID
END
CLOSE C1
DEALLOCATE C1
Thanks in advance
Maksuda
August 11, 2009 at 10:07 pm
You could do something like...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Add_TagName]
--@fiscalYear int
as
CREATE TABLE #Sites
(SitesIDINT IDENTITY (1, 1)
, AlertNameVARCHAR (30) -- or whatever is the correct datatype
,AlertPriorityVARCHAR (30)) -- or whatever is the correct datatype
INSERT INTO #Sites
SELECT distinct AlertName,AlertPriority from SiteDataTemp where
AlertName not in(select TagName from [dbo].[TagDetails] )
Declare @TagNameID int
SELECT @TagNameID=isnull(MAX(TagNameID),0) from TagNameTemp
INSERT INTO TagTemp (TagNameID,TagName,SeverityID)
SELECT @TagNameID + SitesID, AlertName, AlertPriority
FROM #Sites
August 11, 2009 at 10:17 pm
You could do something like...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Add_TagName]
--@fiscalYear int
as
CREATE TABLE #Sites
(SitesIDINT IDENTITY (1, 1)
, AlertNameVARCHAR (30) -- or whatever is the correct datatype
,AlertPriorityVARCHAR (30)) -- or whatever is the correct datatype
INSERT INTO #Sites
SELECT distinct AlertName,AlertPriority from SiteDataTemp where
AlertName not in(select TagName from [dbo].[TagDetails] )
Declare @TagNameID int
SELECT @TagNameID=isnull(MAX(TagNameID),0) from TagNameTemp
INSERT INTO TagTemp (TagNameID,TagName,SeverityID)
SELECT @TagNameID + SitesID, AlertName, AlertPriority
FROM #Sites
August 12, 2009 at 9:59 am
Here is another option using Row_Number() instead of the Identity Column. This means that you don't need the DDL language to create the temp table.
Declare @TagNameIDBase int
SELECT @TagNameID=isnull(MAX(TagNameID),0) from TagNameTemp;
WITH Tags AS (
SELECT distinct AlertName,AlertPriority from SiteDataTemp
WHERE AlertName NOT IN (SELECT TagName FROM [dbo].[TagDetails] )
)
INSERT INTO TagTemp (TagNameID,TagName,SeverityID)
SELECT @TagNameIDBase + Row_Number() Over( ORDER BY AlertName), AlertName, AlertPriority
FROM Tags
This code is untested, because the OP did not provide DDL for the tables.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 12, 2009 at 12:19 pm
Drew I tried your code. It's fine and working ok. Just need to make a small change.
In place of insert statement
INSERT INTO TagTemp (TagNameID,TagName,SeverityID)
SELECT @TagNameIDBase + Row_Number() Over( ORDER BY AlertName), AlertName, AlertPriority
FROM Tags
I modified it -
INSERT INTO TagTemp (TagNameID,TagName,SeverityID)
SELECT @TagNameID+ Row_Number() Over( ORDER BY AlertName), AlertName, AlertPriority
FROM Tags
(instead of @TagNameIDBase I use @TagNameID- as null value is not acceptable in table)
Other than this everything is fine and working faster.
Thanks for the help brother.
Maksuda
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply