Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 23, 2004

MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data - Page 6

By William Pearson



Populate the Table with Query Analyzer and Create a Select Query



Let's populate the new SubscriberDetails table by taking the next steps.



1.  From Enterprise Manager, with the new table highlighted / selected, select Tools -> Query Analyzer from the main menu, as depicted in Illustration 18.



Click for larger image

Illustration 18: Select Query Analyzer ...



Query Analyzer opens, selected to the new RS_Subscription database, as shown in Illustration 19.




Illustration 19: Query Analyzer Appears, with the RS_Subscription Database Selected

NOTE: If necessary, select the RS_Subscription database in the selector, as shown circled in Illustration 19 above.

2.  Type (or cut and paste) the following script into the Query pane:

-- RS006:  INSERT Query to Populate SubscriberDetails Table

INSERT INTO 
   dbo.SubscriberDetails (Participant, File_Share, Identification_No, Media_Format,       
      Parameter)
VALUES 
   ('Damascus, Billy','\\MOTHER1\D$\RS_SubscriberShares\DamascusB','10059','EXCEL', 
      'Non-Consumable')
GO

INSERT INTO 
dbo.SubscriberDetails (Participant, File_Share, Identification_No, Media_Format, 
   Parameter)
VALUES 
   ('Adams, Victoria','\\MOTHER1\D$\RS_SubscriberShares\AdamsV','32981','PDF', 'Food')
GO

INSERT INTO 
   dbo.SubscriberDetails(Participant, File_Share, Identification_No, Media_Format, 
      Parameter)
VALUES 
   ('Pearson, William E. III','\\MOTHER1\D$\RS_SubscriberShares\PearsonW3','04089','HTML4.0', 
      'Non-Consumable')
GO

INSERT INTO 
   dbo.SubscriberDetails(Participant, File_Share, Identification_No, Media_Format, 
      Parameter)
VALUES 
   ('Dhue, Laurie','\\MOTHER1\D$\RS_SubscriberShares\DhueL','14761','IMAGE', 
      'Non-Consumable')
GO

Our load script appears in the Query pane as depicted in Illustration 20.


Illustration 20: Our Script in Query Analyzer

3.  Click the Execute Query button, shown in Illustration 21.


Illustration 21: Click the Execute Button ...

The script executes, and we receive a "(1 row(s) affected)" message for each of the four lines of the script in the Results pane.

4.  Save the script, if desired, by clicking the Query pane (versus the Results pane) and selecting File --> Save As from the main menu, and selecting an appropriate name for the .sql file, along with a convenient location.

We can easily verify the fact that the table is populated by going to Enterprise Manager, but since we will need to create a query to supply necessary data to the Data-Driven Subscription we will define later, let's use this query to ascertain the fact that all data is in place in the SubscriberDetails table.

5.  Click the New Query button (see Illustration 22) in the main toolbar to open a new Query pane.


Illustration 22: Click the New Query Button ...

6.  Type the following simple query into the Query pane:

SELECT * 
FROM
dbo.SubscriberDetails

Our query appears in the Query pane as depicted in Illustration 23.


Illustration 23: Our Query in Query Analyzer (Compressed View)

7.  Click the Execute Query button, once again.

The script executes, and the Results pane is populated (Grid View), as shown in Illustration 24.


Illustration 24: Query Results in Grid View (Compressed View)

We thus obtain confirmation from the results of the SELECT statement that our table has been populated as we expected. We will save the query, so as to be able to use it in our Data-Driven Subscription setup later.

8.  Save the SELECT statement by clicking the Query pane (once again, versus the Results pane) selecting File --> Save As from the main menu, and selecting an appropriate name for the .sql file, along with a convenient location.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM