2

So i'm trying to copy all data of table CarOrders into a new table, NewCarOrders. But i only want to copy columns and dependencies and not data. I don't know if it truly matters, but i'm using SQL Server Management Studio.

Here are two things that i tried, but none worked.

SELECT *
INTO NewCarOrders
FROM CarOrders
WHERE 0 = 1

The issue with above is, it is copying all the columns but it is not copying the dependencies/relationships. I saw some posts on some of the forums regarding this, but the suggested solution was using SSMS Wizard. I want to do it using SQL (or T-SQL).

I'm new to SQL world, so i'm really sorry if it is a stupid or no brainer question. Let me know if i am missing some important information, i'll update it.

Billy
  • 183
  • 2
  • 14

2 Answers2

1

Try below query and check if this works for you

SELECT TOP 0 * 
INTO NewCarOrders
FROM CarOrders

This will create NewCarOrders table with same structure as CarOrders table and no rows in NewCarOrders.

SELECT * FROM NewCarOrders -- Returns zero rows

Note : This will not copy constraints , only structure is copied. For constraints do as below -

  1. In SSMS right click on the table CarOrders, Script Table As > Create To > New Query Window.
  2. Change the name in the generated script to NewCarOrders and execute.

Also change the constraints name in the generated script else it will throw error like There is already an object named 'xyz' in the database

Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
  • is this will copy all the dependencies/relationships also? – Abdul Rasheed Feb 19 '18 at 06:09
  • Hi Mudassir, so i tried that query but i'm getting error in syntax. INTO is red marked, and it says, "Incorrect syntax near "INTO", expecting '.', ID or QUOTED_ID." – Billy Feb 19 '18 at 06:11
  • 1
    OP already know how to copy schema, concern is about relation. – Sandip - Frontend Developer Feb 19 '18 at 06:13
  • check now ..... – Mudassir Hasan Feb 19 '18 at 06:13
  • Okay i did as you said, i replaced all "CarOrders" in script with "NewCarOrders", but i'm getting a lot of errors. "Msg 2714, Level 16, State 6, Line 11 There is already an object named 'NewCarOrders' in the database." – Billy Feb 19 '18 at 06:19
  • you have to do either one of them .. i think you have executed the `SELECT * INTO` query and then doing from SSMS. Drop the `NewCarOrders` table. then do the steps as listed in second solution i.e. from SSMS. – Mudassir Hasan Feb 19 '18 at 06:21
  • It worked :) Thanks a lot Mudassir. You were right, i did both of those things, hence the error. But i dropped the table, and replaced the name in the script, and it worked. Thank you. :) – Billy Feb 19 '18 at 06:29
  • Btw this is totally different question, so feel free to ask me to delete this. Whenever i create a table, and use SELECT statement once on it, and i try to drop it, i always get this error "Cannot drop the table 'dbo.NewCarOrders', because it does not exist or you do not have permission" I mean the table clearly exists, so what is up with permissions? – Billy Feb 19 '18 at 06:45
  • This answer also uses SSMS. There is a pure t-sql solution in the answers of the duplicate question. – Zohar Peled Feb 19 '18 at 06:47
  • @ZoharPeled Can you please give a link to that question? Apologies for duplicate question. Thanks :) – Billy Feb 19 '18 at 06:49
  • @Shrey You already have a link - on the top of your question with a yellow background. – Zohar Peled Feb 19 '18 at 06:51
-2

You can use the like command: 

CREATE TABLE  NewCarOrders LIKE CarOrders;
Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Divyam Vyas
  • 1
  • 1
  • 1
    No, that will generate a syntax error. – Zohar Peled Feb 19 '18 at 06:29
  • @JasonAller why bother editing an answer that is so clearly wrong, and on a duplicate question? – Zohar Peled Feb 19 '18 at 06:48
  • for points.. :D – xGeo Feb 19 '18 at 06:55
  • @GeomanYabes once you get to 2000 reputation, you get the [Edit Questions And Answers Privilege](https://stackoverflow.com/help/privileges/edit). Your edits are automatically approved but you no longer get the 2 reputation points for approved edits, so that can't be it. – Zohar Peled Feb 19 '18 at 07:01
  • @JasonAller without completely changing the answer, it can't be improved. Formatting is great but it can't replace the wrong content... – Zohar Peled Feb 19 '18 at 07:02
  • Could it have been an attempt at: https://stackoverflow.com/questions/10147565/create-table-like-a1-as-a2 – Jason Aller Feb 19 '18 at 07:03