January 28, 2009 at 6:33 am
the code run without error but not insert any help please
set nocount on
create table #DISTINCTC(
[name] nvarchar(120)
)
insert into #DISTINCTC SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'AGEN'
------------------------
--select * from #DISTINCTC
declare Dist cursor for
select [name]from #DISTINCTC
open Dist
DECLARE @z int
declare @y nvarchar(120)
declare @SQLString nvarchar(4000)
fetch next from Dist into @y
while @@fetch_status=0
begin
SET @SQLString = N'select count(distinct('+@y+N')) from AGEN'
EXEC sp_executesql @SQLString, N'@n int OUTPUT', @z OUTPUT
if ((select @z)<= 10)
begin
create table #DISTINCTC1( DistinctVal nvarchar(4000))
insert into #DISTINCTC1 EXEC('select distinct '+@y+' from AGEN')
select * from #DISTINCTC1
declare Insertion cursor for select * from #DISTINCTC1
open Insertion
Declare @I nvarchar(4000)
FETCH NEXT FROM Insertion INTO @I
WHILE @@FETCH_STATUS = 0
begin
insert into SATIS values('AGEN',@y,@I)
FETCH NEXT FROM Insertion INTO @I
end
close Insertion
Deallocate Insertions
end
fetch next from Dist into @y
end
close Dist
Deallocate Dist
drop table #DISTINCTC1
drop table #DISTINCTC
January 28, 2009 at 7:37 am
My guess is that there's no table called "AGEN." If that's only part of the table name, then try something like this...
Insert Into #DISTINCTC
Select Column_Name
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name Like '%AGEN%';
You can also interrogate tables more easily by using sys.tables:
Select *
From sys.tables
Where name Like '%AGEN%';
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
January 28, 2009 at 7:49 am
i think the error is here:
the select into would insert a column name dColumn_name:
insert into #DISTINCTC SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'AGEN'
------------------------
--select * from #DISTINCTC
the cursor looks for a column named name
declare Dist cursor for
select [name]from #DISTINCTC
try changing the cursor to declare Dist cursor for
select [Column_Name] from #DISTINCTC
Lowell
January 28, 2009 at 8:19 am
Lowell (1/28/2009)
i think the error is here:the select into would insert a column name dColumn_name:
insert into #DISTINCTC SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'AGEN'
------------------------
--select * from #DISTINCTC
the cursor looks for a column named name
declare Dist cursor for
select [name]from #DISTINCTC
try changing the cursor to declare Dist cursor for
select [Column_Name] from #DISTINCTC
I think the problem is invalid table name.
When I tried this script with personal table and it worked fine for me.
January 28, 2009 at 8:55 am
1. You failed to copy and paste correctly from the following
http://www.sqlservercentral.com/Forums/Topic644782-1291-1.aspx#bm644794
2. if ((select @z) <= 10) should be if (@z <= 10)
3. Why bother with cursors? Try something like:
DECLARE @SQLString varchar(8000)
    ,@CRLF char(2)
SELECT @SQLString = ''
    ,@CRLF = CHAR(13) + CHAR(10)
    
SELECT @SQLString = @SQLString
    + 'select distinct ''' + Table_Name + ''', ''' + Column_Name + ''''
        + ', cast(' + Column_Name + ' as nvarchar(4000))' + @CRLF
    + 'from Agen' + @CRLF
    + 'where (select count(distinct ' + Column_Name + ') from Agen) <= 10' + @CRLF
    + 'union all' + @CRLF
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name = 'Agen'
SET @SQLString = LEFT(@SQLString, LEN(@SQLString) - 13)
--PRINT @SQLString
INSERT INTO Satis
EXEC(@SQLString)
January 28, 2009 at 9:09 am
I agree with Ken, you should try to avoid cursors whenever possible. Also, I suggest the use of sys.columns instead of information_schema.columns. Querying the catalog views is the recommended and preferred method for accessing metadata in SQL Server.
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply