How To Dynamically Create Columns For A Table
Apr 29, 2008Hi,
how to dynamically create columns for a table
ADVERTISEMENT
Is It Possible To Dynamically Create Columns In A Table In SSRS
Nov 19, 2007
Hi,
I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.
So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths in those tables..
any help is appreciated..
Regards
Karen
SQL Server 2012 :: Dynamically Create A Script Only Selecting Columns Where There Is Data?
Dec 2, 2013I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
Create Target Table Dynamically Based On Source Table Schema?
Sep 13, 2005I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely,
Bryan Ax
Create Table Dynamically
Apr 14, 2008Hi,
I have N1 table where columns name(id,Field). Base on the fields of this table I want to create N2 table from SP where data from N1 will be columns in N2.
id Field
-- ------
1 ID
2 First
3 Last
Create table N2(ID,First,Last)
regards,
Mark
T-SQL (SS2K8) :: Create Table Dynamically?
Sep 5, 2014I am having SP which gives, two result sets. The columns which are coming from result sets are also dynamic.
i.e. some time 5 columns and some time 10 columns.
Now I want to load this output into 2 different tables on daily basis. This would be truncate/delete table and load again.
Now my problem is that as I am not sure about columns, Is it possible to create table(Physical Table) depends on output of SP, and after load data into it.
During each load we can drop table, No issue and we can handle this through SSIS Package.
Create Table And Column Dynamically
Jun 2, 2008Hi,
There is a table exists in a database, I have to write a stored procedure to create the same table in different database, with the same column name and field. This should be done in runtime. Is it possible. The table will be passed as a parameter to the stored procedure.
Create Destination Table Dynamically In A Program
Feb 17, 2007HI,
I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.
Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.
Has anybody done that?
Any help in this regard is greatly appreciated.
Pavan
SQL Server 2008 :: Dynamically Create The Table With Current Date At The End?
Feb 12, 2015I am running a script by the end of the day. What I need is the rows in my temp table get saved in a permanent table.
The name of the table should end with the current date at the end.
Declare @tab varchar(100)
set @tab = 'MPOG_Research..ACRC_427_' + CONVERT(CHAR(10), GETDATE(), 112 )
IF object_id(@tab ) IS NOT NULL
DROP TABLE '@tab';
Select * INTO @tab from #acrc427;
SQL Server 2012 :: Create Table Syntax Dynamically On Run Time
Apr 19, 2015I am having 100 of flat files need to load in respective staging table.I want to create table on run time as per filename input.suppose if input filename is ABC then table name should be Staging_ABC if file name is XYZ then it should be Staging_XYZ.Table structure is below need to create at run time
CREATE TABLE Staging_'Filename'(
[COL001] [varchar](4000) NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[LoadDate] [datetime] NOT NULL default getdate()
)
Integration Services :: Create A Table Based On Existence Dynamically
Aug 10, 2015I am using the following script to check existence of table in the Database and create it dynamically...
This is working when table not existed, it error-ed when the table existed...
This script i am using in the Exec Sql Task.....
[Execute SQL Task] Error: Executing the query "declare @ODSDB varchar(50)
declare @SQLSTMT varcha..." failed with the following error: "There is already an object named 'addressTable' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not
set correctly, or connection not established correctly.
declare @ODSDB varchar(50)
declare @SQLSTMT varchar(max)
set @ODSDB = 'SampleDB'
begin
set @SQLSTMT = '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + @ODSDB + '.dbo.addressTable'') and Type=''U'')
[Code] ...........
SSIS 2005: Dynamically Create Data Flow Dest Table If It Does Not Exist?
Sep 20, 2007Hi all,
I am looking for a way to leave a Data Flow Task destination table name as-is, and have SSIS auto-create the table if it doesn't exist already.
I searched on this in the forums but based on the question it's difficult to kow if it has been answered or not.
Details:
I am writing some SSIS packages that need to be executable on another server. Many of the Data Flow Tasks copy data (such as from a Fuzzy Grouping transformation, and lots of other stuff) into a new table. But the other server will not have these tables set up for the first run.
My current solution is to check information_schema.tables and drop IF EXISTS. But, then the Data Flow Task will not work (becase table does not exist). So, I script to new window a create table statement based on the existing table that I use in my dev environment. This is a hack and I want to find a better method.
It is quite possible (although unlikely) that the source columns could be changed in the future, or some query used to pull the data might be modified. If this happens, then I would need to change the CREATE TABLE Execute SQL task. I want my package to accommodate without having to modify it.
When I use the Import/Export Wizard, I can select a table name from the drop down list OR type in a new name. When I type in the new name, it assumes I want to create the table. NOW, is there a way to mimic this in BI Developer Studio? Yep, I saved the Wizard version of the SSIS package and all it does is run a CREATE TABLE statement first.
I am looking for a way to leave a Data Flow Task destination table name as-is, and have SSIS auto-create the table if it doesn't exist already.
Any ideas?
Brian Pulliam
Integration Services :: Create SSIS Package Dynamically For Inserting Data From Flat File To Table?
Sep 30, 2015I have requirement like to develop dynamic package for inserting data from flat file to table.
Find below points for more clarification :--
1) if I changed the flat file values and name in source variable AND  the table name should be also changed based on variable value .
2) it should dynamically mapped with column values with source file as we have to insert data in target table.
See below diagram for more clarification.
Create Table With Veriable Number Of Columns
Apr 12, 2008
Hi everyone,
I need to create temporary table in one of the SP.The problem is that number of columns in table will vary depanding on input in SP.
How can i create table with variable number of columns?
Thanks,
Alex
How To Create Dynamic Columns In A Temporary Table
Sep 5, 2007Hi there,
i have a requirement that a temporary table contains dynamic columns depending on where condition.
my actual table is like
Key
Value
X1
x
X3
x
X5
x
Y1
y
Y2
y
when user select x, the input variable passed to stored proc and the result is shown like
column names
X1 X3 X5 as column headers.
the select query is from temporary table.
these out put is based on the user selection. so the temporary table created with columns dynamically.
please help me out.
please let me know if you didn't understand.
thanks
Praveen.
T-SQL (SS2K8) :: Identify Columns Which Will Create Unique Record In A Table
Sep 15, 2014I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
View 4 Replies View RelatedHow Create Multiple Driildowns For All Columns In Table Using Ssrs Report
Dec 13, 2007
hai, iam new to ssrs please help me,
my report having 10 columns, i can explain my need with example that is, assume like this customer id is first column of my table customer id is 101 it having 3 departments a1,b1,c1,perticular department that is a1 having emp1,b1 having emp2,c1 having emp 3.
i want output like this when clicking + customer id driildwon it display 3 departments taht is a1,b1,c1, when +a1 drill down clicking i need to dispaly emp1, corresponding b1 to emp2 , c1 to emp 3.
above explanation is only one column of the table, like that iam also displaying this driiling procedure for remaining different columns.
and i need to display customer information by weekly,daily,monthly,yearly at bottom of the report
please give which logic used in creating format like above drilldown report which having multiple drilldowns for all columns in a table
if any body give procedure for creating fromat for drilldown report which having multiple drilldowns for all columns in a table is appriciate.
thanks
jacks
SQL Server Admin 2014 :: Create Dynamic Columns In Temp Table?
Jun 9, 2014I want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below
CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]
for i want to generate table for the above feilds with datatype
Adding Columns Dynamically
Sep 3, 2006Dear All,
I have a requirement, in which i have to add columns dynamically depending the records of a master table in sql server 2000.
Pl, do help me
Create SP Dynamically
Sep 2, 2005Hello,
I am trying to create a sp dynamically. However each time, I run my sp, which creates the dynamic SQL for the sp, then it automatically changes:
CREATE PROCEDURE .....
to
Alter PROCEDURE ....
Then I am obvoiusly getting the error msg:
Invalid object name.
How can I get rid of that?
Dynamically Trasnpose Rows Into Columns
Dec 10, 2005I've seen several posts that begin to address this problem, but havenot found a simple, elegant solution that will accomplish this goal.The important part of this solution is that it must be completelydynamic - I have over 40 different categories of devices, each withdifferent fields, and each search will return only one category. Ihave no knowledge of the number or datatype of these field namesbeforehand and must use the sp to dynamically create the table and thentranspose the data.Here is an exampleI have normalized data in this format (this is a simplification)deviceId fieldName fieldValue1 color red1 shape square1 weight(kg) 2.02 shape round2 weight(kg) 1.53 color blue3 shape oval3 weight(kg) 1.0I would like to convert this to the format: (note that it must handlenulls - deviceId 2)deviceId color shape weight(kg)1 red square 2.02 round 1.53 blue oval 1.0Anyone with any thoughts on how best to accomplish this?thanks,Matt
View 19 Replies View RelatedSearching In Columns Dynamically Without Dynamic SQL
May 8, 2007Hi everyone
Is it possible to search dynamically in table columns without using dynamic SQL (i.e. EXEC or sp_executesql)?
I wanna exec FREETEXT(<column_name>, @searchPhrase) by giving columns' names as paramater to stored proc. Is it possible to avoid using dynamic SQL?
Thanks in advance
Dynamically Order Columns In Report
Mar 10, 2008
I have a report that displays data based on the last 12 months. Is there a way I can order the columns (header and data) based on the month it was run. eg. If I were to run the report in March, I want the columns to be ordered like this:
MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB
If run the report in April, I want the columns to be ordered like this:
APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB, MAR
So, the columns in the report are always ordered (12 months backward) based on the month it was run.
Create Tables Dynamically
Sep 12, 2007Hi! I am using VS 2003 (v 1.1)
I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.
How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.
Give me the complete code with can example (if possible)
Thanks & Regrads
Jai Shankar
How To Dynamically Create Tables
Oct 7, 2006I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.
The Typed Table might get created by something like:
public void CreateTypedLiterals(Type type)
{
String sql = String.Format(
"CREATE TABLE [Literals_{1}] (" +
"ID int DISTINCT NOT NULL, Value {1})",
// BUG: does not work
// WARNING: introduces a potential sql-injection problem
type.ToString()
);
...
As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:
[SqlProcedure]
public void CreateTypedLiterals(Type type)
{
DataTable TypedLiterals =
new DataTable(
String.Format("Literals_{0}", type.ToString()));
TypedLiterals.Columns.Add(
"ID", typeof(int), "DISTINCT NOT NULL");
TypedLiterals.Columns.Add("Value", type);
...
But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:
using(Microsoft.SqlServer.Server)
{
CurrentDatabase.Tables.Add(TypedLiterals);
}
But this is afaik not possible. Has anybody an idea how to solve this issure?
Create Subtotals Dynamically
Apr 3, 2007
I am trying to dynamically create subtotals base on values in a column. I also have to group by year. I have gotten the group by year. I used the (previous function) to check for the year and col1 not being equal. I am getting the first subtotal back, don't know how to proceed in my function to return the remaining subtotals
i.e.
col1 col2 col3
aaa 111 2005
bbb 222 2006
aaa 333 2006
ccc 444 2005
ccc 555 2006
bbb 666 2006
bbb 777 2006
ddd 888 2007
2005
aaa subtotal: 1
ccc subtotal: 1
total: 2
2006
aaa subtotal: 1
bbb subtotal: 3
ccc subtotal: 1
total: 5
2007
ddd subtotal: 1
total: 1
grandtotal:
aaa 2
bbb 3
ccc 2
ddd 1
totalnumber 8
Create Charts Dynamically
May 3, 2008Hi there!
I have a certain scenario and would like to know if it would be recommendable to try and realize it through RS.
I have a matrix of sales data (countries, regions, categories, total sales, Year-To-Date, changes, etc.) that need to be shown on charts. The charts themselves have a single format (bars). The out put needed is a relatively large number of charts (3000+) that would allow for a great number of comparisons (country to country, region to region, etc.). All these charts share a single layout. What would be varying greatly are the values and the labels attached to the elements on the chart (labels of the bars, title of the chart, legend, footnotes, etc.)
My plan is the follwoing:
- create a .net module that would prepare the data for the chart. This would be done with two tables
- parent table with general information
- child table with the the values for the bars and the labels
- Create a parametrized report in RS that accepts the Id of a report from the parent table
- Call this report for each and every report that can be found in the parent table
I would really appreciate any information of the feasabilitiy of this plan. I have worked a lot with Crystal before, but I don't have it anymore and will be using RS from now on. I haven't really started working with RS as of yet, but would need to know if this is "doable" with RS!
Thank you very much in advance for your replies!
Greetings
NB
Dynamically Delete Entire Columns' Values
Aug 3, 2004Hello all,
I was wondering if anyone knew of a way to dynamically delete all of the values for a group of columns. What I mean by this is that lets say a table (TableA) has five fields (Field1, Field2, Field3, Field4, and Field5) with 100 rows of data. I want to delete all of the data in Field1, Field2, and Field4. I do not want to delete any of the data in Field3 and Field5. I would then end up with a table with 5 fields and 100 row, but only 2 fields (Field3 and Field5) have data.
The catch is that I can't hardcode the field names of the fields I want to clear out (Field1, Field2, and Field4) into the SQL. This is because if any new fields are eventually added to the table I want them to be cleared out as well without modifying the SQL.
I can hardcode the field names of the fields that I want to keep values for (Field3 and Field5) in the SQL.
If anyone has any idea how to do this, I would greatly appreciate it.
Thanks in advance!
Dynamically Concatenating Multiple Columns In Sequence?
Oct 16, 2014I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths.
eg..
Input 1:
Table A: (below are the fields and their respective values, not all fields will have values)
-----------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1234567890
LIFNR
VKORG-->a234
PRCTR
KUNRE-->4355325363
LIFRE-->88390234
PRODH
---------
Table BSadIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)
---------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1
LIFNR
VKORG-->3
PRCTR
KUNRE-->2
LIFRE -->4
PRODH
LEN--> 10|10|4|10
Expected Result:
---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR1234567890
LIFNR
VKORGa234
PRCTR
KUNRE4355325363
LIFRE0088390234
PRODH
Concat_String12345678904355325363a2340088390234
Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif
then after concat the value should be like below:
12345678904355325363a234 88390234
Note:The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...eg..
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR -->2
KUNNR--> 4
LIFNR
VKORG-->1
PRCTR
KUNRE
LIFRE --> 3
PRODH
I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...
I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..
Dynamically Hiding Columns In A Matrix Report
Mar 5, 2008Hi,
I am wondering if someone has some experience with hiding columns in a Matrix report. I have got two details columns: "Yr to Yr Credits Growth€? and "Yr to Yr Credits Growth %" and those two columns return only one value "N/A" for the earliest year since there is nothing to compare to. Thus, I don't really need them for the earliest year. On top of detail columns, I have got three matrix groups: matrix1-Year, matrix1-Quarter and matrix-Date. Once those two detail columns are hidden, I would like obviously resize (shrink) those three matrix groups columns to reflect the fact that the detail columns were hidden.
Thanks!
Create And Then USE A Dynamically-named Database?
Nov 2, 2006I have a need to create a database, and then populate it. However, thecode below doesn't work as I hoped it might (it creates the table inthe "master" database, which is Not A Good Thing). I know already(thanks Tony!) that if you use Dynamic SQL for the USE command, thenthe subsequent operations need to be Dynamic SQL as well, which is apity since there are over 11,000 lines of it and I don't really fancydebugging it!Does anyone have a cunning plan? In a nutshell, I would like to beable to:1. Create a new database with a derived name - as in the case below aname based on the year, though it might be month as well.2. Create and populate tables in this new database.These operations would ideally be running from a scheduled job.Any thoughts?TIAEdward====================================USE MASTERDECLARE @DBName VARCHAR(123)SET @DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)if not exists(select dbid from master.dbo.sysdatabases where name =@DBName)exec('CREATE DATABASE ' + @DBName)elseraiserror('Database already exists.',3, 1)EXEC ('USE ' + @DBName)if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[TestTable]GOCREATE TABLE [dbo].[TestTable] ([TestID] [int] NOT NULL ,[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY]GO
View 3 Replies View RelatedI Need To Dynamically Create A Script Component
Oct 8, 2007Hi,
I need to dynamically create a script component (by code).
Is it possible?
Thanks ahead,
Eran
Trying To Dynamically Create Text Files
Oct 27, 2006I need to get a list of customer ids and then use them as a parameter to select from a transaction table and then create a file for each customer. I have used an execute SQL task to get the list of customers and have put the result set into a variable.
How do go through the recordset to create a file for each customer?