July 29, 2011 at 7:19 am
this is the problem:
I have a table # temp with 12000 records
I execute a tructe on the table dbo.__test
I execute a insert the records in the table #temp to dbo.__test.
I open a cursor
and inside I run this query
SELECT D.emittente as Emittente, D.ID as IDEmittente, @elemento as Elemento, KEY_TBL.RANK as rk
FROM dbo.__test D INNER JOIN
FREETEXTTABLE(dbo.__test , emittente, @search, LANGUAGE 'English', 5 ) AS KEY_TBL
ON D.ID = KEY_TBL.
ORDER BY KEY_TBL.RANK desc;
the problem that the query returns nothing.
If I run the query with the table dbo.__test with existing data the query returns the data correctly.
I truncate and insert the action has not yet released the lock and for this reason, the query fails.
How can I do to make consostenti data after the insert?
thanks
July 29, 2011 at 7:35 am
Did you name the table yourself? Can you fix it? Using special characters as the first "letter" of a table name is a really bad idea. In fact, SQL Server discourages it and forces you to use square brackets to get around it.
Back to your original question, I'm not sure I understand it correctly.
You truncate your test table, then run a cursor to insert data into the test table with data from the test table. You're not using the temp table in this query, which is the table that has the data. You're trying to insert data from an empty table into an empty table. Correct?
July 29, 2011 at 9:18 am
I have explained myself badly, here is the full sample
CREATE TABLE [dbo].[test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[emittente] [nvarchar](100) NULL,
CONSTRAINT [PK___test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- 1 GO PROCEDURE
truncate table [dbo].[test]
--drop table #temp
insert into [dbo].[test]([emittente])
values
('mario'),
('giovanni'),
('angelo'),
('davide'),
('giacomo')
-- 2 GO PROCEDURE
create table #temp
(
name varchar(50)
)
insert into #temp(name)
values ('mario'),('giacomo')
declare @name as varchar(50)
DECLARE _cursor CURSOR READ_ONLY FOR
SELECT name FROM #temp
OPEN _cursor;
FETCH NEXT FROM _cursor
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT T.[emittente], KEY_TBL.RANK as rk
FROM [dbo].[test] T INNER JOIN
FREETEXTTABLE([dbo].[test],[emittente],@name, LANGUAGE 'English', 5 ) AS KEY_TBL
ON T.ID = KEY_TBL.
FETCH NEXT FROM _cursor
INTO @name
END
CLOSE _cursor;
DEALLOCATE _cursor;
SELECT * FROM test
Create table and run the procedure -- 1 GO PROCEDURE
and the next run procedure -- 2 GO PROCEDURE
you'll see the difference in the result, on what is due?
2.thank you
August 25, 2011 at 9:16 am
Sorry it's been so long since I responded to this. I've been busy at work.
I'm looking at your cursor and a little confused at what exactly you're trying to do with it. Could you explain in English (not pseudo code) the purpose of your code and why exactly you need a cursor to do what you need to do?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply