[RESOLVED] Select Into ... help ..

[GremlinSA]

Okay you Sql Fundi's .. i have another question to ask...

We need a Search function to return all records matching the search string.. One catch.. the search is run every time one character is entered, and a autoComplete function is done, however ALL matching results are shown ..

select ConsumerSurname +', ' +ConsumerName + ' [' + AccountNo + ']' ConsumerName ,
ConsumerSurname,
ConsumerID,
AccountNo
into #TmpCust001
FROM [CF3_Consumer]
WHERE ConsumerSurname like '%' + @SearchStr +'%'
ORDER BY ConsumerSurname
if Object_ID('TempDb..#TmpCust001') is not NULL --Then the customer/s not found at all
begin
if exists(select * from #TmpCust001)
begin
select * from #TmpCust001
drop table [dbo].[#TmpCust001]
end else
'....... etc
However this will only return records where the surname matches, and not the name.. if I use the folowing for my where clause WHERE ConsumerSurname like '%' + @SearchStr +'%' or ConsumerName like '%' + @SearchStr +'%'The autocomplete function does not work on names ..

So i was looking into doing select ConsumerSurname +', ' +ConsumerName + ' [' + AccountNo + ']' ConsumerName ,
ConsumerSurname,
ConsumerID,
AccountNo
into #TmpCust001
FROM [CF3_Consumer]
WHERE ConsumerSurname like '%' + @SearchStr +'%'
ORDER BY ConsumerSurname

select ConsumerName +', ' +ConsumerSurName + ' [' + AccountNo + ']' ConsumerName ,
ConsumerSurname,
ConsumerID,
AccountNo
into #TmpCust001
FROM [CF3_Consumer]
WHERE Consumername like '%' + @SearchStr +'%'
ORDER BY ConsumernameHowever the second Into #tmpCust001 is not accepted, so how can i add a second Select into a Temporary table .. ????

Thanks...

Gremmy..

[HairyMonkeyMan]

Do you have line terminators in your code?

i.e
select ConsumerSurname +', ' +ConsumerName + ' [' + AccountNo + ']' ConsumerName ,
ConsumerSurname,
ConsumerID,
AccountNo
into #TmpCust001
FROM [CF3_Consumer]
WHERE ConsumerSurname like '%' + @SearchStr +'%'
ORDER BY ConsumerSurname;

select ConsumerName +', ' +ConsumerSurName + ' [' + AccountNo + ']' ConsumerName ,
ConsumerSurname,
ConsumerID,
AccountNo
into #TmpCust001
FROM [CF3_Consumer]
WHERE Consumername like '%' + @SearchStr +'%'
ORDER BY Consumername;

HTH

[GremlinSA]

Sorry i forgot to put what the acctual error was ... It tells me that "There is already an object named '#TmpCust001' in the database." ...
If i change the second Into to #TmpCust001 then Sql will accept it ...

Ahh and it is MS Sql 2005 .... (forgot to mention that too)

Do you have line terminators in your code?Thanks.. I'm using Begin End blocks and the line terminators did nothing to help...

[GremlinSA]

Okay after a lot of searching and testing i can confidently say i've found the best way to do this one.

The idea was to have the results from two slightly different Selects returned in a single dataset.

And I managed to find it ...

if Object_ID('results') is not NULL
begin
drop table [dbo].[results]
end
CREATE TABLE dbo.Results
( ConsumerName varchar(150) NOT NULL,
ConsumerID Int NOT NULL,
AccountNo varchar(20) NOT NULL
);

insert results
select ConsumerSurname +', ' +ConsumerName + ' [' + AccountNo + ']' ConsumerName ,
ConsumerID,
AccountNo
FROM [CF3_Consumer]
WHERE ConsumerSurname like @SearchStr +'%'
ORDER BY ConsumerSurname;

insert results
select ConsumerName +' ' +ConsumerSurname + ' [' + AccountNo + ']' ConsumerName ,
ConsumerID,
AccountNo
FROM [CF3_Consumer]
WHERE ConsumerName like @SearchStr +'%'
ORDER BY ConsumerName;

if exists(select * from results)
begin
select * from results
drop table [dbo].[results]
end
I found that this gave me the best results without too much effort..

Thanks To all

Gremmy..