SQL Server Tips & Tricks: Create Bulk Queries 2

  • Article

In my previous post, I mentioned that sometimes you need to create dynamic queries for SQL servers and that one of the options is to write queries that dynamically creates the needed queries for you.

Although this might seem unclear when describing in words, an example could clarify this point more.

Example:

You need to create a set of insert statements that insert values in the “Navnodes” table. These statements should include an insert statement for each row in another table “Webs” if that row has a specific value in “SiteID” column.

The insert statements should look like this:

Insert into [navnodes] values (‘6DD2A42C-28D3-4026-801A-BF34B1102472’,’F34195CC-6DB1-40C1-B688-014BD519887B’,1025,0,0,0,1,’’,NULL, 'Quick Launch',’01-01-2011’,NULL,1,1,0) '

Insert into [navnodes] values (‘6DD2A42C-28D3-4026-801A-BF34B1102472’,’F34195CC-6DB1-40C1-B688-014BD519887C’,1025,0,0,0,1,’’,NULL, 'Quick Launch',’01-01-2011’,NULL,1,1,0) '

Insert into [navnodes] values (‘6DD2A42C-28D3-4026-801A-BF34B1102472’,’F34195CC-6DB1-40C1-B688-014BD519887D’,1025,0,0,0,1,’’,NULL, 'Quick Launch',’01-01-2011’,NULL,1,1,0) '

Insert into [navnodes] values (‘6DD2A42C-28D3-4026-801A-BF34B1102472’,’F34195CC-6DB1-40C1-B688-014BD519887E’,1025,0,0,0,1,’’,NULL, 'Quick Launch',’01-01-2011’,NULL,1,1,0) '

Insert into [navnodes] values (‘6DD2A42C-28D3-4026-801A-BF34B1102472’,’F34195CC-6DB1-40C1-B688-014BD519887F’,1025,0,0,0,1,’’,NULL, 'Quick Launch',’01-01-2011’,NULL,1,1,0) '

Insert into [navnodes] values (‘6DD2A42C-28D3-4026-801A-BF34B1102472’,’F34195CC-6DB1-40C1-B688-014BD519887G’,1025,0,0,0,1,’’,NULL, 'Quick Launch',’01-01-2011’,NULL,1,1,0) '

 

 

Note that the number of insert statements you want to create is dependent on the value of the SiteID column and could be in 100s or 1000s range; thus, it is not practical to create them manually.

 

A simple query that would return the list of the insert statements needed is the following:

select 'insert into [navnodes] values('‘<Site Collection ID>'', ''' + cast(id as varchar(70)) + ''',1025,0,0,0,1,'''',NULL,''Quick Launch'',getdate(),NULL,1,1,0) '

from  Webs where SiteId=‘<Site Collection ID>‘

Notice the following:

  1. We are using a select statement as if we are selecting rows from the Webs table where the siteId is the ID we want
  2. Rather than selecting values only, we have literals concatenated with the return values of the queries. The literals compose the insert statement we want to dynamically create.
  3.  We cast integer return to string as the output of our select query is a large string.

 

By running this select statement, the output will be rows of the format required and that contains the needed insert statements. Thus, we can redirect the output to a text file and use it as our sql script.

This method could be used to create any queries you want whether they are insert/update or delete with little effort and can scale to as many records you want.