|
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?
|
|
|
|
|
As has been pointed out, you have a fundamental problem with change control!
Both SQL Server and Oracle have system tables/views which will list all the objects (table and column names) in the database. I would use these and create a checksum or something based on the existing structure, create a proc that checks this and use it in your apps launch!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
won't you get a lot of service calls any time you add a table or a field, making it all fail suddenly?
|
|
|
|
|
As there is a fundamental problem with change management I think that would be the least of his problems. It sounds like he does not own the database and he is not in the change management loop.
We recently had something like this where the IT team changed a view that was feeding us and did not inform the clients of the view. A rocket went around the IT team and we are now in the CM loop.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hello
can any one please tell me how can i perform the following actin in SQL
if ((sizee == "") && (sidee == "") && (color == ""))
{
ItemComputerCode = PartGroup + "-" + Model + "-" + Brand + "-" + orgnShortName;
}
else if ((sizee == "") && (sidee == "") && (color != ""))
{
ItemComputerCode = PartGroup + "-" + Model + "-" + Brand + "-" + color + "-" + orgnShortName;
}
else if ((sizee == "") && (sidee != "") && (color == ""))
{
ItemComputerCode = PartGroup + "-" + Model + "-" + Brand + "-" + sidee + "-" + orgnShortName;
}
else if ((sizee == "") && (sidee != "") && (color != ""))
{
ItemComputerCode = PartGroup + "-" + Model + "-" + Brand + "-" + sidee + "*" + color + "-" + orgnShortName;
}
else if ((sizee != "") && (sidee == "") && (color == ""))
{
ItemComputerCode = PartGroup + "-" + Model + "-" + Brand + "-" + sizee + "-" + orgnShortName + "*";
}
else if ((sizee != "") && (sidee == "") && (color != ""))
{
ItemComputerCode = PartGroup + "-" + Model + "-" + Brand + "-" + sizee + "*" + color + "-" + orgnShortName;
}
else if ((sizee != "") && (sidee != "") && (color == ""))
{
ItemComputerCode = PartGroup + "-" + Model + "-" + Brand + "-" + sizee + "*" + sidee + "-" + orgnShortName;
}
else
{
ItemComputerCode = PartGroup + "-" + Model + "-" + Brand + "-" + sizee + "*" + sidee + "*" + color + "-" + orgnShortName;
}
return ItemComputerCode;
thnx in advance
Regards
|
|
|
|
|
It would likely involve case , but I wouldn't do it in SQL, just clean up the code you have.
System.Text.StringBuilder sb = new System.Text.StringBuilder() ;
sb.Append ( PartGroup ) ;
sb.Append ( "-" + Model ) ;
sb.Append ( "-" + Brand ) ;
string sep = "-"
if (sizee != "") { sbAppend ( sep + sizee ) ; sep = "*" ; }
if (sidee != "") { sbAppend ( sep + sidee ) ; sep = "*" ; }
if (color != "") { sbAppend ( sep + color ) ; sep = "*" ; }
sb.Append ( "-" + orgnShortName ) ;
return ( sb.ToString() ) ;
|
|
|
|
|
thnx,but if ay one plz do it in sql
|
|
|
|
|
Hi, I am running VS2008 and use SQLExpress. I craete a SP in my database. then when I wanna save it I will get this error : Cannot update identity column 'Code'
the above code is my SP
Create PROCEDURE dbo.SprocInsertUpdatePerson
(
@code int,
@id int,
@name nvarchar(50),
@passportNumber nvarchar(50),
@nationality nvarchar(50),
@created datetime,
@creator nvarchar(50)
)
AS
Declare @ReturnValue int
if (@Code IS NULL) --New Item
Begin
insert into Person
(
Code,
Id,
Name,
PassportNumber,
Nationality,
Created,
Creator
)
values
(
@code,
@id,
@name,
@passportNumber,
@nationality,
@created,
@creator
)
Select @ReturnValue = Scope_Identity()
End
else
Begin --Update Item
Update Person
Set Code = @code,
Id = @id,
Name = @name,
PassportNumber = @passportNumber,
Nationality = @nationality,
Created = @created,
Creator = @creator
where Id = @id
Select @ReturnValue = @Id
End
IF (@@ERROR != 0)
BEGIN
RETURN -1
END
ELSE
BEGIN
RETURN @ReturnValue
END
I really appreciate if someone help me.
thx a lot
|
|
|
|
|
You can remove it from the insert-statement; an IDENTITY [^] is automatically generated when a new record is inserted. Kinda like an autonumber-column in Microsoft Access. The Scope_Identity would then return that newly generated value.
I are Troll
|
|
|
|