insert 100 rows without using loop or union etc.,

Question

insert 100 rows without using loop or union etc.,

Viewing 10 posts - 31 through 39 (of 39 total)

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

Jeff Moden (5/4/2012)
michael vessey (5/4/2012)
capn.hector (5/4/2012)
Eugene Elutin (5/4/2012)
Jeff Moden (5/3/2012)
michael vessey (5/2/2012)
does the following count as a loop?
INSERT INTO dbo.yourTable default values
go 100
??:-P??
BWAAAA-HAAAA!!!!! Ask that question on the receiving end of an interview and see what happens. 😉
Agree, it could be very tricky one to answer.
I would probably go with:
"Strictly speaking, it's not a loop. It's a single statement sql batch executed 100 times."
I'll ask this one and will see what kind of responses I get...
i would actually argue that it is a loop. except the loop is on the client side not the server side. just like a client program looping through an array and then handing SQL Server single inserts for each item in the array. its a loop but the server never see's the loop
it's not a loop - it's an iterative process 😀
Actually, it doesn't matter if it's a loop or not. If you submit it as the only answer to the question during an interview with me, you will be promptly escorted off the premises. 😉

i see my humour is lost on you jeff

MVDBA

Sony Francis @EY SSCommitted Points: 1539 More actions · Answer 2

Pls try this

With SLNo

AS

(

SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY object_id ) Id from sys.objects )

Select ID from SLNo

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

michael vessey (5/8/2012)
i see my humour is lost on you jeff

Nah... I had a smiley face there. 🙂 I just didn't want a newbie reading that to miss the humor and to ever think it was a good idea.

--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)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Jeff Moden (5/8/2012)
michael vessey (5/8/2012)
i see my humour is lost on you jeff
Nah... I had a smiley face there. 🙂 I just didn't want a newbie reading that to miss the humor and to ever think it was a good idea.

I'm not sure if reading good suggestions from this forum would prevent many of newbies from being "promptly escorted off the premises" after interview with some of us :w00t:.

But, at least, it could provide some entertainment :hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

I'm not sure if reading good suggestions from this forum would prevent many of newbies from being "promptly escorted off the premises" after interview with some of us :w00t:.
But, at least, it could provide some entertainment :hehe:

you should see some of the people I have interviewed in the past - if i could have done a simpsons "mr burns" and shouted "release the hounds" i would have !!!

I asked one candidate "what do you think are the 5 most important things to remember when working with SQL server?"

his answers -

1) lots of comments in code

2) use Pascal Case when naming objects

3) always check in your code to source control

4).. can't think of any more

while they are reasonable points i don't think they are the most important - really i was looking for

indexes, query plans, secure the sa account, tables have primark keys, choose the right data type etc... you know the simple things

MVDBA

Gazareth One Orange Chip Points: 27737 More actions · Answer 6

michael vessey (5/8/2012)
I'm not sure if reading good suggestions from this forum would prevent many of newbies from being "promptly escorted off the premises" after interview with some of us :w00t:.
But, at least, it could provide some entertainment :hehe:
you should see some of the people I have interviewed in the past - if i could have done a simpsons "mr burns" and shouted "release the hounds" i would have !!!
I asked one candidate "what do you think are the 5 most important things to remember when working with SQL server?"
his answers -
1) lots of comments in code
2) use Pascal Case when naming objects
3) always check in your code to source control
4).. can't think of any more
while they are reasonable points i don't think they are the most important - really i was looking for
indexes, query plans, secure the sa account, tables have primark keys, choose the right data type etc... you know the simple things

1. Backups

2. Verify Backups

3. Restore Backups

4. See above

5. See above

😀

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

I asked one candidate "what do you think are the 5 most important things to remember when working with SQL server?"

Sorry, I hate such questions. They are generic and very personal!

Someone need to remember to make backups, another one should remember to hit save before closing script file and the third one may need to remember to stand up when manager is entering the room.

My 5 most important things to remember when working with SQL Server are:

1. Name of my favoured kind of beer

2. Senior manager name (so, I can invite him to the pub)

3. How to get back to home

4. Time for the last train home

5. House number I do live in.

My "5 most important things" are universal and can be applied with working on any SQL Server version.

The rest is of much less importance...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 8

Sorry, I hate such questions. They are generic and very personal!
Someone need to remember to make backups, another one should remember to hit save before closing script file and the third one may need to remember to stand up when manager is entering the room.

that's why they make good interview questions - they are open questions and get a candidate to think - there are no 5 absolute right answers - just a nice starting point for a good conversation about SQL server

but if someone says to me that comments are important but forgets backups, indexes and primary keys then i'm starting to worry he's not up to the job

MVDBA

Cadavre SSC-Forever Points: 41690 More actions · Answer 9

michael vessey (5/8/2012)
I'm not sure if reading good suggestions from this forum would prevent many of newbies from being "promptly escorted off the premises" after interview with some of us :w00t:.
But, at least, it could provide some entertainment :hehe:
you should see some of the people I have interviewed in the past - if i could have done a simpsons "mr burns" and shouted "release the hounds" i would have !!!
I asked one candidate "what do you think are the 5 most important things to remember when working with SQL server?"
his answers -
1) lots of comments in code
2) use Pascal Case when naming objects
3) always check in your code to source control
4).. can't think of any more

To be fair, I have no idea how I'd have answered that sort of question. It's too subjective, so any answer I give it likely to be incorrect from someone else's point of view.

I'd have probably said: -

1) Sketch out tasks as at least 3 different algorithms

2) Test queries against small, medium and large data-sets before testing against the real data

3) Always include "BEGIN TRAN" & "ROLLBACK" at the beginning and end of every SQL query window. They can be removed once I'm sure that the query is correct.

4) Follow coding conventions set out by the company

5) Follow correct procedure for getting code onto "live" systems

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

GSquared SSC Guru Points: 260824 More actions · Answer 10

michael vessey (5/8/2012)
I'm not sure if reading good suggestions from this forum would prevent many of newbies from being "promptly escorted off the premises" after interview with some of us :w00t:.
But, at least, it could provide some entertainment :hehe:
you should see some of the people I have interviewed in the past - if i could have done a simpsons "mr burns" and shouted "release the hounds" i would have !!!
I asked one candidate "what do you think are the 5 most important things to remember when working with SQL server?"
his answers -
1) lots of comments in code
2) use Pascal Case when naming objects
3) always check in your code to source control
4).. can't think of any more
while they are reasonable points i don't think they are the most important - really i was looking for
indexes, query plans, secure the sa account, tables have primark keys, choose the right data type etc... you know the simple things

Five most important?

Systems support businesses, not the other way around

For-profit businesses exist to make money by delivering products and services to customers for a reasonable exchange of value

Communication can solve just about any conflict

Disaster recovery is inferior to disaster prevention

A backup plan is inferior to a recovery plan

Those come to mind readily.

Could just as easily be:

Have a purpose - happiness can easily be defined as the overcoming of not-unknowable barriers towards a desirable purpose

Life is about living and there is no substitute for an all-out, over-the-ramparts charge against life

We're all human beings and all in this together, whether we know it or not

Never regret yesterday - life is in you today and you make your own tomorrow

Your integrity to yourself is more important than little things like living or dying

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon