Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do I add columns into a Select Into table ?

Posted on 2008-10-16
14
Medium Priority
?
202 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
  • 9
  • 5
14 Comments
 
LVL 18

Accepted Solution

by:
jmoss111 earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

581 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