April 5, 2022 at 1:47 pm
I have a small table ( < 1000 lines). I deleted the first entry ID 1 as it was invalid.
I know it is not necessary to Re-ID it, however I want the first entry to be ID 1 not ID 2. I tried the following script;
CREATE TABLE Codelines_backup AS
SELECT ID, Rail_Road, NCS_Codeline, NCS_Subdivision
FROM Codelines_List;
DELETE FROM Codelines;
DBCC CHECKIDENT ('Codelines', RESEED, 0);
INSERT INTO Codelines (Rail_Road, NCS_Codeline, NCS_Subdivision)
SELECT Rail_Road, NCS_Codeline, NCS_Subdivision
FROM Codelines_backup
ORDER BY ID ASC;
And I get an error on the very first SELECT statement.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
My code looks right to me.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 5, 2022 at 2:06 pm
SQL Server doesn't support the CREATE TABLE as SELECT syntax.
You either explicitly create and then insert, or use SELECT INTO -- e.g.,
SELECT ID, Rail_Road, NCS_Codeline, NCS_Subdivision
INTO Codelines_backup
FROM Codelines_List;
April 5, 2022 at 2:12 pm
So, change it to this?
SELECT *
INTO Codelines_Backup
FROM Codelines;
DELETE FROM Codelines;
DBCC CHECKIDENT ('Codelines', RESEED, 0)
GO
INSERT INTO Codelines (Rail_Road, NCS_Codeline, NCS_Subdivision)
SELECT Rail_Road, NCS_Codeline, NCS_Subdivision
FROM Codelines_backup
ORDER BY ID ASC;
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 5, 2022 at 2:13 pm
Your syntax is wrong for SQL Server. You can't use SELECT in a CREATE TABLE statement. I recommend you review the documentation from Microsoft on CREATE TABLE syntax (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15) and go from there.
EDIT - disregard this reply. I was too slow to reply and I see others provided better advice.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 5, 2022 at 2:42 pm
Thanks, that worked in the corrected script I posted.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 5, 2022 at 2:56 pm
I just wanted to add in that I am not a big fan of the "SELECT * INTO" approach to table creation. It recreates the table, but only the bare minimum level of the table - no indexes or keys.
That being said, you are using it to back up an existing table, so that is the one scenario I can think of where I may use that syntax. I am a much bigger fan of the "CREATE TABLE" syntax as then I can create a primary key on it and a clustered index and non clustered indexes (if required). That being said, you CAN create the keys and indexes after table creation, but I prefer to have my primary key in and clustered index in place prior to adding data to the table.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 5, 2022 at 2:58 pm
Thank you. Good suggestion.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 5, 2022 at 4:42 pm
Following Brian Gale's suggestion, to create a new table based on an existing table, you can use the scripting function.
Right click your original table -> Script table -> New query window.
Then modify the script to create a new table. Check any references in the script to the table name that need to be changed.
April 5, 2022 at 5:22 pm
I just wanted to add in that I am not a big fan of the "SELECT * INTO" approach to table creation. It recreates the table, but only the bare minimum level of the table - no indexes or keys.
That being said, you are using it to back up an existing table, so that is the one scenario I can think of where I may use that syntax. I am a much bigger fan of the "CREATE TABLE" syntax as then I can create a primary key on it and a clustered index and non clustered indexes (if required). That being said, you CAN create the keys and indexes after table creation, but I prefer to have my primary key in and clustered index in place prior to adding data to the table.
I prefer to have it both ways. That is, use SELECT ... INTO but also create the clus index (at least) before you load the table. Naturally you can create the non-clus indexes first too if you prefer. This approach is also much better as it reduces locks on system pages held by a loading SELECT ... INTO, which can cause blocking issues for other queries.
SELECT TOP (0) *
INTO dbo.Codelines_Backup
FROM dbo.Codelines
CREATE UNIQUE CLUSTERED INDEX ... ON dbo.Codelines_Backup ...
--CREATE UNIQUE NONCLUSTERED INDEX ...
INSERT INTO dbo.Codelines_Backup WITH (TABLOCK)
SELECT *
FROM dbo.Codelines
--CREATE UNIQUE NONCLUSTERED INDEX ...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 5, 2022 at 8:33 pm
Also worth mentioning is that you should (almost certainly) never use a DELETE on the whole table. TRUNCATE is much more efficient, and frees up the allocated resources + it resets the identity column.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply