sql server programming: Using output with insert (SQL2005) -- sql server programming

You're in the

sql server programming

group:

Using output with insert (SQL2005)

Re: Using output with insert (SQL2005)

David Portas
5/12/2006 1:15:54 PM

sql server programming:

[quoted text, click to view]

Why don't you have any keys? What is the point of inserting duplicate
rows into the second table? You can do it by eliminating the duplicates
as in the following example. We might be able to help you better if you
explain what you really want to achieve.

INSERT INTO #CJD2( Val )
SELECT DISTINCT Val
FROM #CJD a ;

SELECT C2.RowId, C1.Val, C1.RowNum
FROM #CJD AS C1
JOIN #CJD2 AS C2
ON C1.Val = C2.Val ;

RowId Val RowNum
----------- ----------- -----------
1 50 1
2 75 2
1 50 3

(3 row(s) affected)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Re: Using output with insert (SQL2005)

David Portas
5/12/2006 3:20:13 PM

[quoted text, click to view]

That's exactly what DISTINCT is for. Like so many SQL problems it seems
that this one is rooted in the design: Garbage in, garbage out. Did you
try the solution I posted?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Re: Using output with insert (SQL2005)

Jim Underwood
5/12/2006 3:45:11 PM

I don't understand?

To get the values into the second table you can do:
Insert Into #CJD2 (val) select Val from #CJD;

However, this will not guarentee the order of the rows, so your IDs may not
match up.

You can add an order by to it:
Insert Into #CJD2 (val) select Val from #CJD order by RowNum;

but the identity will only match the rownum if they are contiguous.

You could turn on identity insert an insure that they always match, but I'm
not sure thats what you want.

Why not just create a view?

Select RowNum, Val from #CLD;

[quoted text, click to view]

Using output with insert (SQL2005)

Colin Dawson
5/12/2006 7:29:20 PM

Hi all.

Here's a question that I was asked today. I'm farily confident in the
answer, but want to make sure that I've not missed something.

Here's the problem.

Create Table #CJD (

RowNum integer,

Val integer )

Insert Into #CJD( RowNum, Val ) values ( 1, 50 )

Insert Into #CJD( RowNum, Val ) values ( 2, 75 )

Insert Into #CJD( RowNum, Val ) values ( 3, 50 )

Create Table #CJD2(

RowId integer Identity(1,1),

Val integer )

Insert Into #CJD2( Val )

Output

inserted.RowId,

inserted.Val,

a.RowId

Select Val

From #CJD a

drop table #CJD

drop table #CJD2

The SQL above doesn't exactly work, but it demonstrates the problem very
well. What I want to do is insert Val into #CJD2, then select the RowId
from #CJD2 and link it to RowNum from #CJD.

Any suggestions?

Regards

Colin Dawson

www.cjdawson.com

Re: Using output with insert (SQL2005)

Colin Dawson
5/12/2006 8:50:35 PM

Hi Guy.

What I'm trying to achieve is to insert data into #CJD2. This will trigger
the identity, the output part of the insert will return the freshly assigned
identity values. The final thing is that I want to be able to tie the
RowNum column to the identity.

Why would I want to do this? As I explained in the OP, a collegue asked me
the question so I'm not really sure about the real story. My best guess is
something like this...

The application has a grid, this grid contains several rows of data which is
to be inserted into a table. The data on the grid is wrapped up into an XML
document, then sent to a stored procedure. The XML is then parsed and used
for the insert. I do have a temporary key - i.e. the row number on the
grid, but this is not really useful once the grid is destroyed. The idea
is to insert the data, which has no key of it's own. The output is used to
retieve the identities, match it to the original grid items, then return the
data as XML to the client application. The new identity values can then be
saved against each row to provide the key.

Currently this scenario can be accomplished by sending the xml to the stored
procedure then using a cursor to call multiple insert statements. It would
be alot more efficient to complete the entire move in one step.

I do agree that it would be alot easier to code using the cursor, but there
are performance implications of doing multiple individual inserts especially
if there are triggers on the table.

I hope this explains the concept a little more.

Regards

Colin Dawson
www.cjdawson.com

p.s. in the original post, I hinted that I suspected that I already knew the
answer. My though is that it is not really possible to do this.

[quoted text, click to view]

Re: Using output with insert (SQL2005)

Colin Dawson
5/13/2006 12:00:00 AM

I didn't try the solution, but I do understand what you're getting at.

I don't think that I'm explaining the problem correctly. I need to retain
all the values, as they represent items entered by the user. The duplicated
need to be assigned with different numbers from the identity.

Let me recap. Here's the data that the user entered into the grid....

GridRow Data
1 100
2 50
3 75
4 20
5 50
6 10

When the Data is inserted into the table it will be assigned with
identities.

In the table

RowId(Identity) Data
70006 100
70007 50
70008 75
70009 20
70010 50
70011 10

Using the insert and output I want to return the following dataset....

GridRow RowId(Identity) Data
1 70006 100
2 70007 50
3 70008 75
4 70009 20
5 70010 50
6 70011 10

Currently it is possible to do this by using a loop. Here's how I'd do it
with a cursor

Create Table #tmpTable(
GridRow integer,
Data integer,
RowId integer
)

Declare
@GridRow integer,
@Data integer,
@RowId Integer

Declare LoopCursor Cursor Fast_Forward For
Select
GridRow,
Data
From InputTable

Open LoopCursor Integer
Fetch Next From LoopCursor into @GridRow, @Data

While @@Fetch_Status = 0
Begin
Insert into DataTable( @Data )
Set @RowId = Scope_Identity()

Insert Into #tmpTable( GridRow, Data, RowId ) Values ( @GridRow, @Data,
@RowId )

Fetch Next From LoopCursor into @GridRow, @Data
End

Close LoopCursor
DeAllocate LoopCursor

Select
GridRow, RowId, Data
From #tmpTable

Drop Table #tmpTable

If SQL supported the syntax, that I posted in the orignal post it would save
all that code!

Regards

Colin Dawson
www.cjdawson.com

Re: Using output with insert (SQL2005)

David Portas
5/13/2006 6:19:41 AM

[quoted text, click to view]

Let's take a look at the source data first:

GridRow Data
1 100
2 50
3 75
4 20
5 50
6 10

You've said that this comes from a data entry grid and that this is the
information the user wants to record in the database. But what is being
represented here? Apparently it is a SEQUENCE of values. If there were
no sequence to the values then there would be no logical reason to
store the value 50 on two rows - even if the user chose to enter it in
that manner. So I'll assume from hereon that a sequence is what we are
dealing with. If I'm wrong then the answer is obvious: eliminate the
duplicate rows. Redundancy harms both efficiency and integrity.

Now consider your target table under the assumption that this is a
sequence. The RowId IDENTITY column is totally unsuitable for the
purpose of recording a sequence. It is unsuitable because you cannot
control the order in which the IDENTITY values are assigned. In other
words there is no guarantee that the IDENTITY will preserve the
sequence that the user entered in the grid. The only exception is in a
single user system where you insert single rows at a time - but in that
case, why use IDENTITY at all? You could also use IDENTITY_INSERT to
insert explicit values for the RowId and then your problem is solved,
but again, in that case you don't need IDENTITY and would be better off
without it.

Here's an alternative design:

CREATE TABLE Sequences (SequenceNo INTEGER NOT NULL IDENTITY PRIMARY
KEY /* some other data that identifies the sequence...? */);

CREATE TABLE SequenceValues (RowId INTEGER IDENTITY NOT NULL PRIMARY
KEY, SequenceNo INTEGER NOT NULL REFERENCES Sequences (SequenceNo),
GridRow INTEGER NOT NULL, Data INTEGER NOT NULL, UNIQUE
(SequenceNo,GridRow));

DECLARE @SequenceNo INT;

INSERT INTO Sequence (...) /* Create a new sequence */
SET @SequenceNo = SCOPE_IDENTITY();

INSERT INTO SequenceValues (SequenceNo, GridRow, Data)
SELECT @SequenceNo, GridRow, Data
FROM Grid ;

The crucial difference here is that the SequenceValues table has a key
that is not an IDENTITY. An IDENTITY column is designed for surrogate
keys and should not be the only key of a table. The fact that it is the
only key in your original design indicates denormalization and that is
what caused your original problem. Your design didn't preserve any
entity integrity but you were then trying to assume a correspondence
between duplicate rows in the two tables where no such correspondence
existed. That's why you were forced to use a cursor. A point
demonstrated many times is that flawed designs often lead to
over-reliance on cursors.

I've made a lot of assumptions and the truth is that without analyzing
your business scenario in detail it's difficult to give totally
reliable design advice. However, I'm still certain that this is a
problem with your design and/or functional requirements.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Re: Using output with insert (SQL2005)

David Portas
5/13/2006 11:10:59 AM

[quoted text, click to view]

You are right that SQL Server cannot perform a join based on
information that isn't there. Does that surprise you?

Erland has posted a workaround. His solution does force you to assume
that the IDENTITY sequence will always match the ORDER BY clause and
that no other inserts will happen in parallel. I personally would not
like to rely on it and I have seen cases where the IDENTITY sequence
doesn't match the ORDER BY clause in an INSERT statement.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Re: Using output with insert (SQL2005)

David Portas
5/13/2006 11:50:57 AM

For completeness, here's a modified version of Erland's query that
doesn't depend on the insertion order:

SELECT a.RowId, b.RowNum, a.Val
FROM (SELECT RowId, Val,
identrowno = row_number()
OVER (PARTITION BY Val ORDER BY RowId)
FROM CJD2) AS a
JOIN (SELECT RowNum, Val,
RowNumrowno = row_number()
OVER (PARTITION BY Val ORDER BY RowNum)
FROM CJD) AS b
ON a.identrowno = b.RowNumrowno
AND a.Val = b.Val
ORDER BY b.RowNum ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Re: Using output with insert (SQL2005)

Erland Sommarskog
5/13/2006 4:18:28 PM

Colin Dawson (newsgroups@cjdawson.com) writes:
[quoted text, click to view]

I agree with David that the root of the evil is the IDENTITY column,
although from a different angle. I don't mind the duplicate values;
maybe they serve some purpose that I don't know about.

But had the key column not had had the IDENTITY property, this would
be a piece of cake. You would just add RowNum to the current MAX value
for the key in the target table.

This can of course be achieved with the current design as well, if you
use SET IDENTITY_INSERT, but this will require dealing with the extra
permissions required.

--
Erland Sommarskog, SQL Server MVP, esquel@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

Re: Using output with insert (SQL2005)

Colin Dawson
5/13/2006 4:20:00 PM

I understand that. The sequence is only relevant during the life of the
grid. If you look at excel, it's the grey numbers down the left hand side
of the screen. i.e. it doesn't really exist, and once the data has been
stored in the table has no relevant what so ever. I've included it in the
example because I wanted to demostrate a point.

The important sequence is the rows that are entered into the database. The
duplicate rows are there to demonstrate that simply joining on the inserted
values is not enough.

I'm being awkward and stubbern about this as I don't think that SQL Server
can cope with this exact scenario using the output command. That's what I'm
getting at.

Regards

Colin Dawson
www.cjdawson.com

[quoted text, click to view]

Re: Using output with insert (SQL2005)

Erland Sommarskog
5/13/2006 5:52:17 PM

Colin Dawson (newsgroups@cjdawson.com) writes:
[quoted text, click to view]

I think I have a solution. It's based on the fact that when you use
INSERT SELECT ORDER BY, the for a table with an identity column, the
ORDER BY is refelected in the generated identity values. That, and the
fact the OUTPUT clause can insert into a table variable brings us what
comes below.

Pretty it isn't, and I still prefer a solution without the IDENTITY
column in the target table.

Create Table CJD (
RowNum integer,
Val integer )

Insert Into CJD( RowNum, Val ) values ( 1, 50 )
Insert Into CJD( RowNum, Val ) values ( 2, 75 )
Insert Into CJD( RowNum, Val ) values ( 3, 650 )
Insert Into CJD( RowNum, Val ) values ( 4, 450 )
Insert Into CJD( RowNum, Val ) values ( 5, 85 )
Insert Into CJD( RowNum, Val ) values ( 6, 50 )

Create Table CJD2(
RowId integer Identity(221, 13),
Val integer )
go
DECLARE @output TABLE (RowId int NOT NULL)

Insert Into CJD2 (Val )
Output inserted.RowId INTO @output
Select Val
From CJD
ORDER BY RowNum

SELECT a.RowId, b.RowNum, a.Val
FROM (SELECT RowId, Val,
identrowno = row_number() OVER (ORDER BY RowId)
FROM CJD2) AS a
JOIN (SELECT RowNum,
RowNumrowno = row_number() OVER (ORDER BY RowNum)
FROM CJD) AS b
ON a.identrowno = b.RowNumrowno

go
drop table CJD
drop table CJD2

--
Erland Sommarskog, SQL Server MVP, esquel@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

Re: Using output with insert (SQL2005)

Colin Dawson
5/13/2006 6:09:31 PM

Yes, I agree that it's not pretty, but it should perform alot better than
the cursor based solution, in the there's only really one insert taking
place. I didn't know that you could combing insert output and into at the
same time.

Thanks for the help.

I'll remember the other parts of this discussion too, as there are a number
of important points which should be explored before implementing this.

Regards

Colin Dawson
www.cjdawson.com

[quoted text, click to view]

Re: Using output with insert (SQL2005)

Colin Dawson
5/13/2006 6:22:01 PM

Thanks for your patience David. It really was worth drilling deep into the
problem. Maybe this is worth a suggestion to MS, for a future version of
SQL. Basically, so that columns from the original table can be "output"ed
even though they're not inserted.

Once again. Thanks for the advice and your patience.

Regards

Colin Dawson.
www.cjdawson.com

[quoted text, click to view]

Re: Using output with insert (SQL2005)

Erland Sommarskog
5/13/2006 7:24:07 PM

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
[quoted text, click to view]

But here you are assuming that Val was not in the target table before
the insert, aren't you? Yes, it's kind of funny to insert 50 in the
table if it is already there. But maybe in the real-world case, there
are umpteen columns instead of Val, and enumerating them all would be
1) boring 2) error-prone 3) complicated if there are nullable columns
4) require a change to the code when a new column is added.

I agree that relying on IDENTITY generated in order does not give an
entirely pleasant feeling. But the message from MS os clear on this
point, particularly for SQL 2005, that if you would find a deviation,
it's a bug.

--
Erland Sommarskog, SQL Server MVP, esquel@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

Re: Using output with insert (SQL2005)

David Portas
5/14/2006 3:32:16 AM

[quoted text, click to view]

You're right. Fundamentally you can't retrieve the inserted data unless
you have an alternate key or use the OUTPUT clause. In this case you
could use OUTPUT to populate a table variable and then plug that into
the query in place of CJD2.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.

View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008

home · blog · groups

Questions? Comments? Contact the dn