locked
CREATE TABLE and INSERT in One Script Will Not Build RRS feed

  • Question

  • I use SSDT for Visual Studio 2012. I have a create table script

    CREATE TABLE [dbo].[Foo] (
    Col1 INT DEFAULT ((1)),
    Col2 INT DEFAULT ((1))
    );
    GO
    INSERT INTO Foo DEFAULT VALUES
    GO

    This works fine in SSMS. In VS2012 it throws a build error on the INSERT: SQL70001: This statement is not recognized in this context.

    I know I can prevent this error by changing the Build Action to None. However I use VS Schema Compare to push the scripts to SQL Server and SQL Azure. With Build Action = None the table is not included in the upgrade script.

    How can I write a CREATE TABLE that also inserts default values and have that table picked up in VS Schema Compare?


    • Edited by FSL AU Friday, October 3, 2014 6:24 PM typo
    Friday, October 3, 2014 9:53 AM

Answers

  • Jamie is correct in suggesting you put this script into a post deployment script. Here's the context:

    - SSMS is all about immediate operations against a database. Hence you can use Create, Alter, Insert into etc. all in one script to make changes to a database

    - SQL Server database projects are all about offline development. You define how your database should look (e.g. Create table, view, procedure), not what changes to do. Then when you build the project and deploy the Dacpac created as a build output, the tooling intelligently figures out how to convert the target database to make it look the way you defined it in your project. It does all of the Alter syntax for you.

    - One necessary side effect of this is that you need to put data manipulation statements such as Insert INTO in separate script files that are marked as either Pre or Post deployment scripts. These run before or after all the schema change operations have been completed. Hence you need to put the Create script in one (build) file, and the Insert statement in another (post deployment) file. Remember this could run against any version of the database (e.g. it would run during incremental updates if you change part of the schema), so the recommendation is to make your scripts idempotent so that they can be run multiple times without causing issues with your database.

    I hope this helps clarify things for you. Regards,

    Kevin

    • Marked as answer by FSL AU Friday, October 3, 2014 10:10 PM
    Friday, October 3, 2014 10:00 PM

All replies

  • You're using different table in the INSERT. Is that intentional? Is scenario like creating a table + insert into an existing table?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, October 3, 2014 10:02 AM
  • How can I write a CREATE TABLE that also inserts default values and have that table picked up in VS Schema Compare?

    You can't. Its called Schema Compare for a reason - it only compares schema, not data. I suggest putting your data population statements into a dedicated post-deployment script (that's standard SSDT functionality), this will ensure that those scripts get run when you deploy your project.
    • Proposed as answer by Visakh16MVP Friday, October 3, 2014 1:09 PM
    • Unproposed as answer by FSL AU Friday, October 3, 2014 6:31 PM
    Friday, October 3, 2014 12:37 PM
  • The INSERT was a typo. I've corrected it to insert into the same table. I don't want Schema compare to compare data; I use other tools for that. The table creation script includes an INSERT. The script works in SSMS but VS throws a build error. As this is a valid script, how do I get VS Schema Compare to push it to SQL Azure?

    • Edited by FSL AU Friday, October 3, 2014 6:38 PM
    Friday, October 3, 2014 6:26 PM
  • Jamie is correct in suggesting you put this script into a post deployment script. Here's the context:

    - SSMS is all about immediate operations against a database. Hence you can use Create, Alter, Insert into etc. all in one script to make changes to a database

    - SQL Server database projects are all about offline development. You define how your database should look (e.g. Create table, view, procedure), not what changes to do. Then when you build the project and deploy the Dacpac created as a build output, the tooling intelligently figures out how to convert the target database to make it look the way you defined it in your project. It does all of the Alter syntax for you.

    - One necessary side effect of this is that you need to put data manipulation statements such as Insert INTO in separate script files that are marked as either Pre or Post deployment scripts. These run before or after all the schema change operations have been completed. Hence you need to put the Create script in one (build) file, and the Insert statement in another (post deployment) file. Remember this could run against any version of the database (e.g. it would run during incremental updates if you change part of the schema), so the recommendation is to make your scripts idempotent so that they can be run multiple times without causing issues with your database.

    I hope this helps clarify things for you. Regards,

    Kevin

    • Marked as answer by FSL AU Friday, October 3, 2014 10:10 PM
    Friday, October 3, 2014 10:00 PM
  • Thank you for the explanation Kevin. The real table contains a single row of settings for the application. I was doing it this way because there is an INSTEAD OF INSERT, DELETE trigger that protects that single row of data. The single row of data of course had to be inserted before the trigger was created.

    I guess I'll have to put the trigger in the post deployment script also.

    Thank you Kevin and Jamie for your assistance.


    • Edited by FSL AU Friday, October 3, 2014 10:17 PM typo
    Friday, October 3, 2014 10:16 PM