Triggers and String concatination
Cypher
I am trying to create a Card Number for clients, after inserting a new
record, based on a clientID field
and the year part of the date:
CardNo = Year(Date) + PadL(ClientID, 7, '0')
PADL is an UDF to left PAD a string.
I wrote the following trigger, but I get an error when execute it:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
USE [MyDB]
GO
/****** Object: Trigger [dbo].[tr_makeCardNo] Script Date: 01/04/2010
19:07:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_makeCardNo]
ON [dbo].[ClientData]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE ClientData
SET CardNo = STR(DATEPART(yyyy,C.DataEntryDate)) +
dbo.PADL(C.ClientID,7,'0')
FROM ClientData C
JOIN Inserted I ON C.ClientID = I.ClientID
END
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
This trigger works if I use either of the 2 strings but not when I
concatenate them as above.
I get an error about truncating a binary or string variable and the insert
is aborted but
THE IDENTITY FIELD IS INCREMENTED NONTHELESS!
Any help will be appreciated.
Plamen Ratchev
specify 4:
STR(DATEPART(yyyy, C.DataEntryDate), 4)
Or simply cast to CHAR(4):
CAST(DATEPART(yyyy, C.DataEntryDate) AS CHAR(4))
--
Plamen Ratchev
http://www.SQLStudio.com
Cypher
Now I need generate a barcode based on this card number and store it in the
database.
Is there a way to this using a trigger?
"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:cL-dnX7PsfPpt9_W...@speakeasy.net...
Bob Barrows
> Hi all,
>
> I am trying to create a Card Number for clients, after inserting a new
> record, based on a clientID field
> and the year part of the date:
>
> CardNo = Year(Date) + PadL(ClientID, 7, '0')
>
> PADL is an UDF to left PAD a string.
What does the function look like?
>
> I wrote the following trigger, but I get an error when execute it:
<snip>
> This trigger works if I use either of the 2 strings but not when I
> concatenate them as above.
> I get an error about truncating a binary or string variable and the
> insert is aborted but
> THE IDENTITY FIELD IS INCREMENTED NONTHELESS!
>
Well it IS an "After Insert" trigger ...
I used the following code to try and reproduce your problem but it ran
without error. What are you doing differently?
USE Test
GO
/****** Object: Trigger [dbo].[tr_makeCardNo] Script Date:
01/04/2010
19:07:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE ClientData (
ClientID int IDENTITY,
CardNo varchar(50) NULL,
DataEntryDate datetime NOt NULL)
GO
create function dbo.PADL(@str varchar(20),@final_length tinyint,
@padwith char(1))
returns varchar(256)
AS
BEGIN
return right(replicate(@padwith,@final_length) + @str, @final_length)
END
GO
CREATE TRIGGER [dbo].[tr_makeCardNo]
ON [dbo].[ClientData]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE ClientData
SET CardNo = STR(DATEPART(yyyy,C.DataEntryDate)) +
dbo.PADL(C.ClientID,7,'0')
FROM ClientData C
JOIN Inserted I ON C.ClientID = I.ClientID
END
GO
insert ClientData(DataEntryDate) values(getdate());
select * from ClientData
--
HTH,
Bob Barrows
Cypher
Thanks for your response.
The reason it worked for you and not for me is that my CardNo field is
char(11)
while yours is varchar(50).
regards
"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
news:uzGPHxWj...@TK2MSFTNGP05.phx.gbl...
Plamen Ratchev
trigger. I am not an expert on barcodes so cannot offer any T-SQL code. A quick search shows something that may be of help:
http://devshed.us/Blogs/tabid/227/EntryId/80/Function-to-encode-barcode-128A-in-T-SQL.aspx
Erland Sommarskog
> This trigger works if I use either of the 2 strings but not when I
> concatenate them as above.
> I get an error about truncating a binary or string variable and the insert
> is aborted but
> THE IDENTITY FIELD IS INCREMENTED NONTHELESS!
succeeds or not. If you need to have consecutive numbers, you should
not use IDENTITY.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
--CELKO--
language. But you are using procedural code all over the place, in a
TRIGGER and a UDF! You are still thinking as if you were writing for
magnetic tape files or punch cards. You even use the word "record"
because your mindset is all wrong.
There is no such thing as an IDENTITY field. First of all, a column
is nothing like a field. It is a proprietary, non-relational table
property. Programmers who do not know RDBMS often use it to get a
"record number" so that they can make their SQL look like a
sequential file -- magnetic tape files or punch cards.
The DDL skeleton would be something like this:
CREATE TABLE Clients
(client_id CHAR(7) NOT NULL PRIMARY KEY
CHECK(client_id LIKE '<pattern>'),
signup_date DATE DEFAULT CURRENT_DATE NOT NULL,
(<build card number here>) AS card_nbr,
etc);
You need a way of getting a client identifier that can be validated
and verified. IDENTITY has neither and it cannot even give you
consecutive numbers. I would not expose the client id in the card
number -- do you write your PIN on your bank card?
You need to learn how to follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.
Cypher
cards on a DEC System 10? But seriously:
- I need the computed field in there because another dumb ID card printing
program will use it to print an ID card. Also it is a foreign key field in
other
tables that contain data about the client. The id card is used later to
retrieve
data using either a barcode or a Mag card reader.
- I use the term IDENTITY because that is what MS calls an auto-increment
field.
- Consecutive numbers are not a requirement, but after the testing is done
and
the database is on a production server, I expect it will be. I was somewhat
surprised by the SQL Server behavior because I expected the operation
to be rolled back on error.
- This is not my day job but I am beginning to like it :-)
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:687c5bf6-50b5-4a56...@35g2000yqa.googlegroups.com...
Tony Rogerson
> "record number" so that they can make their SQL look like a
> sequential file -- magnetic tape files or punch cards.
"Some" programmers do, not a lot.
"Most" programmers use it to create a stable (none changing) surrogate key
that can be used to aid concurrency, consistency and all those things I keep
telling you - read Date.
> You need a way of getting a client identifier that can be validated
> and verified. IDENTITY has neither and it cannot even give you
> consecutive numbers.
That reminds me - the left/right pair of the nested sets model you
popularise cannot be "validated" and "verified" because on day 1 the
left/right pair might be 12,1 and the next day or even the next minute it
might be 12,5; so, any table using the left/right pairing is now
disconnected and orphaned.
So, my point - people in glass houses should not throw stones!
The stable and unchangeable value returned by IDENTITY on insert can easily
be verified back - once inserted the value can never change.
Gaps occur only on rollbacks but the benefits in concurrency massively out
weigh this problem; the architect needs to balance that with a "real" need
for an incrementor without gaps in which case they must destroy concurrency
and use MAX( x ) + 1 with some severe locking.
--ROGGIE--
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:687c5bf6-50b5-4a56...@35g2000yqa.googlegroups.com...
Uri Dimant
> Why did you store a computed column in a table?
SQL Server estimates the number of rows satisfying this condition using the
column statistics ?
What do you think about those approaches?
WHERE UnitPrice * Quantity > 100
and ADDING a computed column on AS UnitPrice * Quantity
SQL Server will detect the use of the computed column in the second example
and it will create statistics on the computed column. The statistics will
allow the optimizer to determine the appropriate cardinality estimation on
the filter.
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:687c5bf6-50b5-4a56...@35g2000yqa.googlegroups.com...
Erland Sommarskog
> Thanks, I needed that! How did you know I am old enough to have used
> punched cards on a DEC System 10? But seriously:
No one needs Joe Celko's meaningless posts.
> - Consecutive numbers are not a requirement, but after the testing is
> done and the database is on a production server, I expect it will be. I
> was somewhat surprised by the SQL Server behavior because I expected the
> operation to be rolled back on error.
The fact that IDENTITY is not contiguous is actually a feature: this permits
parallel processes to insert rows without blocking each other.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Tony Rogerson
You can also persist the computed column so long as its deterministic and
index it, saving you having to store the data twice.
In data modelling terms VIN should actually be a computed column - most
encoding "keys" should be because they should be stored in their constituent
parts.
Tony.
"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:Op#OaBejK...@TK2MSFTNGP05.phx.gbl...
--CELKO--
Because I am old enough to have to remember to say "main Storage"
instead of "core memory"! And I still think that PIP was the coolest
utility program ever.
>> - I need the computed field [sic] in there because another dumb ID card printing program will use it to print an ID card.<<
One of my standard rants is to learn the differences between a field
and a column. Fields are physical things inside records, records are
inside files. Things are sequential and contiguous. The data has
meaning from the host program using it -- that is, "READ a,b,c FROM
FileX;" is not the same as "READ c,a,b FROM FileX;" but "SELECT a,b,c
FROM TableX;" returns the same data as "SELECT c,b,a FROM TableX;"
since order does not matter. Columns,rows and entire tables do not
have to be a physical unit of storage in the database.
>> Also it is a foreign key field [sic] in other tables that contain data about the client. The id card is used later to
retrieve data using either a barcode or a Mag card reader. <<
The REFERENCES should not be a problem. But having client data spread
over multiple table could be a real problem. I would assume that the
client_id is the key and the card number is not. You identify an
automobile by its VIN, not by the local parking space card number. You
can re-issue a lost card; you cannot issue a new VIN because it is a
real identifier. This is another important difference between files
and RDBMS; there are a lot more!
In the old file systems, we had data all over the place; the goal of
DBMS was to get it all in one place. Be careful and do not commit the
errors of denormalization and attribute splitting.
>> - I use the term IDENTITY because that is what MS calls an auto-increment field [sic]. <<
Actually, it is a count of the PHYSICAL insertion attempts at the
table level. A rollback will not re-set the IDENTITY, so you get gaps,
this is soooo non-relational, it is not portable, etc.
>> - Consecutive numbers are not a requirement, but after the testing is done and the database is on a production server, I expect it will be. <<
I would think that consecutive numbers area bad idea. They expose
information in violation of basic security requirements -- i.e.
passwords and identification codes ought to be hard to guess, and easy
to validate (check digits and regular expressions are good) and
verified by a trusted source.
When I have had to do this kind of thing, I generate a list of
unpredictable numbers and then issue them with a sign-out system. I
am also old enough to have wor5ke4d in the Cold War Era :)
>> - This is not my day job but I am beginning to like it :-) <<
Have you see the IT market today? Keep the day job; it is important.
Even if the day job includes the technical term "Would you like fries
with that?"
On a more serious note, get a copy of my THINKING IN SETS book.
Getting over to RDBMS and SQL is a change of mindset from files and
procedural programming. Remember what a bitch it was to learn
recursion, weak and strong typing, etc.? Much as I hate the cliche,
SQL is another major paradigm shift.
--CELKO--
No, on Day #1, the hierarchical pair (12, 1) is rejected because it
fails validation. The pair must conform to the constraint (lft <
rgt). As I have said for over ten years, you should do this in Full
Standard SQL-99 with a CHECK() or CREATE ASSERTION. In SQL Server and
other lesser SQLs, you can fake it with a VIEW, like this skeleton
CREATE TABLR RawTree
(node_id char(10) DEFAULT 'VACANT' NOT NULL
REFERENCES Nodes (node_id)
ON UPDATE CASCADE
ON DELETE SET DEFAULT,
-- PRIMARY KEY () -- business rules allow node_id or (lft, rgt),
lft INTEGER NOT NULL CHECK (lft > 0),
rgt INTEGER NOT NULL CHECK (rgt > 1),
CONSTRAINT valid_pair_ordering
CHECK (lft < rgt),
CONSTRAINT valid_pair_spacing -- business rules for no gaps?
CHECK ((rgt - lft) % 2 = 0);
Now fake the CREATE ASSERTION:
CREATE VIEW ValidatedTree (node_id, lft, rgt)
AS
SELECT node_id, lft, rgt
FROM Raw_Tree
WHERE NOT EXISTS -- no range overlaps
(SELECT *
FROM Raw_Tree AS T1, Raw_Tree AS T2
WHERE T1.lft BETWEEN T2.lft AND T2.rgt
AND T1.rgt BETWEEN T2.lft AND T2.rgt
AND (T1.lft <> T2.lft OR T1.rgt <> T2.rgt));
Warning, if you get directly the Raw_Tree base table, you can mess up
things. You need DDL, DML and DCL working together. For kicks, write
the same validations for the adjacency list model. The best I was able
to do was a trigger with procedural code to traverse the tree looking
for cycles.
>> The stable and unchangeable value returned by IDENTITY on insert can easily be verified back - once inserted the value can never change. <<
Actually, this forum has a quite a few postings from forgetting to
set the right flags in BCP and scrambling the IDENTITY values. There
is no "trusted source" for verification since the IDENTITY is a local
table property. Compare this to the manufacturer for a part number
>> Gaps occur only on rollbacks but the benefits in concurrency massively out weigh this problem; the architect needs to balance that with a "real" need for an incrementor without gaps in which case they must destroy concurrency and use MAX( x ) + 1 with some severe locking. <<
I have the CREATE SEQUENCE statement when I am using DB2, Oracle,
etc. I do not know if/when SQL Server will add it, but we did get the
MERGE and ANSI/ISO temporal stuff a few decades late, so I have hope.
Tony Rogerson
> fails validation. The pair must conform to the constraint (lft <
> rgt). As I have said for over ten years, you should do this in Full
> Standard SQL-99 with a CHECK() or CREATE ASSERTION. In SQL Server and
> other lesser SQLs, you can fake it with a VIEW, like this skeleton
On Day #1, left is 1, right is 12
On Day #2 the same row the left, right columns are changed to left = 5,
right = 12
In another table that holds commission against the hierarchy at the time
holds the composite foreign key left = 1, right = 12
That row no longer exists in the hierarchy, worse still - it may be an
entirely different row.
That is the problem when you allow a key to be changeable which when you
treat the coordinate in the nested set a key happens because the hierarchy
is fluid and YOU CANNOT VALIDATE / VERIFY IT because it changes - there is
no getting away from that.
In the enumerated path model you simply use start/end windowing and the
problem is resolved, you can always determine the hierarchy at a given time.
> Actually, this forum has a quite a few postings from forgetting to
> set the right flags in BCP and scrambling the IDENTITY values. There
> is no "trusted source" for verification since the IDENTITY is a local
> table property. Compare this to the manufacturer for a part number
Be very careful pointing out stuff as fact stuff you don't understand and
are just repeating because you've seen a post and misunderstood it.
You cannot use BCP or BULK INSERT for the method you propose, the MAX( col )
+ 1; with that method you are limited to inserting one row at a time -
entirely serialised across the entire database - with BCP and BULK INSERT
you can have many parallel streams and have a very high insert throughput.
The manufacture part number verifies back to a manufacturer database where
the incremental numbering method may be anything, IDENTITY, MAX( x ) + 1,
next number or manually dreamt up by a person - but, it will be one of them.
You lost this battle years ago, but I'm more than prepared to go on - shall
we?
> I have the CREATE SEQUENCE statement when I am using DB2, Oracle,
> etc. I do not know if/when SQL Server will add it, but we did get the
> MERGE and ANSI/ISO temporal stuff a few decades late, so I have hope.
The CREATE SEQUENCE has limited scope; where you say IDENTITY is limited to
a local table property; the CREATE SEQUENCE is from memory to a database -
not that much difference; it is certainly not unlimited and applicable off
that machine running DB2, Oracle etc...
Whilst an incremental sequence without gaps would be useful there is no mass
call for it because a) we have IDENTITY which satisfies most requirements
and b) there are plenty of examples of next number, max( x ) + 1 - some
actually document the concurrency consequences.
Shall we now talk about concurrency cleko??
--ROGGIE--
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:768d57e6-19a0-4f88...@e27g2000yqd.googlegroups.com...
Erland Sommarskog
> You cannot use BCP or BULK INSERT for the method you propose, the MAX(col)
> + 1; with that method you are limited to inserting one row at a time -
This is in correct. You can certinly insert many rows in one go.
However, until you have committed, no other process can insert, so
there is serialisation. Which may be utterly bad, or simply no issue.
When it is an issue, IDENTITY is certainly worth considering. But
where there are no concurrency issues in sight there is little reason
to use IDENTITY, since there are a couple of usability problems with
it - you see them on the newsgroups all the time.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
--CELKO--
The fact that IDENTITY has to queue the rows in a multi-row insertion
in a non-deterministic fashion has been a big problem for me. It
means there is no hope of validation.
I need to look at the Standard CREATE SEQUENCE statement in depth to
see if that works. But I also need to play with
INSERT INTO Foobar (foo_seq, ..)
VALUES (
(SELECT COALESCE (MAX(Foobar.foo_seq), 0)
+ ROW_NUMBER() OVER (ORDER BY some_sequencing_columns))
FROM NewFoobar)
..);
or worse
Tony Rogerson
>
You miss read what I was saying Erland.
Unless you use an instead of trigger combined with a cursor you cannot
calculate the next number because you need to physically do a SELECT max(
id ) + 1 query.
You can imagine the performance of that!
> However, until you have committed, no other process can insert, so
> there is serialisation. Which may be utterly bad, or simply no issue.
>
Correct.
> When it is an issue, IDENTITY is certainly worth considering. But
> where there are no concurrency issues in sight there is little reason
> to use IDENTITY, since there are a couple of usability problems with
> it - you see them on the newsgroups all the time.
Except the added complexity I mention above, which frankly comes with its
own issues like the TSQL developer fully understanding triggers which a lot
of people don't.
Tony.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CF7ECA48...@127.0.0.1...
Tony Rogerson
> in a non-deterministic fashion has been a big problem for me. It
> means there is no hope of validation.
Obviously, the systems you've worked on in the past have been very small
chunks of data.
If you are loading lots of data you need parallel streams, absolutely in a
parallel load scenario the number given is not deterministic but often we
don't care, after all the ID 12 given by the issuing database to the email
bl...@xyz.com AT INITIAL INSERT is meaningless so long as it NEVER changes.
> see if that works. But I also need to play with
>
> INSERT INTO Foobar (foo_seq, ..)
> VALUES (
> (SELECT COALESCE (MAX(Foobar.foo_seq), 0)
> + ROW_NUMBER() OVER (ORDER BY some_sequencing_columns))
> FROM NewFoobar)
> ..);
Remember to check it on each product specifically for concurrency
implications - I will be checking your research for errors. I already know
the outcome on SQL Server.
--ROGGIE--
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:39a88c3c-d6b6-4608...@u7g2000yqm.googlegroups.com...
Uri Dimant
> You can also persist the computed column so long as its deterministic and
> index it, saving you having to store the data twice.
Yep, that what I meant as well
"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:9053F53A-E020-478A...@microsoft.com...
Erland Sommarskog
>> This is in correct. You can certinly insert many rows in one go.
>>
>
> You miss read what I was saying Erland.
>
> Unless you use an instead of trigger combined with a cursor you cannot
> calculate the next number because you need to physically do a SELECT max(
> id ) + 1 query.
>
> You can imagine the performance of that!
If there is no index on id, performance will certainly be a problem.
However, I would expect an index to exist on the primary key. In fact,
I would take it for granted.
If you insert many rows at one time, there is no problem to calculate
the ids row all rows with row_number, as Celko demonstrated.
In fact, in SQL 2005, this is the only way you can do this, and know
which row that got which ID. Say that you have data for two tables
in some semi-structured way, for instance an XML document. Take orders
and order details.
You insert the orders into the Orders table that has an IDENTITY column,
but of the data you insert, there is nothing you can correlate with
the IDENTITY column, even if there is information for this in the XML
document. Thus, the OUTPUT clause is not going to help you. You have
but one possibility to sort this situation out: run a cursor.
In SQL 2008, there is a way out: rather than using INSERT you can insert
the rows with MERGE, and then you can save the identificiation in the
XML document to a table variable in the OUTPUT clause.
Tony Rogerson
> However, I would expect an index to exist on the primary key. In fact,
> I would take it for granted.
Even if there is an index performance is going to suck unless you play with
the commit size of the BCP and do it in chunks.
The cursor in the INSTEAD OF trigger will be a killer and because of the
extra transaction logging etc... performance will be dramatically slower.
You can't parallel stream it either.
On normal INSERT you can use the OUTPUT clause - I do this myself in some
jobs I have for loading debt files from collection agencies.
Tony.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CF876DE2...@127.0.0.1...
Erland Sommarskog
> Even if there is an index performance is going to suck unless you play
> with the commit size of the BCP and do it in chunks.
Obviously, it is difficult to run a SELECT MAX from BCP. Then again,
that is not really the typical usage scenario.
> The cursor in the INSTEAD OF trigger will be a killer and because of the
> extra transaction logging etc... performance will be dramatically slower.
Which INSTEAD OF trigger? I have not proposed any. Celko has not proposed
any. Overall, using an INSTEAD OF trigger to generate a key does not
strike me as the best idea.
> You can't parallel stream it either.
Yes, if you need to support high-concurrency inserts, there is all
reason to use IDENTITY. But that is also more or less precise scenario
where you should use it.
> On normal INSERT you can use the OUTPUT clause - I do this myself in some
> jobs I have for loading debt files from collection agencies.
Yes, but you can only retrieve coluns that were inserted into the
table. You cannot retrieve columns that were only in the source table.
Which means that if you need to know the IDENTITY values to use for
inserting into a subtable, you lose.
Tony Rogerson
> that is not really the typical usage scenario.
But that is what we were talking about....
The whole point was cleko saying bcp scrambling identity to which I replied
you can't effectively use bcp unless you use an instead of trigger and
max( x ) + 1 etc.... go back and reread, hence my point about you miss
reading what I'd posted.
> Which INSTEAD OF trigger? I have not proposed any. Celko has not proposed
> any. Overall, using an INSTEAD OF trigger to generate a key does not
> strike me as the best idea.
>
Please go back and re-read what this thread is about Erland.
That is the whole point, in order to use BCP you would need to use MAX(
isd ) + 1 and to do that you need an INSTEAD OF trigger!
> Yes, but you can only retrieve coluns that were inserted into the
> table. You cannot retrieve columns that were only in the source table.
> Which means that if you need to know the IDENTITY values to use for
> inserting into a subtable, you lose.
Absolutely, but if you wanted source columns they are obviously important so
you are likely wise to store them!
Tony.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CF8A68D4...@127.0.0.1...
Erland Sommarskog
>> Obviously, it is difficult to run a SELECT MAX from BCP. Then again,
>> that is not really the typical usage scenario.
>
> But that is what we were talking about....
>
> The whole point was cleko saying bcp scrambling identity to which I
> replied you can't effectively use bcp unless you use an instead of
> trigger and max( x ) + 1 etc.... go back and reread, hence my point
> about you miss reading what I'd posted.
there not really any in the source, IDENTITY is the most convenient
option. Not the less, since Microsoft now are saying that they guarantee
that the IDENTITY values are correlated with the order of the records in
the file.
I would not consider using an INSTEAD OF trigger of being an option. But
there are two more options:
1) Modify the file before the load to add an identifier - not realistic if
the file is 2GB, though...
2) Use the Bulk-Load API and bulk load from variables, then you can add
your own counter. Certainly takes more work.
Yet a possibility is to load the file with generating any key, but
adding that a later point, for instance when you copy from staging to
target. But then you havz zero chance to get correlation with how
the file looked like.
And obviously, if you bulk load to a staging table, it doesn't matter
if that table has IDENTITY - you still don't need to have it in your
target table, if you don't feel like.
TheSQLGuru
certain order we can FORCE that by setting MAXDOP 1 for the INSERT-driving
SELECT statement, thus removing parallel streaming at the expense of
performance.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:860B8171-AB26-4D9E...@microsoft.com...