How To Set Table Name Dynamically In ‘OLE DB Source’?
Nov 20, 2007
Hi,
I want to load data from one database (from all tables) to another database. For this I am using €˜Foreach loop€˜(with SMO enumerator), I can get table name for specific database into variable successfully but now I want to load data from that table, for this I am using data flow task. In data flow task I have added €˜OLE DB Source€™ with data access mode €˜Table name or view name variable€™ and set variable but it gives me an error when loading data from 2nd table due to change in schema.
How to set table name dynamically in €˜OLE DB source€™?
Thanks in advance J
Omkar.
View 7 Replies
ADVERTISEMENT
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
Oct 25, 2015
we have a table in our ERP database and we copy data from this table into another "stage"Â table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
View 4 Replies
View Related
Sep 14, 2015
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
[code]....
View 9 Replies
View Related
Aug 31, 2006
Hello all:
Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.
The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.
create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )
Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
.
.
.
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..
create table emp (
empid int, empname varchar(100), empage int....)
The table should look like as:
Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
.
.
.
n raj 45.................
================================================== ==
I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]
A small rough snippet of the code will be appreciated....
Waiting for replies........
saby
View 1 Replies
View Related
Oct 14, 2006
Hi,I have 30 Table with same structure but it will accept different value at RunTime according to AirCraftType. I wand to create a stored prosedure that will accept TableName, FlightDate, FlightNo. whenever i wnat to execute this code in the database I received error :Must declare the variable '@TableName'create proc InsertDataForAirBus330 @FlightNo varchar(5),@TableName varchr(5),@FlightDate datetime
as
insert into @TableName values(@JourneyDate,@FlightNo,18,42,280,3,7,35)plz suggest me how to solve this pboblem...Jasim...
View 2 Replies
View Related
Mar 30, 2012
How do I dynamically pivot a table? On my example below, the STORE changes.
View 2 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 10, 2008
Greetings all,
I want to be able to generate a table on the fly. I know I can use dynamic sql to do this but my brains have forzen this afternoon so any help would be much appreciated.
I have the first query that returns one column with three rows. The contents of the these three rows will form the new columns in the dynamically generated table. How can I do this?
Thanks.
View 11 Replies
View Related
Sep 1, 2006
Hello,
I have to design a DTS package (not SSIS ) in which i want to select the destination table dynamically. Can any one help me out.
Thanks
MV
View 2 Replies
View Related
Feb 17, 2008
Hi
i'm a newbie reporting services developer (first experience) and i have this problem that i'm trying to explain you by steps:
1 i link the report to a stored procedure to know if a user is enabled to watch or not the results table
2 if the user not enable to watch i'll write on the report "user not enable..." and so i hide the result table
...is it possible to do this?
Thanks in advance for any information
View 7 Replies
View Related
May 21, 1999
I'm trying to create a procedure that can access a column in a table dynamically.<br>
Lets say I have the following table<br>
create table table_items<br>
(rec int identity not null,<br>
item1 int,<br>
item2 int,<br>
item3 int)<br>
and I have a procedure as such
create procedure access_item<br>
@rec int,<br>
@itemindex int,<br>
@item int as<br>
select @item = (select item1 from table_items where rec = @rec)<br>
However, the column I want to access is @itemindex.<br>
If @itemindex = 1, then set @item to item1<br>
if @itemindex = 2, then set @item to item2<br>
if @itemindex = 3, then set @item to item3<br>
I can't use a simple 'if/then' selection, because the number of columns in the table can grow, and I don't want to have to rewrite the procedure everytime a column is added.<br>
How do I do this?<br>
I tried to use an execute command, as such<br>
create procedure access_item_2<br>
@rec int,<br>
@itemindex int,<br>
@item int as<br>
declare @sql varchar(255)<br>
select @sql = "select @item = (select item" + convert(varchar(2), itemindex) + " from table_items where rec = @rec)"<br>
execute (@sql)<br>
but when the procedure runs, I get the following error:<br>
'@item is not a valid variable.'<br>
What else can I do?<br>
Please e-mail me at sam@microcsl
View 2 Replies
View Related
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
View Related
Apr 10, 2008
Hi there,
I want to create an SQL Function that checks if a table exists and returns true or false. I will pass this function a paramter (say @COMPANYID) e.g.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblmyTableName_' + @COMPANYID) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
how would I write this so the query is dynamically executed and I can get a value of true or false back?
View 4 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
Jul 23, 2005
I need to write a stored procedure to verify that a table exists andalso that the user executing the stored procedure has access to thespecified table.Any user can call this publicly available procedure and pass a databasename, an owner name and a table name as parameters. The procedurereturns success if the table exists and the user has access to it, orfails if he doesn't. Here's a simplified version of what I have, butI'm wondering if there's a better way. Thanks.create procedure dumb asbegindeclare @myError int,@mytable varchar(128),@myquery varchar(128)select @mytable = '[Northwind].[dbo].[sysobjects2]'select @myquery = 'DECLARE @x int SELECT @x = count(1) from ' +@mytable + ' where 1 = 2'exec (@myquery)select @myError = @@ERRORif @myError != 0BEGINRAISERROR ('ERROR: The specified table %s cannot be accessed.', 10, 1,@mytable)RETURN 1endendgo
View 10 Replies
View Related
Apr 29, 2008
Hi,
how to dynamically create columns for a table
View 2 Replies
View Related
May 1, 2007
Hi all,
Is there a way to change the dataset being used by a table dynamically ?
Regards,
Neil
View 2 Replies
View Related
May 7, 2007
Can you dynamically set the name of the table in the SelectCommand section of the SqlDataSource? (If it is relevant, I code in C#)
For example,
<asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:Test-MySQL %>" ProviderName="<%$ ConnectionStrings:Test-MySQL.ProviderName %>" SelectCommand="SELECT * FROM TestTable">
I would like to replace 'TestTable' with the name of a table that is extracted from a string array in the code-behind.
Appreciatively,Peter
View 4 Replies
View Related
Oct 3, 2007
I have the folowing databases DB1,DB2,DB3,D4,DB5........
I have to loop through each of the databases and find out if the database has a tablename with the word 'Documents'( like 'tbdocuments' or 'tbemployeedocuments' and so on......)
If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database.
can someone show me the script to do it?
Thanks.
View 6 Replies
View Related
Jun 11, 2006
Hi,
Is there a way to dynamically create a new table in sql server express using the code behind with vb on a Page_Load event?
Thanks Matt
View 2 Replies
View Related
Apr 21, 2008
I am try to build a Stored procedure to insert into a table whose name is contain in variable @tname, other field are just variable to supply value to table fields,i have implement simple type of SP for "SELECT" AND "CREATE" ,but this procedure does not work, please help me
CREATE proc insert_mess
@tname nvarchar(50),
@mass nvarchar(250),
@to_id int,
@form_id int
as
exec ('insert into ['+ @tname +'] ( [message],to_id,form_id)
values ('+ @mass +',' +@to_id + ','+@form_id +') ')
View 3 Replies
View Related
Nov 27, 2014
Need to scheduled to copy table data from one database to another database daily @7:00 AM by creating the table with date and time stamp.
Example :
Test1DB ---> Table1
Test2DB -----> Table1_281120140700 in Day1 and Table2_291120140700 in Day2.....and so.
SELECT * INTO Test2db.dbo.new_table_name FROM Test1db.dbo.old_table_name in this format ??
script and scheduled to run daily @ 7:00AM?
View 1 Replies
View Related
Jul 20, 2005
Hello,I am interested in dynamically creating temp tables using avariable in MS SQL Server 2000.For example:DECLARE @l_personsUID intselect @l_personsUID = 9842create table ##Test1table /*then the @l_personsUID */(resultset1 int)The key to the problem is that I want to use the variable@l_personsUID to name then temp table. The name of the temp tableshould be ##Test1table9842 not ##Test1table.Thanks for you help.Billy
View 5 Replies
View Related
May 18, 2015
How Can I select Table Dynamically from in Side SQL Query
i.e.,
Select * from (Here I want Select the Dynamically from other Query)
View 6 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 2, 2006
Hi
I have a report of 8.5"(W) by 11"(H).The header and footer size are fixed.
The header size is 4.75 in and footer size is 2.375in.
In the report body I have a table where the data is dynamic.
The problem is when the data is small i mean like 2 or 3 columns it doesnt touch the footer .
If we have more than 10 columns or so it goes to the next page and even then it doesnt touch the footer beacuse the data stops there.
If we have like 4 columns or so in the table then it touches the footer.
I tried to replace the table with the list but I got the same problem there also.
I tried to place the table in a rectangle and it has the same problem.
I want the table to touch the footer no matter how much the data is .
Is there any work around for this problem?
I really appreciate your help and time
Thanks
Hobbs
View 3 Replies
View Related
Apr 20, 2015
I am having 2 tables one is staging temp and another is main import table.
In my staging table there are 3 column Col001,Id,Loaddate
in Col001 column data are present with '¯' delemeter.
I am having function which is used to load data from staging to import table using one function.
this function create a insert statement.
My Existing function
-- Description: To Split a Delimited field by a Delimiter
ALTER FUNCTION [dbo].[ufn_SplitFieldByDelimiter]
(
@fieldname varchar(max)
,@delimiter varchar(max)
,@delimiter_count int
[Code] ....
I am unable to get correct statement with above function.
View 1 Replies
View Related
Sep 24, 2006
Is there a way I can write a query to dynamically select a database table. That is, instead of having a variable for a certain column like customerId which would be €œcustomerID = @customerID€? I want to use the variable to select the table. I assume I may have to use a stored procedure but I am unclear as to how this would be coded.
Thanks
View 1 Replies
View Related
Jun 15, 2007
Hi,
I am new to .NET world. I am using visual studio express.
I am developing website using ASP.NET and C#.
I want to add buttons dynamically on a table row on my web page.
For this I have written this code in "example.aspx" file
<asp:Table ID="tblExample" GridLines="Both" BorderWidth="1" runat="server" >
</asp:Table>
In my corresponding "example.aspx.cs" file i have written
TableRow tr = new TableRow();
.....................
.....................
TableCell tc3 = new TableCell();
tc3.Width = 120;
Button bt = new Button();
bt.Text = btnStop.Text;
bt.Width = 120;
bt.CommandArgument = lrs.IpAddress + ":" + lrs.PortNo;
bt.Click += new EventHandler(cmdStop_Click);
tc3.Controls.Add(bt);
tr.Cells.Add(tc3);
tblExample.Rows.Add(tr);
In my EventHandler "cmdStop_Click" I am trying to perform some action but on that particular row's data.
My page is also reloading after every 5 secs.
After clicking a button in a row, when page refreshes, I am getting this message in popup error message. also that entry is ommited(as per code in EventHandler)
______________________________________________________
"The Page cannot be refreshed without resending the information.
Click retry to resend the information again.
or click Cancel to return to the page that you were trying to view"
resetButton cancelButton
_______________________________________________________
How to bind that button to particular row so that when I click on a button the action should be performed on that particular row's data.
Thanks
View 1 Replies
View Related
Feb 1, 2007
I am building a dashboard features that allows user to select reports from a dropdownlist. It is pulling from a table called Reports (cols: ReportID, Description, sqlView) In this Report table the report is associated to a view that queries the report.
And the user's selections are stored in table called UserReport (cols: userID, ReportID, createDt) .
I need to get a Dataset to contain datables of all reports selected. (for example a user select 3 reports, the dataset should contain 3 datables that represent the report).
I want to accomplish this by create a store procedure that queries the Reports table and then dynamically executes the views that related to the user selected reports. Can anyone give me an example on how to create the storeprocedure?
Thanks,
CG
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
