Creating a summary table in SQL Server

Tags:
Dynamic SQL
SQL Server tables
I need to create a summary table for the combination of some fields in a SQL Server table. I have the combination field value and the source table name in one SQL table as a row value. I need to pick this value and then fire a query on a source table with extracted values from the SQL table. How can I do this with out using a cursor or Dynamic SQL?

Answer Wiki

Thanks. We'll let you know when a new response is added.

The only way to turn row output into SQL code would be some form of dynamic SQL. Why the prohibition against it?

Doing this will require both a cursor and dynamic SQL.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Sivu
    B'cause Dynamic SQL kills the performance for large data.
    105 pointsBadges:
    report
  • Denny Cherry
    The amount of data you are working with has no impact on the performance of dynamic SQL. Dynamic SQL hurts SQL 2000 because of the way that SQL Server 2000 and older don't handle dynamic SQL as effectively (with regard to the execution plans) as SQL 2005 and newer. These days the big problem with dynamic SQL is that the security issues still exist (and probably always will).
    66,365 pointsBadges:
    report
  • Elsuket
    The best way to execute dynamic T-SQL code is to execute it with through the system stored procedure sp_executesql, which is able to cache the dynamic SQL query plans (what does not do the EXEC command). It also offers the advantage to use OUTPUT variables, and to affect them to the local variables context, as when you execute dynamic T-SQL code, its context of execution is distinct from the procedure which encapsulates it. @++ ;)
    10 pointsBadges:
    report
  • MarlonR
    I think the main question here is whether he can make summary data without having to resort to cursor or dynamic SQL. There are case against and for both Cursor and Dynamic SQL. You can do a quick research on that and you'll come up with a number of great articles. But for summarizing data, have you looked at PIVOT (see my sample here:
  • http://dbalink.wordpress.com/2008/10/29/summarize-aggregated-data-using-pivot-in-tsql/
  • ) or maybe Common Table Expressions (SQL Server 2005+)?
    50 pointsBadges:
    report
  • Denny Cherry
    Elsuket, Execution plans are cached when using EXEC. Depending on they query they can also be parametrized.
    66,365 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following