|
I am trying to reset the PK on a SQL Server table. The key is auto populated and automatically increments by 1. I have cleared all the data and want to repopulate. Is there a way to have the first Id be equal to 1? Currently numbering starts where the last data ended.
Second question is there a way to print the row number in a select statement?
Jason W.
|
|
|
|
|
If I remember correctly, to reset the row number field you need to TRUNCATE TABLE.
Michael
"I've died for a living in the movies and tv.
But the hardest thing I'll ever do is watch my leading ladies,
Kiss some other guy while I'm bandaging my knee."
-- The Unknown Stuntman
|
|
|
|
|
Exactly what I was looking for thanks. 
Jason W.
|
|
|
|
|
you can also do this by changing your identity to "NO" in the design view, saving the table and then re-instating the identity to YES
|
|
|
|
|
The only mode that I know, is removing the PK state, repopulating the fileld and setting like PK again...
Carlos Antollini.
Sonork ID 100.10529 cantollini
|
|
|
|
|
To reseed your identity column you can use the following dbcc command. Not too sure if it's supported though:
dbcc checkident ( <tablename>, RESEED, <newvalue> )<br />
example:
<br />
dbcc checkident(authors, RESEED, 100000)<br />
|
|
|
|
|
Oh, and for ther row number you might want to check out this link:
http://www.sqlteam.com/item.asp?ItemID=1491
|
|
|
|
|
I want to know if there is a way to get the PK from a row the minute I insert information. Here's the scenario, what's the most efficient way of doing this?
1. I have 50 tables that represent 50 HTML forms.
2. I am inserting results from OnlineAccess.asp into dbo.OnlineAccess.
3. I am keeping all dateTime stamps along with the status of the request (OPEN, BEING PROCESSED, COMPLETE) in another table called dateTime.
4. I want to get the PK of the information entered into dbo.OnlineAccess so that I can insert this into dbo.DateTime for relation to dbo.OnlineAccess's information.
Is there any "bi-di" capabilities that can be used to return the PK without having to run another line of code to SELECT the PK WHERE <and then="" pass="" results="" of="" the="" form="" back="" again="">. For instance, I know I can do it this way, but it seems like too much leg work:
sqlInsert = "INSERT into OnlineAccess(userID,lastName,firstName,...) values('"&userID&"','"&lastName&"','"&firstName&"',...)"
sqlSelect = "SELECT requestID FROM OnlineAccess WHERE userID='"&userID&"' AND lastName='"&lastName&"' AND ..."
RS = MyConn.Execute(sqlSelect)
requestID = RS("requestID")
sqlInsert = "INSERT into DateTime (requestID,status) values('"&requestID&"','OPEN')"
(I have a trigger that takes care of the dateTime stamps based on the status)
Can anyone help me on this one? Thanks in advance!
Robby
|
|
|
|
|
Probably the best way would be to write a stored procedure that can then insert your data and then return the PK to the caller.
I know this works with SQL Server, but I've had trouble returning values from SPs in Oracle.
Dave.
|
|
|
|
|
Indeed, using a stored procedure would by far and away be the best solution.
after an insert, sql server special variable @@IDENTITY contains the new pk of what you just inserted. so this will probably work:-
sqlInsert = "INSERT into OnlineAccess(userID,lastName,firstName,...) values('"&userID&"','"&lastName&"','"&firstName&"',...) ; SELECT @@IDENTITY AS [newkeyname]"
RS = MyConn.Execute(sqlInsert)
requestID = RS("newkeyname")
But really thats a bag of sh!t of an implementation. Stored procedures are the way to go. Why?
1. stored procedures run much quicker, because they are precompiled.
2. They are like functions in proper languages; you can change the internal code without changing the interface.
You could most probably do the whole job in one SP, like this:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [p_YourProc]
@UserId int,
@Firstname varchar(100),
@Lastname varchar(100),
@NewPKout integer output
AS
-- define a sql variable
declare @NewPK int;
-- do the main insert first.
INSERT into OnlineAccess(userID,lastName,firstName) values
( @UserId, @LastName, @Firstname);
-- retrieve the new key
select @NewPK=@@IDENTITY;
-- do the other inserts
INSERT into DateTime (requestID,status)
values(@NewPK,'OPEN')
-- might as well send the pk back to asp
set @NewPKout = @NewPK
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
There. Bob's your uncle.
Signature space for rent. Apply by email to....
|
|
|
|
|
Using Access 2000 and ADO VC++6.0
I am executing the following statement..
VARIANT Params[2];
Params[0].vt = VT_I2; Params[0].iVal = 1;
Params[1].vt = VT_R4; Params[1].fltVal = 8.99;
m_pCommand->adCmdText = "Query4";
m_pCommand->ActiveConnection = m_pConnection;
m_pCommand->Execute(0, Params, adCmdStoredProc);
Generates an exception {DB_E_PARAMNOTOPTIONAL}
Query4 in Access is as follows:
PARAMETERS vID Short, Rad IEEESingle;
INSERT INTO TABLE1 ( ODID, MeasRadius )
VALUES (vID, Rad);
Thanks for your help in advance.
|
|
|
|
|
You need to inform the type of Param, if input or output, for example...
But I see that you are passing a float param,, in that case you MUST to inform the presicion and the scale
Regards
Carlos Antollini.
Sonork ID 100.10529 cantollini
|
|
|
|
|
How to inform in/out or precision and scale in argument list of Execute method.
Thanks
|
|
|
|
|
I saw that you are using a variant to inform the parameters, but if you use use a Parameter type you must to do the following...
pParameter->Direction = (ParameterDirectionEnum)nDirection;
pParameter->PutPrecision(nPrecision);
pParameter->PutNumericScale(nScale);
Remember: When you are using a parameter type float, double, decimal, you must to inform the presisicion and the scale...
Regards
Carlos Antollini.
Sonork ID 100.10529 cantollini
|
|
|
|
|
I'm learning writing SQL query now. If I, for example, want to retrieve information from 3 joining tables (T1, T2, T3), and only want to return the latest failed task and task name is 'Mailboxes', I wrote the following query:
select top 1 TT.task_type_desc, TL.starting_time, AAC.type_code_value
from tasklog as TL inner join task_type as TT
on TL.task_type_id=TT.task_type_id
inner join aatype_code as AAC
on AAC.type_code_id=TL.task_status and AAC.type_code_class='task_status'
where AAC.type_code_value='Failed' and TT.task_type_desc='Mailboxes'
order by TL.starting_time desc
It gave me the required result. But how to do this query without using "top 1" and still get the same result? Thanks!
|
|
|
|
|
If it ain't broke... why do you want to fix it?
Paul
|
|
|
|
|
Because, this is just part of my query, if I use top 1, then, I can't get other entries required by some other conditions. So, basically, on that part I mentioned in my previous mail, I just want the last failed entry with any task name. Any suggestions?
|
|
|
|
|
Can't really answer without seeing the rest of your query, but you may be able to achieve what you're trying with a subquery:-
select a.a,b.b,c.c
from table_a a (nolock)
inner join table_b b (nolock)
inner join ( select top 1 * from table_c (nolock)) c
Signature space for rent. Apply by email to....
|
|
|
|
|
Anonymous wrote:
Because, this is just part of my query, if I use top 1, then, I can't get other entries required by some other conditions.
Use the Top 1, you can use the UNION statement to make multiple selects like this to match all of your criteria:
SELECT a, b, c, d FROM table_one WHERE a = 5
UNION
SELECT a, b, c, e FROM table_one WHERE a = 6
UNION
SELECT a, b, c, f FROM table_one WHERE a = 7
UNION
SELECT a, b, c, g FROM table_one WHERE a = 8
HTH 
Nick Parker
The goal of Computer Science is to build something that will last at least until we've finished building it. - Unknown
|
|
|
|
|
I may need to be able to create and update Access 2000 Queries using SQL commands. Is that even possible? I'm using ADO.NET if that, for some reason I've missed, makes life easier.
Paul
|
|
|
|
|
Paul Riley wrote:
Is that even possible?
Yes.Why not!
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Mazdak wrote:
Yes.Why not!
Well... because I don't know how to do it and thus I have to accept the unlikely possibility that it just can't be done.
So, Mazy, do you happen to know how to do it? 
Paul
|
|
|
|
|
When you use ADO.NET,you use sqlCommand class for SQLServer database,now here for Access database you have to use OleDbCommand class.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Mazdak wrote:
When you use ADO.NET,you use sqlCommand class for SQLServer database,now here for Access database you have to use OleDbCommand class.
 Ooops! That's not what I meant. I get OleDbCommand, although I'm using OdbcCommand.
I mean that I want to create an actual Access Query inside the Access database. Some quite complicated things that I'm going to have to do repeatedly so I don't want the SQL to be in my code, where one bug means fixing it in ten places. I want to get the Query into the database and read that. But it'll be a live database, so downloading it, updating it and then uploading it again will mean shutting the web site down.
I will have a direct SQL page that only I can get at, so it would be really convenient if I can just say "Add Query..." in the same way I can add a table.
The alternative is getting the guy who owns the server to update it (which is not beyond the realms of possibility, but it is a pain).
Paul
|
|
|
|
|
Anyone any experience of outputting a nested set model (to an XML
structure using Microsoft SQL Server? Would like to read in as a stream using ExecuteXmlReader.
For details on Nested Set Model, see the chapter in JOE CELKO'S SQL FOR SMARTIES (Morgan-Kaufmann, 1999, second edition)
http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html
http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci801943,00.html
> For example using the following table and TSQL I can extract an
> employee and their subordinates.
>
> Personnel
> emp lft rgt
> ======================
> 'Albert' 1 12
> 'Bert' 2 3
> 'Chuck' 4 11
> 'Donna' 5 6
> 'Eddie' 7 8
> 'Fred' 9 10
Example XML output:
<root>
<node emp="Albert">
<node emp="Bert"/>
<node emp="Chuck">
<node emp="Donna"/>
<node emp="Eddie"/>
<node emp="Fred"/>
</node>
</node>
</root>
This works, but isnt an XML stream:
SELECT xml1.xmltext
FROM
(SELECT lft, xmltext = '<node emp="' + emp + '">' FROM Personnel
UNION ALL SELECT rgt, xmltext = '</node>' FROM Personnel) AS xml1
ORDER BY xml1.lft
-- Ingram Leedy
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
