Table created with “select into” sometimes does not create all the columns.

Question

Table created with “select into” sometimes does not create all the columns.

Paul.Dunne 98380

SSC Rookie

Points: 25
More actions
April 24, 2014 at 9:43 am

#286058

I have a stored procedure that contains the following syntax:
select rotable.ro, rotable.ro_name,
Br.in_error as in_error, Br.Revd as Revd,
BER3.in_error as in_error3, BER3.Revd as Revd3,
BER4.in_error as in_error4, BER4.Revd as Revd4,
BER5.in_error as in_error5, BER5.Revd as Revd5,
Ba.in_error as in_error1, Ba.Revd as Revd1
into [dbo].BenEntAcc
from rotable Left Outer Join BenEntAccR Br
ON rotable.ro = Br.ro Left Outer Join BER3
ON rotable.ro = BER3.ro Left Outer Join BER4
ON rotable.ro = BER4.ro Left Outer Join BER5
ON rotable.ro = BER5.ro Left Outer Join BenEntAccA Ba
ON rotable.ro = Ba.ro
Occasionally the table is created but is missing columns:
in_error3, Revd3,in_error4, Revd4,in_error5, Revd5.
The table has the columns before AND after the ones that are missing.
I cannot replicate this behavior on demand. Most times the table is created correctly.
I have not been able to find any reference to this type of behavior using “select into”.
SQL Server 2008 R2

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Paul.Dunne 98380 (4/24/2014)
I have a stored procedure that contains the following syntax:
select rotable.ro, rotable.ro_name,
Br.in_error as in_error, Br.Revd as Revd,
BER3.in_error as in_error3, BER3.Revd as Revd3,
BER4.in_error as in_error4, BER4.Revd as Revd4,
BER5.in_error as in_error5, BER5.Revd as Revd5,
Ba.in_error as in_error1, Ba.Revd as Revd1
into [dbo].BenEntAcc
from rotable Left Outer Join BenEntAccR Br
ON rotable.ro = Br.ro Left Outer Join BER3
ON rotable.ro = BER3.ro Left Outer Join BER4
ON rotable.ro = BER4.ro Left Outer Join BER5
ON rotable.ro = BER5.ro Left Outer Join BenEntAccA Ba
ON rotable.ro = Ba.ro
Occasionally the table is created but is missing columns:
in_error3, Revd3,in_error4, Revd4,in_error5, Revd5.
The table has the columns before AND after the ones that are missing.
I cannot replicate this behavior on demand. Most times the table is created correctly.
I have not been able to find any reference to this type of behavior using “select into”.
SQL Server 2008 R2

That doesn't make sense. I have never seen or heard of anything like that. Is your stored proc using dynamic sql to build up that statement? I am curious why you have a stored proc that is always creating a new persistent table. Why not just truncate the table instead of dropping it and recreating it all the time?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

David Burrows SSC Guru Points: 65138 More actions · Answer 2

Is there more than one procedure being used that creates the table?

Far away is close at hand in the images of elsewhere.
Anon.

Paul.Dunne 98380 SSC Rookie Points: 25 More actions · Answer 3

Thanks for the reply. You are correct it does not make sense. That is why I was looking for input.

As to why it is built this way, it is the old story of inheriting a process that is old and the author is long gone.

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

I'd start by looking through other procedures to see if there's any which drop and recreate that table. Sounds like there might be another procedure which does a DROP TABLE... SELECT .... INTO.

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

David Burrows SSC Guru Points: 65138 More actions · Answer 5

GilaMonster (4/24/2014)
I'd start by looking through other procedures to see if there's any which drop and recreate that table. Sounds like there might be another procedure which does a DROP TABLE... SELECT .... INTO.

More eloquently put than me 😀

Far away is close at hand in the images of elsewhere.
Anon.

Paul.Dunne 98380 SSC Rookie Points: 25 More actions · Answer 6

Thanks for all the input. The mystery has been solved.

The culprit was another stored procedure that dropped and created the shortened version of the table.

GilaMonster nailed it.