June 2, 2018 at 10:02 pm
What's the correct syntax?CREATE TABLE Title2
AS SELECT * FROM Title
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.CREATE TABLE Title2
AS (SELECT * FROM Title)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
June 2, 2018 at 10:26 pm
Try:
SELECT * INTO Title2 FROM
(SELECT * FROM dbo.Title) T
...
June 2, 2018 at 10:31 pm
MinhL7 - Saturday, June 2, 2018 10:02 PMWhat's the correct syntax?CREATE TABLE Title2
AS SELECT * FROM Title
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.CREATE TABLE Title2
AS (SELECT * FROM Title)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
or simply try
SELECT * INTO Title2 FROM Title;
Saravanan
June 4, 2018 at 10:20 am
saravanatn - Saturday, June 2, 2018 10:31 PMor simply try
SELECT * INTO Title2 FROM Title;
This one
June 4, 2018 at 11:24 am
Pl
Steve Jones - SSC Editor - Monday, June 4, 2018 10:20 AMsaravanatn - Saturday, June 2, 2018 10:31 PMor simply try
SELECT * INTO Title2 FROM Title;This one
Plus if you just wanted to create a quick replica of the existing table without the data,
SELECT * INTO Title2
FROM Title
Where 1=0 /* will always be false */
This will create the table with zero rows.
----------------------------------------------------
June 4, 2018 at 12:26 pm
SELECT * INTO Title2 FROM Title;
Not a good idea. A direct SELECT ... INTO new_table can cause system table locking and can cause concurrence issues. You should do this instead:
SELECT TOP (0) *
INTO dbo.Title2
FROM dbo.Title
WHERE 1 = 0
INSERT INTO dbo.Title2 WITH (TABLOCK) /*The TABLOCK hint is critical to getting minimal logging if possible.*/
SELECT *
FROM dbo.Title
/* Btw, add this if you want to remove the identity property from a column
---(i.e., you want the identity column to be a regular int column instead).
---The UNION [ALL] will "cancel" the identity property.
UNION ALL
SELECT TOP 0 (*)
FROM dbo.Title
*/
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 4, 2018 at 1:01 pm
ScottPletcher - Monday, June 4, 2018 12:26 PMSELECT * INTO Title2 FROM Title;
Not a good idea. A direct SELECT ... INTO new_table can cause system table locking and can cause concurrence issues.
I had the impression that this issue was corrected for many versions now and has the advantage of having a more straightforward minimally logged operation.
June 4, 2018 at 1:41 pm
Luis Cazares - Monday, June 4, 2018 1:01 PMScottPletcher - Monday, June 4, 2018 12:26 PMSELECT * INTO Title2 FROM Title;
Not a good idea. A direct SELECT ... INTO new_table can cause system table locking and can cause concurrence issues.
I had the impression that this issue was corrected for many versions now and has the advantage of having a more straightforward minimally logged operation.
I've still seen it, and we're on 2016 Enterprise (we have one remaining SQL 2008 and SQL 2012 each). Even in tempdb, it can obstruct things like meta-data queries on sys.objects.
I get what you're saying, and MS may claim it's resolved, but a quick Google search shows I'm not the only one still seeing this effect: https://www.sqlshack.com/sql-server-lock-issues-when-using-a-ddl-including-select-into-clause-in-long-running-transactions/.
So, as for me, I'll still avoid the direct SELECT INTO if possible, especially for large amounts of data.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply