|
|
Difference between DELETE, TRUNCATE, DROP
-
|
|
|
|
|
DELETE removes rows from a table and ensures that referential integrity is maintained, triggers are run and so on.
TRUNCATE just removes the rows and to hell with the consequences
DROP removes an object (like a table, stored procedure, function, view, etc.) from the database
|
|
|
|
|
I'd add to Colin's post that you can recover from DELETE with a rollback, while TRUNCATE and DROP do not provide any mechanism to recover.
Chris Meech
I am Canadian. [heard in a local bar]
Nobody likes jerks. [espeir]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
I am using xml(.) datatype in sql ser 2005
I want to get the value as string without using dataset
because the data is very huge, if i took by dataset the end values were truncated
i want to get the value directly inside a stored procedure and to pass the value as an input to openXML in the same stored procedure
Please guide me
|
|
|
|
|
Get the values from the UI and generate the XML string using .Net XML API class on the fly in the Business logic before sending it to the Stored Procedure.
Pass the generated XML string to the stored procedure.
In the stored procedure use the ntext to store the input XML string for further manipulation.
Sharp
Happy Programming
|
|
|
|
|
Hi,
I am working with SQL Server 2005. I have a created a database on my local machine, and would like to upload it to my hosting company. In the past the incrementing fields loose their IDENTITY so I had to go and manually set this property to be incremented by 1 each a new record is added.
So what I was thinking, maybe it is better if I just generated the SQL script of the tables, and run the script on the database online?? But then all of my records and data on the local machine isn't added into this script that generates the tables. How do I create INSERT statements that will take my current data and add it to the SQL Server at the hosting company.
This is the only way of doing it, is there maybe other ways??
Regards
ma se
|
|
|
|
|
SET IDENTITY_INSERT table on
insert
select
SET IDENTITY_INSERT table off
|
|
|
|
|
Hi,
I have no idea what you are trying to say here. I have a database on my local machine and I want to generate a script file that creates the tables and inserts the current values to the new database as well.
I hope you understand.
Regards,
ma se
|
|
|
|
|
I have a table in MS SQL Server 2005 containing two fields :-
TicketId and Narrative
The data within the table may look as follows
TicketId Narrative
-------- ---------
302523 FARMS
302523 UNIT 8420 KGS
302524 VEHICLE BREAKDOWN
302525 REPAIR SCHEDULE
Notice the first two records have the same ticket Id therefore are related.
I need setup a view the returns 1 record per ticket, concatenating related narratives together e.g.
TicketId Narrative
-------- ---------
302523 FARMS UNIT 8420 KG
302524 VEHICLE BREAKDOWN
302525 REPAIR SCHEDULE
Any help much appriciated
Steve Jowett
|
|
|
|
|
use function
create function abc(@TicketId char(x))
returns char(xxx)
as
begin
declare @ret char(xxxx)
declare @tmp table
(
....
)
insert into @tmp (...)
select ....
while ...
begin
...
set @ret = @ret + Narrative
end
return @ret
end
select TicketId,dbo.abc(ticketid) 'Narrative' from yourtable
|
|
|
|
|
MY finalized function, if anyone is interested :-
<br />
set ANSI_NULLS ON<br />
set QUOTED_IDENTIFIER ON<br />
GO<br />
CREATE FUNCTION [dbo].[ConcatNotes](@TicketId BigInt) <br />
RETURNS NVarChar(4000)<br />
AS<br />
BEGIN<br />
<br />
-- Declare the return variable here<br />
DECLARE @Narrative NvarChar(4000)<br />
DECLARE @Notes NVarChar(2000)<br />
DECLARE notes_cursor CURSOR FOR SELECT [Text] FROM Notes WHERE TicketId = @TicketId ORDER BY NoteId ASC<br />
OPEN notes_cursor<br />
FETCH NEXT FROM notes_cursor INTO @Notes<br />
SET @Narrative = ''<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
IF LEN(@Narrative) > 0<br />
BEGIN<br />
SET @Narrative = @Narrative + ' ¦ '<br />
END<br />
SET @Narrative = @Narrative + @Notes<br />
FETCH NEXT FROM notes_cursor INTO @Notes <br />
END<br />
<br />
CLOSE notes_cursor<br />
DEALLOCATE notes_cursor<br />
-- Return the result of the function<br />
RETURN LTRIM(@Narrative)<br />
END<br />
<br />
Then in my SELECT statement
SELECT TicketId, ConcatNotes(TicketId) AS Narrative FROM Tickets
Thanks to Zhengdong Jin for pointing me in the right direction
Steve Jowett
|
|
|
|
|
Sir,
In sql server 2005 for the particular column I want to get the auto increment property = true.But I don't know where that property is located.
Please help
|
|
|
|
|
In Sql Server Management Studio, Modify the table, View the column properties at the bottom, open the +Identity Specification and change (Is Identity) to Yes.
|
|
|
|
|
how to generate primary keys automatically in SQL Server2000 using enterprise manager
|
|
|
|
|
You have to do it yourself. It's not access. Insert a column, right click it and click Set Primary Key. You may also want to make the column autoincrement also.
ChrisB
|
|
|
|
|
Hi All
I just wanted to ask which is more optimal in terms of time & complexity.
1. Returning more than once to the database to retrieve some data
or
2. Getting all the data once & declaring a table at the database to store this data then getting the rows I want from this table & returning it as a string
Thanks a lot
Happy
|
|
|
|
|
i have pb connecting to remote sqlserver using asp.net the connection string i used
is
Dim sqlconn As SqlConnection = New SqlConnection("server=155.155.55.5;user id =try;password=try; database=db1")
the error i get is "sqlserver does not exist or access denied"
i check the authentication its correct...
i am not able to create obdc connectivity also for it ...
the sqlserver2000 is newly installed and just database is created.no other configurations done to database or tables.
thanks for help in advance .
waiting for your reply...
|
|
|
|
|
If the SQL 2000 installation includes SP3, then the default install disabled SQL authentication.
|
|
|
|
|
thnks for reply
but i have enabled the sql authentication in the installations steps..
i have not updated the sqlserver2000 evaluation edtition as it asked after installation to upgrade it to solve compatibility issue with win20003 r2 server .does this could have been the problem with not connecting..
the greatest thing is my application worked on live server which is already configured but not running this development server...
|
|
|
|
|
Hi all,
For Windows Application,
I have more than one DataTable in a Dataset,
I want to show different columns from diferent DataTable in a row.
For Eg:
DataTable 1: contains Fiels like 1C1,1C2,1C3
DataTable 2: contains Fiels like 2C1,2C2,2C3
DataTable 3: contains Fiels like 3C1,3C2,3C3
I want to show the record in DataGridView in one row like,
1C1,1C2,1C3,2C1,2C2,2C3,3C1,3C2,3C3 use of Binding
And also I want to update all the dataTables back to DataBase.
How to achieve this..
Thanks
raja
|
|
|
|
|
Hi there
guess u should use the join method for the three datatables and then display the results after string manipulation or u need to read the values by using a loop and append the values to a stringBuilder (remember to import the System.text)
Hope that it helps 
Best Regards
3ala2
|
|
|
|
|
I am trying to add data in databse by useing this code but i dont know wnow why it is not craeting MyTable it is creating Database.mdb but table is not cretaing why? is the code is incorrect or you can help me to correct my code?I have not done here the ODBC connection
<br />
CDaoDatabase database;<br />
CDaoRecordset recordset(&database);<br />
CString lpszFile = "c:\\Database.mdb";<br />
database.Create(lpszFile);<br />
database.Open(lpszFile);<br />
CString SqlCmd = "CREATE TABLE MYTable (MarketNo VARCHAR(2),MarketName VARCHAR(8),Bid VARCHAR(10),Ask VARCHAR(10),MarketState VARCHAR(2))";<br />
database.Execute(SqlCmd);<br />
recordset.Open(AFX_DAO_USE_DEFAULT_TYPE,"SELECT * FROM MyTable",0);<br />
database.Execute("INSERT INTO MyTable(MarketNo)");<br />
database.Execute("INSERT INTO MyTable(MarketName)");<br />
database.Execute("INSERT INTO MyTable(Bid)");<br />
database.Execute("INSERT INTO MyTable(Ask)");<br />
database.Execute("INSERT INTO MyTable(MarketState)");<br />
recordset.AddNew();<br />
recordset.SetFieldValue("MarketNo","a");<br />
recordset.SetFieldValue("MarketName",Market);<br />
recordset.SetFieldValue("Bid","b");<br />
recordset.SetFieldValue("Ask","c");<br />
recordset.SetFieldValue("MarketState","d");<br />
recordset.MoveNext();<br />
recordset.AddNew();<br />
|
|
|
|
|
I'm having trouble coming up with a query in SQL Server 2000. Trying my best to sum it up in one sentence; I need a result set that for every EventID/StartDate that exists in tblEvents, I need all StartDates within x days to have a minimum sequential, unique number starting at 1. Now I'll go into some more detail and possibly confuse everyone.
What I have is a table of events that to simplify this question include only an EventID and an StartDate field. What I need to do is for each event, find the other events that occur within x days after the event in question. I then need to number the events starting at 1 so that each eventB that occurs within x days of EventA has the minimum number different from any events from the past x days. I know I just sucked at putting it in words so I'll give an example.
Here is what my idea of the end result table should like if x=2. The source table is basically the same thing minus the Num column.
tblEvents
------------
EventID Date Num
1 10/1/06 1
3 10/2/06 2
7 10/1/06 3
4 10/4/06 1
6 10/10/06 1
2 10/20/06 1
5 10/21/06 2
Here's a quick diagram I made (I know it's ugly and not to scale) which hopefully helps you understand what I need better. Basically, I will be drawing a timeline using vml and each item is going to have text next to it so I need items to be staggered if they are on the same day or close to each other so that things don't overlap. I also need to minimize the vertical space it takes up which is why num needs to get back to 1 as soon as it can instead of just putting each event on its own line. The "num" column will tell me what row to put the item in.
http://i10.tinypic.com/43pxycj.gif
The following query gets me closer I think but it has a few problems and could be the wrong approach completely. I need this to work in SQL Server 2000 and I know that Row_Number and also Over() I think are both SQL Server 2005 only which is one problem with what I've come up with so far. Also, the partition I use has a separate value for the mirror of items (eg. EventID 7 should not be on row 1). Any tips or thoughts would be appreciated.
SELECT E1.EventID AS E1_EventID, E1.StartDate AS E1_StartDate, E2.EventID AS E2_EventID, E2.StartDate AS E2_StartDate, DATEDIFF(day, E2.StartDate, E1.StartDate) As Diff, ROW_NUMBER() OVER(PARTITION BY E1.EventID ORDER BY E1.StartDate, E1.EventID) FROM tblEvents AS E1 LEFT OUTER JOIN tblEvents AS E2 ON E1.EventID < E2.EventID AND ABS(DATEDIFF(day, E1.StartDate, E2.StartDate)) <= 2
Thanks for any help!
Tom
|
|
|
|
|
Hello,
I need to load the last N records from a table.
The last N records are determined by date.
And I want N to be a parameter in the Query.
How can I do this?
I allready have a Query. The only thing I am missing is that part.
SELECT web_News.ArticleDate, web_NewsLocalized.ArticleTitle,
web_NewsLocalized.ArticleText
FROM web_News INNER JOIN web_NewsLocalized ON
web_News.ArticleId=web_NewsLocalized.ArticleId
WHERE ((web_NewsLocalized.ArticleCulture)=[@Culture]);
Thanks,
Miguel
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
