I have a table in which i have to delete last n inserted rows, how should i approach this , Sql server does not provide any ROWGUID BASED ON TIMESATAMP
i dnt think Rank() wil work either
Any suggestions?
I would like to perform autodeletion operation from my table using stored procedure....
Here is my table structure:
Emp.id | emp_name | dept | desig | time_out | date | emp_sign |
if the user inserts new employee record that record should delete automatically from original after
30 minutes..I don't want to keep that newly inserted record after
30 minutes...I don't know how to achieve this...
I need to count how many rows are inserted and then how many updated from a table then put that result in a new table.
Lisa Jefferson
There is a stored procedure that inserts a row into 'Vendors' table. Is it possible that two different calls to this sp happen at the same time and as a result, each sp inserts into the table its row at exactly the same time?
View 1 Replies View RelatedWhy does this code tell me that I inserted 2 rows when I really only inserted one? I am using SQL server 2005 Express. I can open up the table and there is only one record in it. Dim InsertSQL As String = "INSERT INTO dbCG_Disposition ( BouleID, UserName, CG_PFLocation ) VALUES ( @BouleID, @UserName, @CG_PFLocation )"Dim Status As Label = lblStatus
Dim ConnectionString As String = WebConfigurationManager.ConnectionStrings("HTALNBulk").ConnectionString
Dim con As New SqlConnection(ConnectionString)
Dim cmd As New SqlCommand(InsertSQL, con)
cmd.Parameters.AddWithValue("BouleID", BouleID)
cmd.Parameters.AddWithValue("UserName", UserID)
cmd.Parameters.AddWithValue("CG_PFLocation", CG_PFLocation)
Dim added As Integer = 0
Try
con.Open()
added = cmd.ExecuteNonQuery()
Status.Text &= added.ToString() & " records inserted into CG Process Flow Inventory, Located in Boule_Storage."
Catch ex As Exception
Status.Text &= "Error adding to inventory. "
Status.Text &= ex.Message.ToString()
Finally
con.Close()
End Try Anyone have any ideas? Thanks
Using insert stored procedure ,How to count the number of rows inserted.
View 1 Replies View RelatedRajesh writes "Hi
I have a table. I need to see the newly inserted row on the day. i.e. newly inserted data alone. I dont have any date field in the table."
HI, I am wondering if it is possible to retreive this information without using row count transform. Can I get the # of rows inserted/updated or deleted by destination from the log?
Thank you,
Ccote
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
View 2 Replies View RelatedI ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:
DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)
The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.
I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.
Does anyone have suggestions on what might cause this sort of behavior?
Hi,
I am trying to create a SSIS package, which will extract data from a SQL server view and populate the data in our local SQL server database tables. My objective is to get the data from the view such that only inserted and updated rows are fetched from the view.
Note: the view does not expose any updated date type of column thru which I can check. So I guess I have to compare each and every field with my destination table row's fields.
I would appreciate any suggestions on how to approach the problem.
Thanks in advance.
Hi!
I want to merge in a Datawarehouse, data of "source databases"
I need to insert on destination inserted rows a default value, acording to the datasource of the rows.
Something like in the field <SOURCE> fill on correspondent ROW the value <DATASOURCE1>
the row data is filled with default DATASOURCE value.
in nowdays i can extract and load the data of sources but i know how i can do this inserts,
thx.
Hello All,
Doe anyone know if any limitations with SQL Server Express may be causing this?
I have an application (a web service hosted on my local PC) that parses an InfoPath form and submits the data to a database (SQL Server Express). The problem I'm have is that new records are not always inserted into the database when I submit. There is an autoincrement ID field and I observed that every other record was successfully inserted (a day ago) and today I observed that the records were inserted intermittently. However, when I run the same application on a laptop, the records are inserted each time.
I posted the associated code on the Data Access forum but it occurred to me that it may be because of the nature of SQL Server Express.Any suggestions would be much appreciated. Thanks!
I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?
View 3 Replies View RelatedHi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
hi
I want to do a "bulk" insert and then get the id (primary key) of the inseret rows:
insert into table1 select * from table2
If I get the value of the @@identity, I always get the last inserted record.
Any idea how to get the ids of all inserted values?
Thx
Olivier
I have a TableA where data get inserted from Excel(IMPORT/EXPORT wizard)
TableA;
ID(identity) Date (NOT NULL Defaulyt Getdate() )
Name Phone
1 2014-06-17 17:28:21.190
Nick 12345678910
2 2014-05-17 17:28:21.190
Stan 00045678910
3 2015-08-17 17:28:21.190
Kim 11111678910
4 2015-08-17 17:28:21.190
Tom NUll
3,4 are the rows i have inserted now , you can see by date, likewise i have 100,000 rows(old and new combination) and now the data from excel to TableA can be imported/exported daily , hourly, weekly basis.
Now i want to find out only the rows which are imported to tableA today, or hours back, or yesterday .....
the reason is ,
Step1:get data from excel and import to tableA( this is a manual Step) and i know when the data is inserted with exact date and time.
Step2: get newly inserted rows from TableA and pass them as Parameters in Stored Procedure.( i may run step 2 after 1 hour, or after 1 day or after 1 week ,but i want only rows that are inserted )
I tried with using where Datecreated, but did work.
I have a table that sometimes has modifications to column(s) comprising the primary key [usually "end_date"]. I need to audit changes on this table, and naturally, turned to after triggers.
The problem is that for updates, when the primary key composition changes, I'm not able to relate/join using the primary key - obviously, it no longer matches across INSERTED and DELETED. Now, for a single row update, it's easy to check for updates on PK columns and then deduce what changes were made...
So the real question is: are rows in INSERTED and DELETED always in matching order (1st row in INSERTED corresponds to the 1st row in DELETED...)?
I don't want to put a surrogate key (GUID nor IDENTITY) on the base table if at all possible. INSERT... SELECT from the inserted/deleted tables into a temp table with identity column is fine, and is what I'm currently doing; I would like MVP or product engineer level confirmation that my ordering assumption is correct.
Testing using an identity surrogate key on base table, and selecting from the Ins/del tables, and the temp tables without an order by clause seems to always return in proper order (proper for my purposes). I've tested under SQL 2005 RTM, SP1, SP2, and SP2 "3152".
FYI, I've lost the debate that such auditing is better handled by the application, not the database server...
Aside: why doesn't the ROW_NUMBER() function allow an empty OVER( ORDER BY() ) clause? Will SQL ever expose an internal row_id, at least in the pseudo tables, so we can work around this situation?
Thanks
Mike
I create a Trigger that allows to create news row on other table.
ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX]
ON [dbo].[_IMPORT_FILES_CONTACTS]
AFTER INSERT
AS
[code]...
But if I create an INSERT with 50 rows.. My table CONTACT and ADDRESS possess just one line.I try to create a Cursor.. but I had 50 lines with an AdressID and a ContactID differently, but an Account and an AccountId egual on my CONTACT table :
C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC001 - ACCOUNT 001
C003 - AD003 - AC001 - ACCOUNT 001
C004 - AD004 - AC001 - ACCOUNT 001
C005 - AD005 - AC001 - ACCOUNT 001
I search a means to have 50 lines differently on my CONTACT table.
C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC002 - ACCOUNT 002
C003 - AD003 - AC003 - ACCOUNT 003
C004 - AD004 - AC004 - ACCOUNT 004
C005 - AD005 - AC005 - ACCOUNT 005
In my ETL job I would like to truncate stg table but before truncating stging table, I want to make sure that all the records are inserted in the data model. The sample is as below
create table #stg (
CreateID int,
Name nvarchar(10)
)
insert into #stg
select 1, 'a' union all
select 2, 'b' union all
[Code] ....
How can I check among these tables and make sure that all values are loaded into the data model and the staging table can be truncated.
Stepping thru the code with the debugger shows the dataset rows being deleted.
After executing the code, and getting to the page presentation. Then I stop the debug and start the
page creation process again ( Page_Load ). The database still has the original deleted dataset rows.
Adding rows works, then updating works fine, but deleting rows, does not seem to work.
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); }
/* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
Hi all!In a insert-trigger I have two joins on the table named inserted.Obviously this construction gives a name collition beetween the twojoins (since both joins starts from the same table)Ofcourse I thougt the usingbla JOIN bla ON bla bla bla AS a_different_name would work, but itdoes not. Is there a nice solution to this problem?Any help appriciated
View 1 Replies View Relatedis there any way of getting the identity without using the "@@idemtity" in sql??? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me???
View 1 Replies View RelatedHi All,I am designing a purge process for a db that has grown to almost 200GB.My purge process will remove about 1/3 of the 500 million rows spreadover seven tables. Currently there are about 35 indexes defined onthose seven tables. My question is will there be a performance gain bydropping those indexes, doing my purge, and re-creating the indexes. Iam afraid that leaving those indexes in place will create a lot ofextra overhead in my delete statements by having to maintain theindexes. I know that it could take many hours to rebuild the indexesafterward, but I am planning on doing that anyway. The reason that Iwant to know whether I should drop the indexes ahead of time, is I maynot be able to do the entire purge at once and the tables may need tobe accessed between purges. If this occurs, I will need to have thoseindexes in place.So do I drop the indexes before the purge and re-create them later ordo I leave them in place and re-index them afterward?Thanks In Advancep.h.
View 12 Replies View RelatedHi
I am trying to delete rows in temp1 which are exist in temp2
delete from dbo.temp1 as a
inner join temp2 as b
on a.regionname=b.regionname
and a.servicecode=b.servicecode
and a.directioncode=b.directioncode
and a.destorigflag=b.destorigflag
I am getting error :
Incorrect syntax near the keyword 'as'.
thanks in advance
subash
Hi there,
I have two tables a,b, with the same columns.
How do I delete all the data from a that has the same entry as b?
Thanks for all the answers
I have a table that I would like to only keep the top 10 rows for each username. How can I kep the top 10 and delete the rest?
View 17 Replies View RelatedHi,
I'm really not very good at SQL sadly, so would really appreciate any help.
I'm basically working on a website that has a chatroom. I want it so the chatroom table only holds, say 15 lines of chat, then once a new line of chat is entered the last line in the table is deleted, so the table always has a maximum of just 15 rows.
The fields are:
MessageID
Poster
Message
DateTime
I've tried my best, but just don't know how to do it.
I suppose it's something like:
SELECT TOP 15 * FROM Chatroom ORDER BY MessageID DESC - this gives me the last 15 rows.
Then I need a delete statement to delete the rest?! Sorry, I am very bad at SQL, so any help would be great. This is written in a stored procedure.
Can the stored procedure pick up whether there are 15 or more rows in the table, and if so then delete all bar the newest 15 rows of chat?
To summarise: I want a stored procedure that checks if 15 or more rows exist, if they do then delete all bar the newest 15 rows.
Thanks,
Ricky
Hi..
DELETE FROM table1 WHERE projektID=5
there are 500000 rows that has projektID=5.. and when i run the query the hardrive is working for a couple of minutes and then stops. and NOTHING has happened. not a single row has been deleted?.. cant the DELETE statement handle that many rows or?. or is there another way i can delete these rows?.
Hii want to delete a row in my database but the problem is, i cant delete it as other table rows is linked to it. I have to delete all the rows thats linked to the row i wanna delete first.Is there a easier way to delete the row and all the rows thats linked to it? i wanna code it to do it.an suggestions?
View 1 Replies View RelatedI have a table which looks as follow:
field1 field2 field3 field4 field5 ......
A B C A X ......
A B C B Y ......
A B C C Z ......
A B C A Y ......
. . . . . ......
I want to delete all the rows except one row. Anybody can help?
Thank you very much.
How do you delete duplicate rows in a table so only one row is left in the table, using T-SQL.
View 1 Replies View Related