T-SQL (SS2K8) :: Create Table Dynamically?
Sep 5, 2014
I 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.
View 2 Replies
ADVERTISEMENT
May 27, 2015
UPDATE Report
SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014'
when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015'
when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016'
when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017'
End
Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.
View 4 Replies
View Related
Sep 13, 2005
I’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
View 9 Replies
View Related
Apr 14, 2008
Hi,
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
View 6 Replies
View Related
Jun 2, 2008
Hi,
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.
View 3 Replies
View Related
Apr 29, 2008
Hi,
how to dynamically create columns for a table
View 2 Replies
View Related
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
View 9 Replies
View Related
Feb 17, 2007
HI,
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
View 3 Replies
View Related
Feb 12, 2015
I 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;
View 3 Replies
View Related
Apr 19, 2015
I 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()
)
View 2 Replies
View Related
Aug 10, 2015
I 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] ...........
View 8 Replies
View Related
Aug 4, 2015
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
View 9 Replies
View Related
Sep 20, 2007
Hi 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
View 12 Replies
View Related
Sep 30, 2015
I 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.
View 10 Replies
View Related
Oct 20, 2014
I would like to create a table valued function using the following data:
create table #WeightedAVG
(
Segment varchar(20),
orders decimal,
calls int
);
insert into #WeightedAVG
[code].....
I would like to create a function from this where I can input columns, and two numbers to get an average to output in a table ie,
CREATE FUNCTION WeightedAVG(@divisor int, @dividend int, @table varchar, @columns varchar)
returns @Result table
(
col1 varchar(25),
WeightedAVG float
[Code] .....
View 4 Replies
View Related
Apr 16, 2014
I have view something like
Create view All_employee
AS
SELECT Emp_Name, Emp_code FROM dbo.Employee
UNION ALL
SELECT Emp_Name, Emp_code FROM Emp_201402.Employee
But we have a different "Schema" for same table because we have archive table with same table name but with different schema name. Now we have req to make view which contain data of all table. But I can't seem to figure out how to do it in a view.
SET NOCOUNT ON
DECLARE @Count INT, @TotalCount INT, @SQL VARCHAR( MAX )
DECLARE @Schema TABLE ( ID INT, NAME VARCHAR(512) )
INSERT INTO @Schema
SELECT ROW_NUMBER() OVER (ORDER BY SCHEMA_ID), Name FROM sys.schemas where name like '%emloyee%' ORDER BY schema_id ASC
[Code] ....
Don' think that works.
Is this possible with a view or it other way to do it?
View 7 Replies
View Related
Sep 15, 2014
I 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 Related
Jan 30, 2015
I'm trying to write a function that will retrieve the last backup date/time of a particular database on a remote server (i.e. by querying msdb.dbo.backupset). Unfortunately, you can't use sp_executesql in a function, so I can't figure out a way to pass the server name to the query and still be able to return the datetime value back to the calling TSQL code (so that rules out using EXEC().
View 3 Replies
View Related
Apr 15, 2015
SQL 2008
I have a table that has company id, attachment file name, folderexists columns.
First what I need to do is create a series of folder or directories on a networked server using the company id as the folder name where the folder name does not already exist.
Second I need to move files based on attachment file name and company id to the proper folder.
For those who want to know, this is a remediation project because of a bug in our application.
The application is supposed to created the folder based on company id and then put the attachment in that folder.
View 9 Replies
View Related
Jun 24, 2014
I have a master table containing details of over 800000 surveys made up of approximately 400 distinct document names and versions. Each document can have as few as 10 questions but as many as 150. Each question represents one row.
My challenge is to create a separate spreadsheet for each of the 400 distinct document names and versions containing all the rows and columns present in the master table. The largest number of rows would be around 150 and therefore each spreadsheet will not be very big.
e.g. in my sample data below, i will need to create individual Excel files named as follows . . .
"Document1Version1.xlsx" containing all the column names and 6 rows for the 6 questions relating to Document 1 version 1
"Document1Version2.xlsx" containing all the column names and 8 rows for the 8 questions relating to Document 1 version 2
"Document2Version1.xlsx" containing all the column names and 4 rows for the 4 questions relating to Document 2 version 1
I assume that one of the first things is to create a lookup of the distinct document names and versions assign some variables and then use this lookup to loop through and sequentially filter the master table data ready for creating the individual Excel files.
--CREATE TEMP TABLE FOR EXAMPLE
IF OBJECT_ID('tempdb..#excelTest') IS NOT NULL DROP TABLE #excelTest
CREATE TABLE #excelTest (
[rowID] [nvarchar](10) NULL,
[docName] [nvarchar](50) NULL,
[Code] .....
--Output
rowIDdocNamedocVersionquestionblankField
1document11q1NULL
2document11q2NULL
3document11q3NULL
4document11q4NULL
5document11q5NULL
6document11q6NULL
[Code] .....
View 9 Replies
View Related
May 6, 2014
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
View 9 Replies
View Related
Jun 26, 2014
A user is wanting to run a query against the current 3 active databases. These change every month on the 1st e.g. Test-06-14. Therefore I want to set up a dynamic query which will always use the current database. I believe I am almost there but I cannot set the USE @DatabaseName dynamically yet.
DECLARE @DB_Name varchar(100)
DECLARE @DatabaseName varchar(100)
DECLARE @Command nvarchar(200)
DECLARE @Command2 nvarchar(200)
DECLARE database_cursor CURSOR FOR
[Code] ....
View 9 Replies
View Related
Aug 28, 2014
I have an SSIS package that puts together a series of queries to check all text fields in all tables of a database for a set of "invalid" characters. I know the root query works as we have been running it manually for quite a while. However, now that I have changed it to build dynamically, I cannot figure out why it does not work.
Here is the code. The EXEC (@sSQL) returns 3 records. It just so happens that these 3 records are the only ones in the database with a '?' in the field. The very bottom statement returns 0 records. I cannot figure out what the difference is. When I do PRINT (@sSQL), it looks fine to me, except that @Pattern is fully printed.
DECLARE @Pattern NVARCHAR(MAX)
DECLARE @sSQL NVARCHAR(MAX)
DECLARE @1 nvarchar(max)
DECLARE @2 nvarchar(max)
DECLARE @loop int
[code].....
View 9 Replies
View Related
Sep 2, 2014
I have table of standardized data that I'm merging into from an Import table.
This import table may have the [ContactName]. I one row in the import table has the contact name then every row would have the contact name.
What I'd like to be able to do is something like this in the UPDATE portion of the Merge (I don't care about the insert phase, since I can insert a null [ContactName] since I'm not overwritting an existing [ContactName]
MERGE INTO [dbo].[Standardized] AS [target]
USING [dbo].[ImportDestination]
AS [Source]
ON [Source].[Key] = [Target].[Key]
WHEN MATCHED
THEN UPDATE
SET[ContactName] = CASE WHEN source.[ContactName] IS NOT NULL THEN source.[ContactName] else target.[ContactName];
Is anything like that possible and if the code above works it would only work on a row by row bases. Is it possible to figure out if any [ContactName] has data and if so possibly overwrite an old [ContactName] with a null?
View 1 Replies
View Related
Nov 9, 2014
I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically
drop table test
create table test
(
id varchar(10) not null,
col1 varchar(10) ,
col2 varchar(10)
[Code] .....
Now my next step is verify if the variable is blank or not how do i do that ?
How do i verify all of the columns one after the other .
I am after the statement like this dynamically
-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
--IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '
I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.
View 4 Replies
View Related
May 21, 2015
I have two tables:
tblServer
tblInstance
tblServer represents a server, tblInstance represents any SQL Server instances present on the server. They're related thru srvID.
I'd like to write a query that gives me servers with all instances, on one row. This dataset will populate a List in an SSRS report.
I have this but it's clunky and does not provide for more than 2 instances on the server. I'd like this to account for any number of instances all on one row without having to hard code multiple joins:
with serverInfo as
(
select
srv.srvType as Type
,srv.srvName as ServerName
,srv.srvIP as ServerIP
,ins.instNetName as InstanceNetName
[Code] .....
View 3 Replies
View Related
May 19, 2015
DELETE FROM Report_temp2
WHERE MSSalesID in
( Select Report_temp.MSSalesID FROM Report_temp)
DELETE FROM Report_temp
WHEREMSDate < '2015-07-01'
Actually the year stating form july.
Q1 is july,aug,sep.
Q2 is oct nov,dec.
Q3 is jan,feb,mar.
Q4 is april, may,june.
So what I need is dynamically I want to delete the data every year prior to current year.
View 4 Replies
View Related
May 27, 2015
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @CIR AS tmp On t.RowId = tmp.Max_RowId
[Code] ....
But CASE statement is not working...
View 7 Replies
View Related
Sep 2, 2005
Hello,
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?
View 14 Replies
View Related
Sep 12, 2007
Hi! 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
View 1 Replies
View Related
Oct 7, 2006
I'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?
View 5 Replies
View Related
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
View 1 Replies
View Related
May 3, 2008
Hi 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
View 5 Replies
View Related
