Change value of column in last row when doing insert

Question

Post reply

Change value of column in last row when doing insert

webskater

SSCarpal Tunnel

Points: 4214
More actions
February 10, 2013 at 6:35 am

#288970

DECLARE @LastOne bit = 0
CREATE TABLE #tblTasks
(
TaskID int,
Task varchar(50),
LastOne bit,
)
GO
INSERT INTO #tblTasks (TaskID, Task, LastOne)
SELECT(TaskID, Task, @LastOne)
FROM tblOldTasks
When doing an insert like the one above - where I am inserting a hard-coded value of 0 for @LastOne column - is there any way of writing such that ...
For every row inserted - insert 0 (or false) as LastOne except for the last row - when LastOne should be 1.
I realise I can do it immediately after the Insert with an Update, but I wondered if there is some way to do it within the Select statement that is inserted.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

SQLAJ Ten Centuries Points: 1359 More actions · Answer 1

Use default argument for the column.

"DEFAULT

Specifies the value provided for the column when a value is not explicitly supplied during an insert. DEFAULT definitions can be applied to any columns except those defined as timestamp, or those with the IDENTITY property. If a default value is specified for a user-defined type column, the type should support an implicit conversion from constant_expression to the user-defined type. DEFAULT definitions are removed when the table is dropped. Only a constant value, such as a character string; a scalar function (either a system, user-defined, or CLR function); or NULL can be used as a default. To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT."

As seen here: http://msdn.microsoft.com/en-us/library/ms174979(v=sql.105).aspx

______________________________
AJ Mendo | @SQLAJ

mickyT SSChampion Points: 10360 More actions · Answer 2

Hi

You could try the following

INSERT INTO #tblTasks

SELECT TaskID,

Task,

CASE

WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = COUNT(*) OVER (PARTITION BY (SELECT NULL)) THEN 1

ELSE 0

END LastOne

FROM tblOldTasks

This assumes that the insert is not ordered

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

sku370870 (2/10/2013)
DECLARE @LastOne bit = 0 CREATE TABLE #tblTasks ( TaskID int, Task varchar(50), LastOne bit, ) GO INSERT INTO #tblTasks (TaskID, Task, LastOne) SELECT(TaskID, Task, @LastOne) FROM tblOldTasks
When doing an insert like the one above - where I am inserting a hard-coded value of 0 for @LastOne column - is there any way of writing such that ...
For every row inserted - insert 0 (or false) as LastOne except for the last row - when LastOne should be 1.
I realise I can do it immediately after the Insert with an Update, but I wondered if there is some way to do it within the Select statement that is inserted.

To answer that, we would need to know "which row is the last row?".

You realise that a SELECT has no guarantee of first or last row without an ORDER BY that guarantee a first or last row...?

Also, the order of a select does not reflect the order rows are inserted...that will depend on many other factors.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

SQLAJ (2/10/2013)
Use default argument for the column.
"DEFAULT
Specifies the value provided for the column when a value is not explicitly supplied during an insert. DEFAULT definitions can be applied to any columns except those defined as timestamp, or those with the IDENTITY property. If a default value is specified for a user-defined type column, the type should support an implicit conversion from constant_expression to the user-defined type. DEFAULT definitions are removed when the table is dropped. Only a constant value, such as a character string; a scalar function (either a system, user-defined, or CLR function); or NULL can be used as a default. To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT."
As seen here: http://msdn.microsoft.com/en-us/library/ms174979(v=sql.105).aspx

Please explain how adding a default to the table will clearly and certainly make a different entry for ONLY the last row inserted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 5

webskater

SSCarpal Tunnel

Points: 4214

February 11, 2013 at 9:09 am

#1586634

Thanks for that, I'll give it a try.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

sku370870 (2/10/2013)
DECLARE @LastOne bit = 0 CREATE TABLE #tblTasks ( TaskID int, Task varchar(50), LastOne bit, ) GO INSERT INTO #tblTasks (TaskID, Task, LastOne) SELECT(TaskID, Task, @LastOne) FROM tblOldTasks
When doing an insert like the one above - where I am inserting a hard-coded value of 0 for @LastOne column - is there any way of writing such that ...
For every row inserted - insert 0 (or false) as LastOne except for the last row - when LastOne should be 1.
I realise I can do it immediately after the Insert with an Update, but I wondered if there is some way to do it within the Select statement that is inserted.

"Lastone" relative to what exactly? Without an ORDER BY to relate to, it's meaningless garbage. With an ORDER BY, it's meaningful and also trivial to do.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQLAJ Ten Centuries Points: 1359 More actions · Answer 7

I don't believe I said,

"will clearly and certainly make a different entry for ONLY the last row inserted."

I was only trying to provided some information in order to help to further the thought process. Not sure why that would be wrong.

______________________________
AJ Mendo | @SQLAJ

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

SQLAJ (2/11/2013)
I don't believe I said,
"will clearly and certainly make a different entry for ONLY the last row inserted."
I was only trying to provided some information in order to help to further the thought process. Not sure why that would be wrong.

Not a problem. I thought you were on to something and wanted to know how you made a default that would do this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)