[dba-SQLServer] SELECT INTO question

Typically you'd use it by saying something like,

SELECT * INTO tblNewTableName From tblOldTableName where 1 = 0.  This copies
everything but the indexes....

This method works great for the lazy typist out there who don't like to
Write out the fields of their Temp Tables, as you know writing to a temp
table w/ the where clause instead of explcitly defining it first or the
method above, can cause a temporary lock while the table is created as it
waits for the data to be retreived....



On 2/27/06, David McAfee <dmcafee at pacbell.net> wrote:
>
> >From SQL Help
>
> INTO Clause
> Creates a new table and inserts the resulting rows from the query into it.
>
> The user executing a SELECT statement with the INTO clause must have
> CREATE
> TABLE permission in the destination database. SELECT...INTO cannot be used
> with the COMPUTE. For more information, see Transactions and Explicit
> Transactions.
>
> You can use SELECT...INTO to create an identical table definition
> (different
> table name) with no data by having a FALSE condition in the WHERE clause.
>
>
> But it seems to error each time for me, no matter how I use it.
>
> D
>
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Susan
> Harkins
> Sent: Monday, February 27, 2006 3:07 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] SELECT INTO question
>
>
> Not INSERT INTO, SELECT INTO...
>
> SELECT INTO creates a new table:
>
> SELECT * INTO newtable
> FROM sourcetable
>
> In Jet, if newtable exists, the statement writes over the existing
> instance
> of newtable. In SQL Server Express, the statement fails with an error that
> newtable already exists. I'm wondering which way SQL Server goes.
>
> Susan H.
>
> Sorry I read that wrong. In SQL 2000, if the correct data types are
> selected, it will simply insert the values as new records:
>
>
> INSERT INTO tblTest SELECT 'Test3'    (table tblTest only has one field, a
> char(10) )
>
>
> If the table does not exist, the table will be created (this is what I was
> referring to in my last post).
>
> If the table already exists but the number or columns are different or the
> types don't match, then the user will get an error.
>
> David
>
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of David
> McAfee
> Sent: Monday, February 27, 2006 2:51 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] SELECT INTO question
>
>
> SQL Server 2000 works like access. Some coworkers like using this method.
> I
> don't.
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Susan
> Harkins
> Sent: Monday, February 27, 2006 2:40 PM
> To: SQLList
> Subject: [dba-SQLServer] SELECT INTO question
>
>
> In Jet SQL, the SELECT INTO statement will overwrite an existing table if
> the target table already exists. I just checked SQL Server Express and it
> did NOT overwrite an existing table -- the statement failed with an error.
> I
> uninstalled SQL Server awhile back, so I can't check it -- can anybody
> tell
> me how SQL Server responds?
>
> Susan H.
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...