using select into and controlling varchar sizes

Question

using select into and controlling varchar sizes

Viewing 8 posts - 16 through 22 (of 22 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

:w00t::hehe::w00t:

tfifield (11/29/2011)
I find SELECT INTO as a very handy dandy tool to create a table quickly. If I'm a bit queasy about letting a big DELETE or UPDATE query loose on a table I'll do a quick 'SELECT * INTO Table_Safe FROM Table' as a quick and dirty backup of the table before letting the query rip at the real table.
I also use it extensively for temp tables. That way I don't have to know which columns are DEC(15, 2) and which are DEC(15, 4) and so forth.
If there can be any sort of contention, however, it's not a good practice.
Todd Fifield

What? Your kidding?:w00t:

Please tell me you are joking.:crazy:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Welsh Corgi (11/29/2011)
:w00t::hehe::w00t:
tfifield (11/29/2011)
I find SELECT INTO as a very handy dandy tool to create a table quickly. If I'm a bit queasy about letting a big DELETE or UPDATE query loose on a table I'll do a quick 'SELECT * INTO Table_Safe FROM Table' as a quick and dirty backup of the table before letting the query rip at the real table.
I also use it extensively for temp tables. That way I don't have to know which columns are DEC(15, 2) and which are DEC(15, 4) and so forth.
If there can be any sort of contention, however, it's not a good practice.
Todd Fifield
What? Your kidding?:w00t:
Please tell me you are joking.:crazy:

Heh, I do pretty much the same thing. It's not that uncommon. But you don't do it for active data outside of #tmps.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

:laugh:I just tried to do a SELECT INTO and for the first time ever I got an error.

That's what I get for suggesting that it is better to do an INSERT INTO.

I just wanted a backup of the table.

Has anyone ever seen this error? Any ideas?

I posted the question to this URL:

http://www.sqlservercentral.com/Forums/Topic1213920-338-1.aspx

This is the error:

Msg 1934, Level 16, State 1, Procedure Backup_Tables, Line 11

INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

The problem was caused by a Database Trigger that I created.:sick:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 5

cafescott (11/29/2011)
Welsh Corgi, thanks for the suggestion. I am reluctant to change to the insert-into method because I think it will take longer overall.

Ironically, you've shot yourself in the foot a bit by using STR(). Please see the following article for why. The part about performance appears in the 2nd half of the article in a section titled "STR() is SLOW!!!"

http://www.sqlservercentral.com/articles/T-SQL/71565/

~~I believe I've found a way around your problem but have some more testing to do on it before I post it as a solution.~~

{EDIT} Gah! So much for that idea. Still working on it though.

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Jeff Moden (11/30/2011)
cafescott (11/29/2011)
Welsh Corgi, thanks for the suggestion. I am reluctant to change to the insert-into method because I think it will take longer overall.
Ironically, you've shot yourself in the foot a bit by using STR(). Please see the following article for why. The part about performance appears in the 2nd half of the article in a section titled "STR() is SLOW!!!"
http://www.sqlservercentral.com/articles/T-SQL/71565/

Excellent article Jeff.:cool:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 7

Thanks for the feedback, Welsh Corgi. 🙂

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

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

It turns out that anytime you use a variable the control the length of a RIGHT or other substring function, SQL Server is going to do pretty much as it wants for the final length of the VARCHAR definition during the SELECT INTO.

About all I can do for you short of the Dynamic SQL others were talking about, is to improve your function for performance and scalability a bit and tell you there's absolutely no need for the STR() function in your code.

CREATE FUNCTION dbo.fLPAD

(

@vPadChar CHAR(1),

@vStr VARCHAR(8000),

@vLen INT

)

RETURNS VARCHAR(8000) WITH SCHEMABINDING

AS

BEGIN

RETURN (SELECT RIGHT(REPLICATE(@vPadChar, @vLen) + LTRIM(RTRIM(@vStr)), @vLen))

END

;

select

dbo.fLeft_Pad('0', Str(123456789),9) as [Zip Code1],

Left(dbo.fLeft_Pad('0', Str(123456789),9),9) as [Zip Code2],

cast(dbo.fLeft_Pad('0', Str(123456789),9) as varchar(9)) as [Zip Code3],

'123456789' as [Zip Code4],

[Zip Code5] = CAST(dbo.fLPAD('0',123456789,9) AS VARCHAR(9)) --Uses new function

into dbo.tblTest

EXEC sp_Help 'dbo.tblTest'

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