Jeff Moden

Forum Replies Created

Viewing 15 posts - 32,611 through 32,625 (of 59,099 total)

RE: BULK INSERT permission

BW_Toro (9/8/2011)
I have an ETL stored procedure with a TRUNCATE TABLE statement and a BULK INSERT statement.
I use WITH EXECUTE AS 'dbo' in order to allow the TRUNCATE TABLE statement...
--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)

September 8, 2011 at 10:56 pm

#1378997
RE: Sequence creation

notoriousdba (9/8/2011)
Jeff Moden (9/8/2011)
Fine... I don't like your implication. 😉
Fair enough.:-P
Heh... you had me doubting myself 😉 Inference was the word I was looking for because it appeared that...
--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)

September 8, 2011 at 10:44 pm

#1378995
RE: Sequence creation

LutzM (9/8/2011)
Jeff Moden (9/8/2011)
...
Consider which letters may occur between ZA9999 and ZZZZZZ. You can spell some pretty offensive words with just the 5 letters. 😉
Now, I realize that...
--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)

September 8, 2011 at 12:20 pm

#1378836
RE: Sequence creation

LutzM (9/8/2011)
Jeff Moden (9/7/2011)
SQLServerMS (9/7/2011)
Hi,
I need to populate the values for a column in a sequential manner as below, these need to be auto generated:
i1st it starts with
000001
000002
......
999999
A00001
A00002
......
A99999
B00001
......
B99999
......
Z99999
ZA0001
......
ZA9999
......
ZZZZZZ
How can...
--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)

September 8, 2011 at 11:43 am

#1378809
RE: Sequence creation

notoriousdba (9/8/2011)
Jeff Moden (9/8/2011)
notoriousdba (9/8/2011)
References, or it didn't happen.
I AM the reference! 😉 Do it your way and embarass your company and possibly get sued for offending someone's sensibilities....
--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)

September 8, 2011 at 11:37 am

#1378804
RE: Convert interger to string

nellisjp (9/8/2011)
Keep in mind that the RAND() function is only pseudo-random. If you need truly random numbers, I'd go with another technique - perhaps utilizing GUIDs?
Edit: Jeff beat...
--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)

September 8, 2011 at 10:22 am

#1378726
RE: Sequence creation

notoriousdba (9/8/2011)
Jeff Moden (9/7/2011)
notoriousdba (9/7/2011)
I'd take Lowell's approach, though if the logic for converting the value in the identity column to the alpha-numeric value you want is non-trivial, you can...
--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)

September 8, 2011 at 10:15 am

#1378717
RE: Missing numbers in a series

wiltc-836148 (9/8/2011)
Jeff Moden (9/8/2011)
It seems to me, that the only thing I could take out is the line
GapEnd = hi.MyID - 1
which would have little effect...
--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)

September 8, 2011 at 10:12 am

#1378710
RE: Convert interger to string

Then there's a much better and faster way to do it. You need random numbers from 1000 to 99999999, right?
SELECT CAST(ABS(CHECKSUM(NEWID())) % 99998999 + 1000 AS VARCHAR(8))
The...
--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)

September 8, 2011 at 7:10 am

#1378511
RE: Missing numbers in a series

wiltc-836148 (9/8/2011)
Jeff,
Your gap detection code is very sweet...
However, I'm wondering what you think of the use of a tally table vs your gap detection if the requirement is to find...
--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)

September 8, 2011 at 6:45 am

#1378496
RE: Please help with Query

CELKO (9/5/2011)
tim.kay (9/2/2011)
Would you split a tire into a table for width, material and diameter into their own tables? Or would you use the ISO tire code such as...
--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)

September 7, 2011 at 7:05 pm

#1378327
RE: How can I sum the number of attributes from one table and export the count to a temp table as columns with 1 row per unique key?

@mark-3,
See the following article for how the method Cold Coffee used works.
http://www.sqlservercentral.com/articles/T-SQL/63681/
To see how to post data in the future to help us help you better, please see the first...
--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)

September 7, 2011 at 7:00 pm

#1378323
RE: How can I sum the number of attributes from one table and export the count to a temp table as columns with 1 row per unique key?

Well done, CC.
--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)

September 7, 2011 at 6:57 pm

#1378321
RE: Sequence creation

notoriousdba (9/7/2011)
I'd take Lowell's approach, though if the logic for converting the value in the identity column to the alpha-numeric value you want is non-trivial, you can easily abstract it...
--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)

September 7, 2011 at 6:46 pm

#1378319
RE: deletting the two adjacent same records.

I believe that I'd like to know how a claim was closed before it was opened. From the original data...
40389312 ,Closed ,1/4/2008 ,1
40389312 ,Open ,1/18/2008 ,1
40389312 ,Open ,10/21/2008 ,1...
--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)

September 7, 2011 at 6:42 pm

#1378317

Viewing 15 posts - 32,611 through 32,625 (of 59,099 total)