How To insert records in SqlExpress Database tables through sqlscript while installing setup?

Question

I'm creating a database and all of my tables using SQL Server Express through SQL scripts. Now, I want to insert some records in tables like States (all states names), city (all city names).

How do I make a SQL script for this? I want to add this code to my sqlscripts after creating the database. Alternatively, is there a way to restore database tables by sql scripts?

Answer 1

If you have a script that generates your database and all of the objects, you can just append to the end whatever DML you are looking to accomplish. For instance, if you generated a script for your entire database that contains all of the DDL, just open up that file and at the end of it put something like this:

insert into States
(
   Col1,
   Col2,
   Col3,
   -- etc...
   ColN
)
values
(
   "Col1Val",
   "Col2Val",
   "Col3Val",
   "ColNVal"
),
(
   "Col1Val2",
   "Col2Val2",
   "Col3Val2",
   "ColNVal2"
)

insert into City
(
   -- your column list
)
values
(
   -- your values
)

Likewise, you can just have a separate script file and use SQLCMD to run your DDL script and then have a separate script containing your INSERTs and run that script afterwards. Here is a link on how to do this with SQLCMD.