Workaround for INSERT with columns and values on the same line?

Question

Post reply

Workaround for INSERT with columns and values on the same line?

a648

Old Hand

Points: 381
More actions
April 14, 2011 at 6:40 am

#237612

It make me cringe to specify columns and value in two separate lists like this
insert into TargetTable (
ColumnName1,
ColumnName2,
ColumnName3)
values (
@value1,
@value2,
@value3)
- I'd rather have a series of ColumnName=@value lines, like an UPDATE statement.
My question is : just how much of a SQL sin would it be to break it into two steps :
1) INSERT - add a new record with just the unique key set
2) UPDATE - add the rest of the values (for that key's row), all named as I like
How terrible is that? Would I be roundly mocked by all who see it? 😀
I'd be using it for a quick import of data from a spreadsheet, merging with existing data, so I'd be having to check whether the key's row exists or not anyway (using a WHERE NOT EXISTS( SELECT .. etc.. in the first INSERT step).
Is that a neat way to tie up column names and values or am I an idiot for making it twice as slow? Or is it? Does it matter for a such a simple process?
Thanks...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

a648 (4/14/2011)
It make me cringe to specify columns and value in two separate lists like this

Why? That's standard SQL syntax used by all relational database systems.

Insert into table (column list)

Values (list)

or

Insert into table (column list)

Select (column list)

FROM ...

My question is : just how much of a SQL sin would it be to break it into two steps :
1) INSERT - add a new record with just the unique key set
2) UPDATE - add the rest of the values (for that key's row), all named as I like
How terrible is that? Would I be roundly mocked by all who see it? 😀

Terrible. If I ever saw that in code in any of my systems it would fail code review on the spot.

Firstly if the database designer has done their job there will be columns defined as not null that will fail the insert.

Second, the update will then grow the size of the row, cause massive numbers of page splits, fragment your table to hell and back and slow everything down.

Second, it's not twice as slow, it could be many times as slow as a simple insert, depending on the clustering key (if it's part of the unique key it's not as bad, if it's elsewhere it's worse as SQL will split the update into a delete and insert pair)

It's also more log impact, so larger transaction log, larger log backups.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

a648 Old Hand Points: 381 More actions · Answer 2

Thanks - some reasons there I had thought of, some I hadn't.

I'll do it the correct way like I was going to, but it didn't hurt to ask - who knows it might be useful for anyone else searching for the same kind of thing. I'm happy to ask dumb questions if the answers are educational 🙂

I had Googled beforehand and I found others elsewhere asking the same kind of thing. It just seems such a chore to be looking up and down, back and forth, mentally keeping your place in two long lists of items, rather than have each pairing neatly on one line. I'm not the only one who'd prefer an alternative syntax it seems.

Cheers

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I actually find it easier. All the column names on the line above where I'm typing the values or select (i put all on columns on one line, all values on the next, not one per line). I use management studio (or SQL prompt) to get the column list for the insert, then it's just a case of matching with either variable or column from select.

Would be far more typing, and more time-consuming, if I had to type each column name then each value. (I hate typing long updates)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

terrykzncs Ten Centuries Points: 1324 More actions · Answer 4

Just a quick comment.

Most of us still have SQL 2005 but will (hopefully soon) migrate to SQL 2008.

In SQL 2008 it will be alot easier to insert multiple rows as in the code below:

INSERT INTO MyTable (Column1, Column2)

VALUES ('First',1),

('Second',2),

('Third',3),

('Fourth',4),

('Fifth',5)

This is something to look forward to as it will make the code alot neater.:-)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

terrykzncs (4/20/2011)
Just a quick comment.
Most of us still have SQL 2005 but will (hopefully soon) migrate to SQL 2008.
In SQL 2008 it will be alot easier to insert multiple rows as in the code below:
INSERT INTO MyTable (Column1, Column2)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
This is something to look forward to as it will make the code alot neater.:-)

It's really not that much improvement over this:

INSERT INTO MyTable (Column1, Column2)

select 'First' ,1 union all

select 'Second',2 union all

select 'Third' ,3 union all

select 'Fourth',4 union all

select 'Fifth' ,5

Rich Mechaber SSChampion Points: 11130 More actions · Answer 6

To add to the points raised above, I would add that you would need to start an explicit transaction to wrap the INSERT and UPDATE. Otherwise, if the INSERT succeeds but the UPDATE fails (bad data in violation of a constraint, for example), you'd wind up with a mostly-empty set of rows inserted.

Why add the overhead?

Rich