Showing posts with label sample. Show all posts
Showing posts with label sample. Show all posts

Monday, March 26, 2012

Prblem With Inserting Data With Identity

Greetings!

I Have A Little Problem...

Is It Possible To Make The Identity In My Table Reset?
I Have Used The Table Already To Make Insert Some Sample Data.. Now I Deleted Those Data Inserted.. The Problem Now Is That The Identity Goes On With Incrementing(obviously)...

For Example:

I Have 2 Columns And 3 Rows..
1st Col Is The Identity, 2nd Is Some Data...
The Rows Has Identities Which Are 1,2,3 Respectively..
Then I Manually Deleted The 3rd Row Which Has The ' 3 ' Value In The First Col... Now When I Inserted Data, The Value Becomes ' 4 ' In The First Col, Becuase Obvioulsy It Is An Identity Column..

My Question Is, Is It Possible To Still Have The ' 3 ' Value When I Insert A New Value?

Or Maybe Just Reset The Table To Start With The Last Identity In The Table... Just Like With My Example...
(some Scripts Maybe?)Follow the steps
1. Drop Identity Column from table and Save
2. Re Create Identity Column.

Hope fully your problem will be resolve.

Regards|||[QUOTE=vinci]Greetings!

I Have A Little Problem...

Is It Possible To Make The Identity In My Table Reset?
I Have Used The Table Already To Make Insert Some Sample Data.. Now I Deleted Those Data Inserted.. The Problem Now Is That The Identity Goes On With Incrementing(obviously)...

You can use this:

SET IDENTITY_INSERT TableName ON

and then attempt to insert with an identity number that has been deleted.

Friday, March 9, 2012

Possible to parse a column in a Select statement?

I have a column called SEGMENTED_BLOCK sample data:
X,X,X
XX,XX,XX,
TYZC123456,X,X,
TOYZ654321,1234,777777

I need to do something that has the effect of

SELECT
(stuff before first comma) as FIRST_ITEM,
(stuff after first comma, but before second) as NEXT_ITEM,
(stuff after second comma but before third(if any)) as THIRD_ITEM
FROM SEGMENT_XREF
WHERE LOOKUP_ITEM = 12345
ORDER BY FIRST_ITEM

FIRST_ITEM is pretty easy, but it gets uglier fast.
My attempts are horrendously ugly nested checkindex and substring statements.
Is there an easier way?Hi

Charindex is the usual way to segment strings even when it is like:
http://www.users.drew.edu/skass/sql...unction.sql.txt

John
"grok" <joe.hurzeler@.verizon.net> wrote in message
news:y36Wc.6100$O%4.4380@.nwrddc04.gnilink.net...
I have a column called SEGMENTED_BLOCK sample data:
X,X,X
XX,XX,XX,
TYZC123456,X,X,
TOYZ654321,1234,777777

I need to do something that has the effect of

SELECT
(stuff before first comma) as FIRST_ITEM,
(stuff after first comma, but before second) as NEXT_ITEM,
(stuff after second comma but before third(if any)) as THIRD_ITEM
FROM SEGMENT_XREF
WHERE LOOKUP_ITEM = 12345
ORDER BY FIRST_ITEM

FIRST_ITEM is pretty easy, but it gets uglier fast.
My attempts are horrendously ugly nested checkindex and substring
statements.
Is there an easier way?|||see
http://www.nigelrivett.net/f_GetEntryDelimiitted.html

It's a function that returns entries from a csv string.
I use it for gettig fields from data after bulk inserting but it can be
used for a single string too.

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!