|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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.
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
i did'n fing a alter table for changing scheme the partition of table
|
|
|
|
|
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
|
|
|
|
|
I already have a table with 20,000 record I created a new partition with a new shceme I want applied to my table
|
|
|
|
|
Please try to add some further information, perhaps we can understand then what you are trying to do.
Which database do you use - Oracle?
What is that "partition" - a partition of your hard disk, a "table space", ...?
What is the "scheme" - is that the wording used by Oracle?
And finally, what do you mean with "applied to my table"? Do you want to copy/move your table to that scheme?
|
|
|
|
|
I am using MS Access 2007 and asp .net 2.0.When my application runs,an error occurs."Number of query srtings and destination values are not matched".I googled a lot.But didnt find any solution.when the data are inserted to the table,this error occured.I didnt use any primary key in table.
|
|
|
|
|
Is your situation is like this[^]
|
|
|
|
|
I have to call function to print numbers. For Example if I pass 5 as parameter function has to return
1
2
3
4
5
Is declaring Temp table and Get this result is good way? Is there any alternate
|
|
|
|
|
Here it is
DECLARE @InputValue INT
SET @InputValue=3
DECLARE @PrintValue INT
SET @PrintValue=1
WHILE @InputValue > 0
BEGIN
PRINT @PrintValue
SET @PrintValue=@PrintValue+1
SET @InputValue=@InputValue-1
END
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
|
|
|
|
|
That doesn't sound like a database question.
|
|
|
|
|
Suppose, I have an application which talks to a database in SQL/Oracle, and presents some set of data to the user.
If, for any reason, someone modifies the table structure in the original database, and the application tries to work on the database, there will be an error or incorrect data being displayed.
Is there any way to prompt the user that there was a modification happened on this database ,.. with these tables modified,.. and so on? Or can we write a windows service to do this kind of tracking a database?
|
|
|
|
|
How about this ?
Create a "version" table where you could store a database schema version number and have your application check that value when it starts up.
However, you would have to have an agreement between the folks that modify your database schema and the people that write your application that any changes to the database schema would cause the version number to increment.
Another way of doing this would be to have a procedure in your application startup that would verify each table for it's structure; if the structure doesn't match, then stop the application.
Something like:
Open Table A, Iterate through the fields, check their name, datatype, length, Allow Null or Not ...
What you describe is actually an indication of a bigger problem. No change control procedures. If there is a change to your database in a production enviornment, there has to be a checklist, including signoff that all parties involved are aware of the change. You can't have people making ad-hoc changes to a production system without proper procedures; it will always end up biting you in the end.
Good Luck. 
|
|
|
|
|
You could write a startup procedure in the application the user runs that verifies the known database structure against one hardcoded in the application. This can be done using the ODBC / JDBC, but you'll need to contact the application developer. For as far as I know most programming languages offer the capability to request the existing table structure of a database.
Another option is that all applications access the data through views. And after every schema change an automated check on database level is performed to see if all the views still work. If not you alter the views slightly, making sure they return the expected data. This way you will only have to change the applications when they need additional data from your database scheme.
However the true question becomes rather different, why are you expecting the schema of the database to change without an update to the programming of the various applications using it. As usually a change in the database is triggered by a change in one of the programs using the data in it?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
