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.