why the insert statement not work

Question

Post reply

why the insert statement not work

Ali.M.Habib

SSC Eights!

Points: 930
More actions
January 28, 2009 at 6:33 am

#205895

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Michelle Ufford-459603 SSCommitted Points: 1642 More actions · Answer 1

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

Lowell SSC Guru Points: 323482 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 3

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.

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Ken McKelvey SSCoach Points: 18484 More actions · Answer 4

1. You failed to copy and paste correctly from the following

http://qa.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)

&nbsp&nbsp&nbsp&nbsp,@CRLF char(2)

SELECT @SQLString = ''

&nbsp&nbsp&nbsp&nbsp,@CRLF = CHAR(13) + CHAR(10)

&nbsp&nbsp&nbsp&nbsp

SELECT @SQLString = @SQLString

&nbsp&nbsp&nbsp&nbsp+ 'select distinct ''' + Table_Name + ''', ''' + Column_Name + ''''

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp+ ', cast(' + Column_Name + ' as nvarchar(4000))' + @CRLF

&nbsp&nbsp&nbsp&nbsp+ 'from Agen' + @CRLF

&nbsp&nbsp&nbsp&nbsp+ 'where (select count(distinct ' + Column_Name + ') from Agen) <= 10' + @CRLF

&nbsp&nbsp&nbsp&nbsp+ '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)

Michelle Ufford-459603 SSCommitted Points: 1642 More actions · Answer 5

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