Jeff Moden

Forum Replies Created

Viewing 15 posts - 40,741 through 40,755 (of 59,098 total)

RE: Manipulating Decimal Format 0000000090.00000

Thanks, Arun... the only thing I haven't done is test it for performance... that's because I don't normally do such formatting in SQL Server. The only time I do...
--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)

December 10, 2009 at 7:03 am

#1090513
RE: File move

igloo21 (12/10/2009)
CirquedeSQLeil (12/9/2009)
Have you considered using a maintenance plan, or an SSIS package to do this?
Both seem like viable alternatives as well (in addition to Steve's suggestion).
Yes I have but...
--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)

December 10, 2009 at 7:01 am

#1090508
RE: Cannot bulk import CSV file with double quotes

Yep... you've got it. Select/Into FROM OpenRowSet also works.
You mentioned this was for big files. I'd first see if I could get the vendor to make the delimiters...
--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)

December 10, 2009 at 6:57 am

#1090502
RE: Regarding FindString()

I'm not sure what it is in SSIS but, in T-SQL, CHAR(9) is the tab character. In some places, it may be just \t or "\t".
--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)

December 10, 2009 at 12:52 am

#1090386
RE: Cannot bulk import CSV file with double quotes

This should do it... use the SELECT as if it were a table...
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
...
--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)

December 10, 2009 at 12:47 am

#1090385
RE: Manipulating Decimal Format 0000000090.00000

You can actually do it a tiny bit simpler...
SELECT REPLACE(STR(350,16,5),' ','0')
STR returns NVARCHAR values.
--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)

December 10, 2009 at 12:39 am

#1090384
RE: How to do a Grouped, Comma Separated List

FredS-1001785 (12/9/2009)
This it faster for the operator to scan with their eye for what they are looking for.
Ah... finally... someone with a practical reason. Thanks, Fred.
--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)

December 10, 2009 at 12:30 am

#1090379
RE: Urgent Help required

I do understand... that's why I you should post the data in the format requested... you'll get help a lot faster.
--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)

December 10, 2009 at 12:28 am

#1090377
RE: spliting the data using replace function

You bet, Anitha. Thank you for the feedback.
--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)

December 10, 2009 at 12:26 am

#1090376
RE: Regarding FindString()

Why don't you split it on the way in? I'm no SSIS wizard, but I believe they have a BULK task of some sort to load and split files...
--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)

December 10, 2009 at 12:26 am

#1090375
RE: Resulting message whit Bulk Insert

I'm pretty sure that it's not working correctly at all. The default delimiter for BULK INSERT is the tab character and you're using something else. You've also not...
--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)

December 10, 2009 at 12:21 am

#1090373
RE: Get Last Name, First name and Middle Name from Full Name

Divid'n'Conquer...
CREATE TABLE #names (full_name varchar(254))
INSERT INTO #names VALUES ('Simpson, Homer J')
INSERT INTO #names VALUES ('Simpson, Bartholomew JoJo')
INSERT INTO #names VALUES ('Simpson,Maggie')
INSERT INTO #names VALUES ('Bouvier Simpson, Margorie "Marge"')
INSERT INTO #names...
--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)

December 10, 2009 at 12:17 am

#1090372
RE: Substring column

SUBSTRING(COMMENTS, 1, LEN(COMMENTS)) will always return the full length of the field. Look at it... you need to do something else.
--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)

December 9, 2009 at 11:12 pm

#1090360
RE: Confusing set of strings!

Step 1... make sure the code works as is.
Step 2... do a simple replace of ' with '' (two single quotes)
Step 3... add a ' to the very begining and...
--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)

December 9, 2009 at 11:10 pm

#1090359
RE: counting rows in a table

Bru Medishetty (12/9/2009)
schauhan13 (12/9/2009)
Solution:-
SELECT COUNT(*) AS Expr1
FROM (SELECT partnbr
...
--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)

December 9, 2009 at 11:05 pm

#1090357

Viewing 15 posts - 40,741 through 40,755 (of 59,098 total)