SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to retrieve last inserted records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Abid
Posting Yak Master

Pakistan
105 Posts

Posted - 03/15/2013 :  08:49:55  Show Profile  Reply with Quote
Hi. I have a vb.net form, from which i can Insert more than 2 (3, 4, 5 etc) records with one insert statement, right. Now i want to display all those last inserted records in crystal report.

My Question is that, Is it possible in SQL (i mean is there any such query) that i could retrieve only last inserted record, whether its 1 or more than 1?

Grifter
Constraint Violating Yak Guru

263 Posts

Posted - 03/15/2013 :  10:08:32  Show Profile  Reply with Quote
I take it you mean last inserted records from the VB form?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/15/2013 :  11:27:44  Show Profile  Reply with Quote
do you've a datetime field or auto increment id field in your table? is values stored in same or diffrent fields within sql server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Abid
Posting Yak Master

Pakistan
105 Posts

Posted - 03/15/2013 :  11:51:11  Show Profile  Reply with Quote
quote:
Originally posted by Grifter

I take it you mean last inserted records from the VB form?



Aah, I mean that i want to see the last record actually in my crystal report (last record means that last inserted records whether 1 or more than one).
Go to Top of Page

Abid
Posting Yak Master

Pakistan
105 Posts

Posted - 03/15/2013 :  11:53:26  Show Profile  Reply with Quote
quote:

do you've a datetime field or auto increment id field in your table? is values stored in same or diffrent fields within sql server?



Yes i have Primary Key which is set to Auto_Increment and data is storing in the main (same) table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/15/2013 :  12:56:19  Show Profile  Reply with Quote
quote:
Originally posted by Abid

quote:

do you've a datetime field or auto increment id field in your table? is values stored in same or diffrent fields within sql server?



Yes i have Primary Key which is set to Auto_Increment and data is storing in the main (same) table.


then use SCOPE_IDENTITY to get inserted id value. In case of multiple value inserts use OUTPUT clause to get values from INSERTED table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Abid
Posting Yak Master

Pakistan
105 Posts

Posted - 03/16/2013 :  06:54:42  Show Profile  Reply with Quote
I'm using a simple query i.e.

select top 5 * from ProdInfo order by Prod_ID Desc

this puts me to get 5 last inserted records, but its not solving my problem. My problem is that I have to shown the last numbers of records (whether 1, 2 or 3 or as much) on crystal Report, so in the above mentioned query I'm simply showing the 5 number of records (which is a dummy value) but how sql server will determine that how many numbers of new records have been inserted?
Did you get my idea?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/17/2013 :  13:28:17  Show Profile  Reply with Quote
For that you need a timestamp column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 03/17/2013 :  19:43:12  Show Profile  Reply with Quote
A timestamp might do it but to make multi-user correct you'd need the client transaction to insert into a separate table containing a "batch ID" identity and an optional timestamp. You then create one of those and store that ID against the records you insert into the table referred to in your original question.
Depending on if you want the most recent insert statement or most recent start of transaction, you'll need to get the highest ID or the highest ID with the latest date from the batch ID table.
Make sense?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/18/2013 :  01:49:29  Show Profile  Reply with Quote
Use ROWCOUNT to get number of rows inserted... based on this count you can display those records on grid...

insert into tableName
SELECT * FROM tableName WHERE <conditions>
SELECT @@ROWCOUNT

But this method is depends on type of insert...

--
Chandu
Go to Top of Page

Abid
Posting Yak Master

Pakistan
105 Posts

Posted - 03/18/2013 :  11:48:50  Show Profile  Reply with Quote
@ LozTInSpace

Hi. the timestamp is absolutely new thing to me. Totally Unfamilier to this. Please guide me further that i could overcome with my problem.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/18/2013 :  12:33:47  Show Profile  Reply with Quote
quote:
Originally posted by Abid

@ LozTInSpace

Hi. the timestamp is absolutely new thing to me. Totally Unfamilier to this. Please guide me further that i could overcome with my problem.



you could just add a datetime column defaulted to getdate() to get date value auto populated.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 03/18/2013 :  19:55:04  Show Profile  Reply with Quote
@abid - I mean a date/time not timestamp. Visakh16 is correct that a datetime on your rows could work provided you don't need to worry about two users hitting the database at the same time. That might be ok if this is a schedule batch run of some sort.
What I meant was something like this (not in front of SQL Server so syntax is not likely to be correct):

create table batch
(
id int identity(1,1) primary key,
batchtime datetime not null default getdate()
)
insert into batch values () -- Can't remember how you insert nothing into a table.

insert into yourTable......
values (@@scope_identity)

that will group your inserts
Go to Top of Page

Paramasivan B
Starting Member

India
4 Posts

Posted - 03/19/2013 :  00:08:23  Show Profile  Reply with Quote
While you creating a table, add one column like "ID" with IDENTITY.
IDENTITY is making rowcount automatically. first inserted data id is 1
2nd inserted id value is 2 up to end
if you need last column means YOU CAN TAKE MAX(ID)
EX
CREATE TABLE LAST_RECORD(ID INT IDENTITY,SNO INT)
INSERT INTO LAST_RECORD VALUES(1)
INSERT INTO LAST_RECORD VALUES(3)
INSERT INTO LAST_RECORD VALUES(5)
Last inserted value is 5. if you need that below query is useful for this
SELECT * FROM LAST_RECORD where SNO=(SELECT MAX(ID) FROM LAST_RECORD)




Paramasivan B
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/19/2013 :  01:02:37  Show Profile  Reply with Quote
quote:
Originally posted by Paramasivan B

While you creating a table, add one column like "ID" with IDENTITY.
IDENTITY is making rowcount automatically. first inserted data id is 1
2nd inserted id value is 2 up to end
if you need last column means YOU CAN TAKE MAX(ID)
EX
CREATE TABLE LAST_RECORD(ID INT IDENTITY,SNO INT)
INSERT INTO LAST_RECORD VALUES(1)
INSERT INTO LAST_RECORD VALUES(3)
INSERT INTO LAST_RECORD VALUES(5)
Last inserted value is 5. if you need that below query is useful for this
SELECT * FROM LAST_RECORD where SNO=(SELECT MAX(ID) FROM LAST_RECORD)




Paramasivan B


ID columns will help you to identify last inserted records only if you store MAX(ID) somewhere after each table population.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Abid
Posting Yak Master

Pakistan
105 Posts

Posted - 04/06/2013 :  05:52:52  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by Abid

quote:

do you've a datetime field or auto increment id field in your table? is values stored in same or diffrent fields within sql server?



Yes i have Primary Key which is set to Auto_Increment and data is storing in the main (same) table.


then use SCOPE_IDENTITY to get inserted id value. In case of multiple value inserts use OUTPUT clause to get values from INSERTED table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/


I tried to search on OUTPUT clause, but i didn't understand.
Go to Top of Page

Abid
Posting Yak Master

Pakistan
105 Posts

Posted - 04/06/2013 :  06:00:21  Show Profile  Reply with Quote
@ Params, @ Visakh

I'm using these two queries.
Dim selSale As String = "SELECT * FROM SaleInfo WHERE Sale_Date = (SELECT Top 1 Sale_Date FROM SaleInfo ORDER BY Sale_Date DESC)"
        Dim selSale As String = "SELECT * FROM SaleInfo WHERE Sale_Date = ( SELECT MAX(Sale_Date) FROM SaleInfo)"

Please tell me which one is correct.
Go to Top of Page

Abid
Posting Yak Master

Pakistan
105 Posts

Posted - 04/06/2013 :  06:02:41  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by Abid

@ LozTInSpace

Hi. the timestamp is absolutely new thing to me. Totally Unfamilier to this. Please guide me further that i could overcome with my problem.



you could just add a datetime column defaulted to getdate() to get date value auto populated.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



How to use GetDate method?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000