Click here to Skip to main content
15,877,168 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I am new to sql server .I have recently downloaded Sqlserver 2008 and connected from vb.net . I am trying to create dynamic tables
with two columns such as
name,amount by passing table
names from text box .
SQL
CREATE TABLE " & TblName & "( [Name] TEXT(10), [Amount] TEXT(6))

table1
-------------
name | amount
   a | 40
   b | 60

   table2
---------------
name | amount
   a | 150
   b | 50

I want to display output like this
table name| total
   table1 | 100
   table2 | 200

I can retrieve table names using the below query
SQL
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

I have tried the query but
it displays a empty column
SQL
SELECT 'SELECT ISNULL(SUM('+COLUMN_NAME + '),0) AS a FROM ' + TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'amount'

I would like to know
Is it possible to write a query without specifying table name?
Posted
Updated 24-May-15 11:10am
v3
Comments
jgakenhe 24-May-15 16:13pm    
That is not a good idea. How do you dispose the newly created table? You won't get any data back if you do not insert data into the table.
Maciej Los 24-May-15 16:55pm    
Is it possible to write a query without specifying table name? No!

try

SQL
declare @sql Nvarchar(max)
select @sql='select * from ('+  STUFF((SELECT ' union all '+ c
                    from (SELECT c= 'SELECT  '''+TABLE_NAME+''' as tablename, ISNULL(SUM('+COLUMN_NAME + '),0) AS Total FROM ' + TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'amount' ) p
                       FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,11,'') + ') t'

exec sp_executesql @sql
 
Share this answer
 
Instead of creating separate table for each unknown table. Why don't you try create one table named META_TABLE for all such tables and add all amount field into that one table?

You can opt below approach!

SQL
CREATE TABLE META_TABLE
(
  TABLE_NAME VARCHAR(100),
  NAME       VARCHAR(100),
  AMOUNT   INT
)

INSERT INTO META_TABLE(TABLE_NAME, NAME, AMOUNT)
SELECT 'table1', 'a', 40
UNION ALL
SELECT 'table1', 'b', 60
UNION ALL
SELECT 'table2', 'a', 150
UNION ALL
SELECT 'table2', 'b', 50

SELECT TABLE_NAME, SUM(Amount)
FROM  META_TABLE
GROUP BY TABLE_NAME
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900