Database

12-Oct-10 21:42

Maybe I have been at it too long today, but am I missing something simple here?

DECLARE @SomeLongName VarChar(100) = 'Some long text'
DECLARE @temp TABLE(
    someCode VarChar(10)
    )

DECLARE @Sql VarChar(1000) =
        'INSERT @temp SELECT MAX(SomeCode) ' +
        'FROM MyTable ' +
        'WHERE [Name] LIKE''' + LEFT(REPLACE( @SomeLongName, ' ', ''), 3) + '%'''

EXEC (@Sql)

I get 'must declare the table variable @temp' when I try to run this?

Please put me out of my misery... $Shucks | :-\$

(edit: Small typo in Sql)

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

modified on Wednesday, October 13, 2010 3:52 AM

Blue_Boy12-Oct-10 22:11

12-Oct-10 22:11

This way should works:

DECLARE @SomeLongName VARCHAR(100) 
SET @SomeLongName = 'Some long text'        
CREATE TABLE #temp
(
	someCode VARCHAR(500)
)                
DECLARE @Sql VARCHAR(1000) 
SET @Sql = 'INSERT into #temp SELECT MAX(SomeCode) FROM MyTable WHERE [Name] LIKE ' +
    
    CHAR(39) + LEFT(REPLACE(@SomeLongName, ' ', ''), 3) + ' %' + CHAR(39) + 
    ' select * from #temp drop table #temp'

EXEC (@Sql)

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Andy_L_J12-Oct-10 22:37

12-Oct-10 22:37

My sinvere thanks. Thumbs Up | :thumbsup:

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Blue_Boy12-Oct-10 22:41

12-Oct-10 22:41

You are welcome Smile | :)

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Andy_L_J12-Oct-10 23:23

12-Oct-10 23:23

Now, what if i want to do this in a UDF? (Can't use temporary tables in a UDF Frown | :(

)

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Mycroft Holmes13-Oct-10 0:58

Mycroft Holmes

13-Oct-10 0:58

I think you'll find you can't use dynamic sql in a UDF so the temp table is moot.

As the boy was useful it is polite to up vote his answer [edit] I must have missed the vote [\edit].

Never underestimate the power of human stupidity
RAH

Corporal Agarn13-Oct-10 9:44

Corporal Agarn

13-Oct-10 9:44

Because you are using the execute the temporary table needs to be in the database tempdb.

I have even had to use CREATE TABLE ##Temp... to make an execute work.

Andy_L_J13-Oct-10 9:48

13-Oct-10 9:48

This works fine in a SSMS query - it wont work in a UDF because of the dynamic sql.

I think I may have to look at using a trigger or something...as I want to use the result to create a field when a new row is added to a table.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

sql novice: how to find only duplicates

www.Developerof.NET17-Oct-10 9:17

www.Developerof.NET

17-Oct-10 9:17

This one does execute.

DECLARE @SomeLongName VarChar(100) = 'Some long text'    
               
    DECLARE @Sql VarChar(1000) 
    SET @Sql='DECLARE @temp TABLE( someCode VarChar(10) )' 
	SET @Sql=@Sql+
            'INSERT @temp SELECT MAX(SomeCode) ' +
            'FROM MyTable ' +
            'WHERE [Name] LIKE''' + LEFT(REPLACE( @SomeLongName, ' ', ''), 3) + '%'''
               
    EXEC (@Sql)

The problem here is the scope in which the sql statement executes. The @temp table variable declared is different from the @temp variable used in the inline query as the scope of both the queries are different. To make the query work we need to run the query in same scope ie declare the table variable using the same SQL satements as one used for the query.

When you fail to plan, you are planning to fail.

PJ Arends12-Oct-10 18:55

12-Oct-10 18:55

SELECT Col1, Col2, Col3 FROM MyTable ORDER BY Col1, Col2, Col3;

6582 row(s) affected

SELECT DISTINCT Col1, Col2, Col3 FROM MyTable ORDER BY Col1, Col2, Col3;

6473 rows(s) affected

How can I get just the 109 rows that are duplicates? ie: the opposite of DISTINCT?

Thanks

You may be right
I may be crazy
-- Billy Joel --

Within you lies the power for good - Use it!

Blue_Boy12-Oct-10 20:35

12-Oct-10 20:35

Try to union all by three selects like:

select distinct col1 from mytable
union all 
select distinct col2 from mytable
union all 
select distinct col3 from mytable

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

PJ Arends13-Oct-10 6:07

13-Oct-10 6:07

Thanks for the help, but unfortunately this does not give me the results I am looking for.

You may be right
I may be crazy
-- Billy Joel --

Within you lies the power for good - Use it!

J4amieC12-Oct-10 21:59

J4amieC

12-Oct-10 21:59

You can do this using a Group By clause

SELECT Col1,Col2,Col3, COUNT(*) as DuplicateCount
FROM MyTable
GROUP BY Col1,Col2,Col3
ORDER BY DuplicateCount DESC

At this point you will have all the records, with the duplicates (Those with a count>1) at the beginning of the list.

Interestingly, you can reduce this to JUST the duplicates by replacing the ORDER BY with a HAVING clause

SELECT Col1,Col2,Col3, COUNT(*) as DuplicateCount
FROM MyTable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1

Now you only have the duplicates.

PJ Arends13-Oct-10 6:08

13-Oct-10 6:08

Thanks, this is exactly what I am looking for.

You may be right
I may be crazy
-- Billy Joel --

Within you lies the power for good - Use it!

Mycroft Holmes13-Oct-10 1:01

Mycroft Holmes

13-Oct-10 1:01

You're next question will be how do I remove the duplicates?

Look into row_number() and partition over in BOL

Never underestimate the power of human stupidity
RAH

PJ Arends13-Oct-10 6:11

modify a table scheme [modified]

13-Oct-10 6:11

Actually not. Using the results I got using J4amieC's answer I am going to try and figure out which other column in the table will make these rows unique.

You may be right
I may be crazy
-- Billy Joel --

Within you lies the power for good - Use it!

genieabdo12-Oct-10 1:15

genieabdo

12-Oct-10 1:15

hi i'm lookin for changing a scheme of my table
who can i do it

I already have a table with 20,000 record I created a new partition with a new shceme I want applied to my table

thanks

modified on Tuesday, October 12, 2010 10:26 AM

Blue_Boy12-Oct-10 2:11

12-Oct-10 2:11

Use ALTER TABLE[^]

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

genieabdo12-Oct-10 2:25

genieabdo

12-Oct-10 2:25

i did'n fing a alter table for changing scheme the partition of table

Gerben Jongerius12-Oct-10 3:13

Gerben Jongerius

12-Oct-10 3:13

You can't, that is to say you could create a new instance in the schema and then populate it. But you won't copy keys, indexes or constraints. The query below would do that.

insert into newscheme.dbo.mytable
  select * from oldscheme.dbo.mytable

genieabdo12-Oct-10 4:26

genieabdo

12-Oct-10 4:26

I already have a table with 20,000 record I created a new partition with a new shceme I want applied to my table