Hi there..

The target table got these two 'must be set columns', one called DATASET, and
one called LXBENUMMER.

Now, when I want to insert data into this target table I need to fill those two
columns with the new records I'm inserting. DATASET is easy as it'll always be a
fixed value, however, LXBENUMMER must be set with a number that comes from a
stored procedure.

If I do an exec sp_xal_seqno 1, 'DAT' I get a number back, which must be put in
LXBENUMMER with the new record.

An example-

insert tbl1
    (dataset,lxbenummer,col1,col2,col3,etc)
    select 'DAT',**value from stored procedure**, col1, col2, col3, etc
    from tbl2

How do I go about this?

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  It is very hard to debug code when you do not let us
see it.

Also, you do not know that a column is not anything like a field and
that a row is not a record.  After 20+ years of doing SQL and charging
a lot of money for consulting work in correctly schemas,  this is a
signal that you are really screwed up.

Well everywhere I look, row and record is the same and is used interchangeable.
Why should I think different? I don't see where I wrote field and compared it
with a column; but well you do know better.

Dude, you need to take a break from time to time, instead of throwing dirt at
those to aren't "worthy" compared to you. I'd see your point better if you had
kept it nicer.

I'm screwed up if I write nutty SQL? Man, if you think that, you /seriously/
need to take a break from anything relating to computers.

Anyway, I shall post again when I have a DDL ready.

From your attitude i'd be suprised if anybody outside of education has hired
you in the past couple of years!

You need to bring your skills up-to-date with what business wants now, not
15 years ago.

Times have significantly changed and you seem to have been left behind.

Show quoteHide quote

CREATE TABLE [tbl1] (
[DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
[LXBENUMMER] [int] NOT NULL ,
[col1] [varchar] (30) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [XALSEQ] (
[SEQID] [int] NOT NULL ,
[DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
[SEQNO] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO XALSEQ
VALUES (0,'DAT',1000)
GO
INSERT INTO tbl1
VALUES ('DAT',1000,'somerandomtext')

GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_xal_seqno   @increment INT, @dataset CHAR(3) AS
BEGIN TRAN  
UPDATE XALSEQ SET SEQNO = SEQNO + @increment    
WHERE DATASET = @dataset AND SEQID = 0  
SELECT SEQNO - @increment FROM XALSEQ    
WHERE DATASET = @dataset AND SEQID = 0  
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Kim Noer (kn@nospam.dk) writes:
General comment: don't use sp_ as the leading charcters in the name of
your objects. This prefix is reserved for SQL Server, and SQL Server will
first look in master for these objects.

There are number of options, of which some requires you to change
the procedure. I happen to have an article about this on my web site:
http://www.sommarskog.se/share_data.html.

Aye, I name all those I create differently, but alas, I did not get to chose the
name of the stored procedure, this one comes from MBS themself.

In my second post to Celko, I've included the structure of the tables, the SP
and tiny amount of sample data, if that helps point me in the correct direction
(I'm going to read up on your article anyway though).

Basically, the whole purpose is to make sure that every single row (or is it
record?) get their very own unique number. It would be exceedingly lovely if any
solution to this particular problem could be made generic if possible.

Thanks in advance.

Kim Noer (kn@nospam.dk) writes:
MBS? That's some third-party software?

It would certainly be more convenient if it was an OUTPUT parameter
rather than a result ser. If you can't change the procedure, you will
have to use INSERT EXEC.

Besides this looks funny:

   CREATE TABLE [XALSEQ] (
    [SEQID] [int] NOT NULL ,
    [DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
    [SEQNO] [int] NOT NULL
   ) ON [PRIMARY]
   GO

It doesn't have a primary key?

If there are no indexes on the table, this means that SQL Server will
have to take a out a table lock to give you a sequence number, and thus
no other will be able to get a sequence number simultaneously.

Erland Sommarskog wrote:

Microsoft Business Solution, they bought a company named Navision, which
makes applications that primarily talks flat fileish. In my case the
application uses cursors, a /lot/ of cursors. They do plan to change this,
but that's 2+ years in the future atleast.

I'm trying to insert data from the "outside" of this application, which
means that I can use all kinds of RDBMS tricks.

I'm sure can write a new, as long as I retain the functionality. Which one
of your methods would you use then?

Maybe they forgot, or the application might not be able to handle such a
situation.

Kim Noer (kn@nospam.dk) writes:
OUTPUT parameter.

Sorry for being slow here, but reading your example, and what you write seems to
be centered around inserting one single row. And I can't seem to figure how to
deal with more than one row, unless I go the cursor way, which I'd very much
like to avoid.

For some reason I forgot to note that I need to insert 1 to many rows, and each
row's lxbenummer must be unique, and the XALSEQ must be updated accordingly.

This is an example how I'd like to see it work, when I insert data into tbl1 -

dataset,lxbenummer,col1
'DAT',1000,'txt1'
'DAT',1001,'txt2'
'DAT',1002,'txt3'
and so it continues.

A trigger would probably be very handy here, but problem is, that the
application that uses the SQL server already got that routine (but in the
application), which would result in two triggers = something messy as the
result.

Kim Noer (kn@nospam.dk) writes:
Sorry, somehow I overlooked the significance of the @increment parameter.

Here is a way to do it:

   CREATE TABLE #tmp (ident int IDENTITY,
                       col1  ...)

    INSERT #tmp (...)
       SELECT ...
       FROM   sourctbl

    SELECT @rowc = @@rowcount

    EXEC get_seqno @incrment = @rowc, @dataset = 'DAT',
                   @seqno = @seqno OUTPUT

    INSERT targettbl (DATASET, LBEXNUMBER, ...)
       SELECT 'DAT', @seqno + ident - 1, ....
       FROM   #tmp

Thank you incredible much, never thought of using identity, which is rather
obvious for this kind of task.

Internal SQL Server error
COM+ Applications hanging
Who Added Indexing to Table?
sql weird
NOLOCK