Syntax for creating a table on the fly

Question

Post reply

Syntax for creating a table on the fly

Joe Contreras-290946

SSCommitted

Points: 1903
More actions
September 16, 2010 at 7:12 am

#226366

Hi,
I can't remember the exact syntax on creating a table that doesn't exists using SQL. Can someone help out.
SELECT firstname,lastname [NEWTABLENAME] FROM Employees
Not sure if that's the right syntax.
Thanks,

Viewing 12 posts - 1 through 11 (of 11 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

SELECT firstname,lastname into [NEWTABLENAME] FROM Employees

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Joe Contreras-290946 (9/16/2010)
Hi,
I can't remember the exact syntax on creating a table that doesn't exists using SQL. Can someone help out.
SELECT firstname,lastname [NEWTABLENAME] FROM Employees
Not sure if that's the right syntax.
Thanks,

BOL, the on-line manual installed with SQL Server, has some notes.

SELECT Examples (Transact-SQL)

D. Creating tables with SELECT INTO

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Joe Contreras-290946 SSCommitted Points: 1903 More actions · Answer 3

Joe Contreras-290946

SSCommitted

Points: 1903

September 16, 2010 at 7:58 am

#1222205

Thank You All

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 4

Hi

Joe

If U find the things in Ur Pocket, why ask to another? First check Ur pocket (SQL BOL) please, the forum's expert not free for answer a silly question.

Ali
MCTS SQL Server2k8

Joe Contreras-290946 SSCommitted Points: 1903 More actions · Answer 5

Duly noted. I did my homework before posting and couldn't find it. There's alway one in the crowd.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

I have to admit that unless you know what you're looking for, this one is hard to find in BOL. GOOGLE? That might be a different story...

http://www.google.com/search?btnG=1&pws=0&q=how+to+create+a+table+using+select+%2B%22SQL+SERVER%22

--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)
Intro to Tally Tables and Functions

Joe Contreras-290946 SSCommitted Points: 1903 More actions · Answer 7

I Google first looking for

dynamically building tables in SQL

Create table based on SQL

SELECT Into

I couldn't remember the exact syntax and it was driving me nuts. It was one of those where did I leave my car keys situation.

I must admit it was a total brain fart.

Again Thank You all for helping an old man out.

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

Joe Contreras-290946 (9/18/2010)
I Google first looking for
dynamically building tables in SQL
Create table based on SQL
SELECT Into
I couldn't remember the exact syntax and it was driving me nuts. It was one of those where did I leave my car keys situation.
I must admit it was a total brain fart.
Again Thank You all for helping an old man out.

Heh... absolutely understood. Sometimes ya just don't know enough about what you're looking for to even Google it. Us old dude's have gotta stick together on stuff like this. The term "It Depends" has a whole lot different meaning for us. 😀

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Joe,

Here's a bit more information...

First, open up BOL in the INDEX mode and do a search for "SELECT clause" (without the quotes on any of these lookups), and then make sure you select "SELECT Clause (Transact-SQL)" from the index results tab at the bottom of the screen, you'll find a lot more about SELECT INTO. One of the more important aspects is the following right straight out of that entry...

[font="Arial Black"]Selecting Identity Columns[/font]

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

The SELECT statement contains a join, GROUP BY clause, or aggregate function.

Multiple SELECT statements are joined by using UNION.

The identity column is listed more than one time in the select list.

The identity column is part of an expression.

If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. All rules and restrictions for the identity columns apply to the new table.

--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)
Intro to Tally Tables and Functions

Kingston Dhasian SSCoach Points: 19795 More actions · Answer 10

Ali Tailor (9/17/2010)
Hi
Joe
If U find the things in Ur Pocket, why ask to another? First check Ur pocket (SQL BOL) please, the forum's expert not free for answer a silly question.

I don't think it was such a silly question after all. I was asked the same question in an interview and I replied that I don't know the answer. Then the interviewer asked if i knew about SELECT * INTO Clause. I said YES. I do. But won't that copy the data as well. He asked Can't you write a WHERE Clause to filter the data?. I thought for a few seconds and again said YES. We can use WHERE 1 = 2 condition. The interviewer started smiling and he asked me not to get nervous.

If you want to see a silly question, have a look at the link below where the OP asked about the BIT datatype and see how the experts patiently answered the OP.

http://qa.sqlservercentral.com/Forums/Topic984249-9-1.aspx

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 11

Kingston Dhasian (9/19/2010)
Ali Tailor (9/17/2010)
Hi
Joe
If U find the things in Ur Pocket, why ask to another? First check Ur pocket (SQL BOL) please, the forum's expert not free for answer a silly question.
I don't think it was such a silly question after all. I was asked the same question in an interview and I replied that I don't know the answer. Then the interviewer asked if i knew about SELECT * INTO Clause. I said YES. I do. But won't that copy the data as well. He asked Can't you write a WHERE Clause to filter the data?. I thought for a few seconds and again said YES. We can use WHERE 1 = 2 condition. The interviewer started smiling and he asked me not to get nervous.
If you want to see a silly question, have a look at the link below where the OP asked about the BIT datatype and see how the experts patiently answered the OP.
http://qa.sqlservercentral.com/Forums/Topic984249-9-1.aspx

Hi

Kingston Dhasian

It's was a try to just say , ....

Ali
MCTS SQL Server2k8