通过

SQL Server Tips & Tricks: Create Bulk Queries 2

  • 项目

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.

Comments

  • Anonymous
    May 08, 2011
    ...but the perf on this is not equal with  bulk insert, you pay the cost of each insert.

  • Anonymous
    May 08, 2011
    In case the update query is based on another table directly accessible from your database, then why not simply use a statement like below: INSERT INTO [Table1] SELECT <values> FROM [Table 2] A WHERE [Table 1].Id = [Table 2].id  AND [Table 2].Attribute = ?

  • Anonymous
    May 08, 2011
    This script is not meant to be superior in perfrmance to bulk insert. This is meant to show how to build queries dynamically. I guess the title of article is rather misleading as it draws this comparison

  • Anonymous
    May 08, 2011
    Insert into <table1> select.... will work fine, but in some cases you need to include constants or data from additional tables. This is an alternative method that auto-generates the insert scripts for you, in a convnient way.