Solved

How do I add columns into a Select Into table ?

Posted on 2008-10-16
14
197 Views
Last Modified: 2010-03-20
1.  In my query I Select Into a table that doesn't exist in my DB.  Will this table get created automatically?
2.  After the Select statement, how can I add 4 additional columns to it (code exmaple please).  
3.  Also, At some point in my query I also need to delete 2 columns in this table that was part of the Select Into statement's result.

thank you.
0
Comment
Question by:lapucca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
14 Comments
 
LVL 18

Accepted Solution

by:
jmoss111 earned 500 total points
ID: 22736935
Hello lapucca,

The SELECT INTO creates the table if the table doesn't exist.
Do ALTER TABLE MyTable ADD MyCol VARCHAR(10)
Do ALTER TABLE MyTable DROP COLUMN MyCol


Regards,

Jim
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22736940
If the table exists, you'll get an error. If this process is recurring do DROP TABLE MyTable before SELECT INTO



Jim
0
 

Author Comment

by:lapucca
ID: 22736966
This sounds stupid, but is Do part of the code?
also, how do I check if the table exist then drop it at the begining of my query?  Thank you.
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

Author Comment

by:lapucca
ID: 22736973
Also, after adding or deleting columns, do I need some kind of commit statement to save the changes to the table?  Thanks.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22736980
Nope nowherer I used it; no question is stupid except the unasked ones.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22736982
As soon as the SELECT INTO executes you're there
0
 

Author Comment

by:lapucca
ID: 22736998
So no commit statement if needed.
Thank you.
0
 

Author Comment

by:lapucca
ID: 22737004
Still I'm not sure here.  No commit statemetn is need for
ALTER TABLE ???
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22737025
Nope. If that was part of a TRANSACTION then it would require a BEGIN and COMMIT.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22737031
Missed one:

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'MyTable')
   DROP TABLE MyTable
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22737035
Is this exercise inside a TRANSACTION?
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22737045
I'll be away for about 15 minutes. Don't think I've left this thread...

Jim
0
 

Author Comment

by:lapucca
ID: 22737055
No, it's not a transaction.  It's either a query or a stored procedure.  Thank you very much for your thourough help.  Really appreciate it.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22737123
I'm glad that I have been able to help out a little.

Jim
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Recursion 6 43
Get data from two MySQL tables 6 59
SQL Server how to create a DYNAMIC TABLE? 11 44
MSSQL Query for Selecting the SUM of a Specific Group 2 33
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question