Re: Resetting 'Identity Specification' column in Sql Table - Database Discussion Boards

Thnx for your answer. It perfectly solves my problem Smile | :)

If there are no foreign keys on the table I guess you can always use:

truncate table <table_name>

to kill 2 birds with one stone... Smile | :)

Don't let the animal welfare people hear you talk of "killing two birds with one stone". Laugh | :laugh:

Yes, not very PC of me, obviously if I wanted two birds killed I wouldn't use something as inelegant as a stone, I'd simply put a contract out on them... Smile | :)

Nah this is so obvious that Ellen's answer had to be the relevant one surely!

Besides I'd use a shotgun and get the whole flock (or at least more than 2).

Never underestimate the power of human stupidity
RAH

Hmm, I guess it all depends where you need to use it. I prefer my code to be able to be used in as many situations as possible, therefore to me issuing DML is _always_ preferable to DDL. Besides which, the obvious solution to deleting all rows in a table is to use truncate, the fact that it resets the seed is an added benefit, why make life more difficult for yourself than you need to ? Wink | ;)

unclejimbob wrote:
I prefer my code to be able to be used in as many situations as possible

I don't ever recall actually coding a truncate/reseed into a stored proc, in 20+ years. These live under the snippets/template folder in my IDE!

Never underestimate the power of human stupidity
RAH

"I don't ever recall actually coding a truncate/reseed into a stored proc, in 20+ years."
Hey, good for you, I use truncate in sps for several - admittedly specialist - purposes. I too have a large number of tsql patterns/idioms as templates.

I can't ever recall anyone suggesting that 2 lines of code (delete/reseed) is somehow better than 1 (truncate) and yes, I've been at this game at least as long as you have.

You will note that I never suggested my solution was any better or worse than Ellen's I just looked at it from a slightly different angle, taking into account the full content of the question - an approach you might like to try and emulate rather than turning a useful discussion into a point-scoring exercise with you as the self-anointed games master.

At 20+ years experience I shouldn't have to be pointing out to you self-evident things such as the fact that the first/most obvious solution isn't necessarily the most appropriate.

Oh yeah the bird thing ? Leave it, it started out as witty repartee and you've come in late in the piece and successfully tortured it to death.

___

Updated/final:
A few observations on this thread before I go.

I have deliberately not read either the most recent post or my email as I assume it contains the same pointless drivel I've been exposed to up to now.

1) Anyone who includes the clause 'I've never needed to do this in x years' is, of course, demeaning your contribution because they mistakenly equate length of service with quality of work; you rarely see this from published, world-class contributors, they tend to be pretty diffident - you don't need to put up with it

2) Indidivuals who come in after the fact and attempt to rate your contribution based upon their own close-enough-is-good-enough two-second evaluation are, of course, putting you down - don't accept it

3) Argumentative postings which don't add value to the discussion are a sign of insecurity over one's position - don't accept it

4) Zero-value postings which are used to simply boost an already over-inflated points-based rating system are, of course to be accepted for what they are

5) Crass, infantile, witless and off-colour postings unrelated to an original throw-away post are a sign that the person is humour-challenged, accept the fact that these people cannot be helped

Read the posts in this thread, there are 2 valid answers, 2 witty (albeit off-topic) observations and a host of pointless, unsubstantiated nitpicking - I leave you to decide which of these posts has actually assisted in answering the question

The main point of all this unseemly bickering is of course that it is NOT acceptable to rate another contributor's post without substantiating it with some solid evidence.

I don't care who you are out in the world, the same rules of posting etiquette should apply to both first-time and veteran contributors.

Bye.

-- modified 9-Jan-12 11:45am.

unclejimbob wrote:
I use truncate in sps for several - admittedly specialist - purposes.

As you say , specialised requirements, thankfully I have never actually had to use them.

unclejimbob wrote:
Taking into account the full content of the question - an approach you might like to try and emulate

What and actually give a balanced and reasonable response - Nah.

I actually was not denigrating your response as it was a good one (and got an upvote from me) more to the point that the OP probably was not aware of the limitation of the truncate solution.

unclejimbob wrote:
Oh yeah the bird thing

Never, I say never, have I come in late to an obscure discussion and got the wrong handle on on the discussion while trying to be amusing!

Never underestimate the power of human stupidity
RAH

unclejimbob wrote:
I can't ever recall anyone suggesting that 2 lines of code (delete/reseed) is
somehow better than 1 (truncate) and yes, I've been at this game at least as
long as you have.

Hopefully that statement is flippant.

There are in fact differences between using delete and truncate, especially in SQL Server, which have nothing to do with this discussion. And those differences are the reasons is should be used with care and why it is seldom needed.

Choosing the correct one has nothing to do with the number of lines. And there is very little in programmng that should be based solely on line count. (It isn't even a good developer productivity metric.)

unclejimbob wrote:
Indidivuals who come in after the fact and attempt to rate your contribution
based upon their own close-enough-is-good-enough two-second evaluation are, of
course, putting you down - don't accept it

This site is set up specifically to allow rating answers. Especially on technical questions. That is what the "Rate this message" is for.

And one need not even explain why they give it a good or bad rating.

Moreover I think that any technical site that doesn't allow and expect discourse on the correctness of answers is probably worthless.

Hi all

I have encountered a stored procedure template that we are supposed to use for our daily development work. The strange thing about this template (to me, at least) is that it has a RETURN statement just before the final END statement. Obviously if the proc is returning a value, this statement is necessary, but we are talking about a stored proc that returns no value. I do not understand why this convention exists, much less why someone would care to enforce it. It looks like it clutters the readability of the stored proc by appending code that does nothing. I don't recall seeing any C code like this: "void fx(void) { return; }", at least not on purpose.

But I figure that this convention must have come from somewhere, and maybe at some point there were a perfectly valid and good reason for it.

So my question is this: What good reason is there (if any) to have a RETURN; statement just prior to the final END statement in a stored proc that does not return a value?

Here is an example:

SQL

CREATE PROC fx
AS
BEGIN
    RETURN; -- Why is this here just before the END?
END
go

No reason.

For TSQL the only thing I find odd with return is the need to put one after raiserror. Seems like raiserror itself would be sufficient to say stop.

Depends on the errorlevel; I use 'em also to send some intermediate messages to the layer above.

Bastard Programmer from Hell Suspicious | :suss:

Because that's where it belongs.

I think I recognize a Fortran voice...

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

I am building a reporting database that is pulling data from several source and different databases.

The plan so far is to turn on change data capture on the source databases and process the log generated using SSIS.

I can find a few papers and sample online on how to process the ubiquitous INSERT/UPDATE/DELETE log and it is helpful but I fail to find any good sample or tool with multiple tables.

I can't see how I can process the items in the right order and keep integrity.

Let's say we have a classic database with Product, Order and OrderLine.
And this scenario:

8:00 - Create a product P1
8:01 - Create an Order O1
8:02 - Create an Order Line L1
8:03 - Delete the Order Line L1
8:04 - Delete the Order O1
8:05 - Create a new Order O1
8:06 - Create a new Order Line L1

The cdc tables will contain

For Product

8:00 INSERT P1

For Order

8:01 INSERT O1
8:04 DELETE O1
8:05 INSERT O1

For Order Line

8:02 INSERT L1 Order:O1 Product:P1
8:03 DELETE L1
8:06 CREATE L1 Order:O1 Product:P1

To keep the referential integrity of the reporting database, I would need to:
- insert order lines after orders
- delete order lines before orders

Because of this I can't really treat a CDC at a time.

As I see it the only way would be to process all CDC simulataneously in datetime order.
Is there ways to do that?

The source databases are Oracle and MS SQL and possibly text files.
The reporting database is MS SQL

Wow in 20+ year of writing reporting systems I have never had to consider this for of replication, imagine reading the logs!

Why do normal replication tools (something else I know little about) not meet your requirements.

Never underestimate the power of human stupidity
RAH

hello veri nice

http://www.hobby-zoo.ro

One idea could be do to disable all relations/checks/constraints on the report tables, execute CDC statements and then re enable relations/checks/constraints.
Your execution should handle errors nicely though for this to work.

Shreekar

i have to schedule backup of particular schema objects, using sql server agent jobs.

in database there is five schema like

schema [a]
[a].tbl_a -- Table
[a].usp_a --Procedures
[a].fn_a -- udf

schema[b]

[b].tbl_b --Table
[b].usp_b --Procedures
[b].fn_b --udf

where [a] and [b] are the schemas.

now i have to schedule full backup of only "[a]" schema objects.
And there is only one file group(Primary) for database.

using sql server agent job,is it possible or not?

Have you tried googling it?

Here is suggestion which I have not tried with database backups but I have used from scheduling jobs with the windows scheduler.

Create a batch program(text file with DOS commands, and with extension .bat). In the batch program connect to -iSQL and pass to it a .sql file that contains the backup instructions.--> That means you also have to create a .sql file. Now create a schedule in windows scheduler that executes the bat program

Windows Scheduler calls mybackup.bat; mybackup.bat calls -isql which in turn calls mybackup.sql.

Test the whole thing on test on test database.

Hello,

I just started learning TSQL Stored procedure (SQL Server 2005) and looks like it does not have strong set of functions that I could do in programming languages. Anyway, I wanted to return a string type data which holds comma separated value read from a temporary table. After spending hours, I came to the following code that WORKS. But I am wondering there should be more efficient way to do that. My following code runs at least 2 query to the temp table in each iteration to build the string where one of the query uses Nested SQL statement which can be more expensive in performance. So, Would anyone please tell me is there any better way to do that ? I thought there would be SKIP operation but SKIP operator is not being accepted by my SQL Server. IF there was any SKIP operator, at least I could save a nested query. Anyway, your help would be highly appreciated.

Regards.

SQL

DECLARE @Iterator INT
 SET @Iterator = 0

WHILE (@Iterator < @totalIds)
BEGIN

    IF(@Iterator = 0)
     BEGIN
              SELECT TOP 1 @DeletedIDArr = CAST(Id AS varchar(50)) FROM #deletedIds
     END
    ELSE
     BEGIN
             SELECT TOP 1 @DeletedIDArr = (@DeletedIDArr + ',' + CAST(Id AS varchar(50)))
             FROM #deletedIds
             WHERE Id NOT IN(SELECT TOP (@Iterator) Id FROM #deletedIds);
     END
    SET @Iterator = @Iterator + 1
END

That looks a real bottleneck: for N entries in the result list, you perform 2N-1 SELECT statements, and N-1 string concatenations, which must be as bad in SQL as they are in any OO language (remember the purpose of StringBuilder versus string in C#).

Are you sure you really need a comma separated list at the SQL level? If you were to perform a simple SELECT statement, you would get a result table that contains the data you want, one result element per row. At practically no cost.

Too often IMO people tend to find an SQL-only solution to a problem. This may well be one more example of that.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Hi Luc,

Thanks for your reply. Yes, thats what I realize, I would never do the same thing in my programming. (I am a C# programmer) But the problem is, I dont know the correct TSQL Syntax / operators that can let me get rid of this loop.

My intention to get data from stored procedure in the following structure

-------------------------
string product_name,
int affectedRows,
bool hasError,
int[] someIds
-----------------------

Now, everything was fine until I reached the need to get int[] someIds. I was lost, how I could do that. If I just needed to return SomeIds (: int[]) then, yes, I could simply return the result set, but as I needed to get the other string, bool, int etc type data so I created a temporary table in my procedure to store the int values (someIDs : int[]) then, I used the specified procedure to read back the int ids from the temporary table and serialize as comma separated string to return.

Would you please show me any technique to get such data structure from stored procedure ?