Dynamically Selecting Table In DTS
Sep 1, 2006Hello,
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
ADVERTISEMENT
Selecting Database Dynamically (as A Variable)
Jun 2, 2006Hey all.
I need to call another database in the same server dynamically; creating a varible off it or somehow. Example:
-- In database DBaseA
declare @dbname as varchar (30)
set @dbname='[DBaseB].[dbo]'
select * from @dbname.[table2]
I know this block of code doesnt work. And i need a way of making the database DBaseB dynamic.
DBaseA and DBaseB are in the same server. I am using Sql Server 2000.
How do i solve this? Solutions ?
Dynamically Selecting Tables In OLEDB Source
Mar 17, 2008hello guys,
I have 10 tables, table1, table2, table3, table4.......table10. all these tables have different structure.
From each of these tables I want extract data and dump into flat file csv.
So i have OLEDB source and FlatFile Destination.
If i write seperate data flow task for each of the tables, then there is no issue.
But i want to use a single data flow task for all these tables. So for this, i use a variable @SQLStr . And i dynamically set the value of this variable to select * from table1, select * from table2.........selct * from table10.
So in the OLEDB source I select Data Access Mode as : SQL Command from variable. And I use @SQLStr for this varible.
For Destination, i dynamically generate the flat file for each of the table.
But this doesnt work, i get validation errors.
So, first can i use a single data flow task to dynamically change the source(different source tables) and destination. If so, what i am missing in the above process?
any help appriciated.
Thanks
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
Transact SQL :: Dynamically Alter Schema Of Stage Table When Source Table Structure Changed?
Oct 25, 2015we 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 RelatedSQL Server 2012 :: Return A Table Of Table-names Dynamically?
Sep 14, 2015I 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]....
Selecting ONLY Records From One Table Having ALL Data Contained In Other Table (GROUP BY?)
Jul 20, 2005Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz
View 2 Replies View RelatedSelecting SUM From One Table As Field In Results Of Rows From Another Table.
Nov 20, 2007Hi,
These aren't my tables, but lets dumb it down for my sake!
I have 2 tables:
CompanyInfo: IDNumber, EffectiveDate, CompanyName
TransacationInfo: TransactID, CompanyID, TransDate, Amount
There are several records in TransactionInfo for each record in CompanyInfo.
I want to:
SELECT CompanyInfo.IDNumber, CompanyInfo.EffectiveDate, CompanyInfo.CompanyName,
SUM(TransactionInfo.Amount)
How can I do this? Can I?
Creating Table Fields Dynamically From Another Table Row
Aug 31, 2006Hello 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
Selecting From A Table, When I Only Have The Table Name As A String Format
Apr 7, 2006is there some way to allow this to execute, using a function or something?:
select * from 'cusomer'
you see i have a list of about 300 table name in string format, and i want to use a cursor to itterate through each table, but as all the table names are in string format i cant get it done. any ideas??
How To Put Table Name At Dynamically ???
Oct 14, 2006Hi,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...
Dynamically Pivot A Table?
Mar 30, 2012How do I dynamically pivot a table? On my example below, the STORE changes.
View 2 Replies View RelatedCreate 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
Creating A Table Dynamically
Jun 10, 2008Greetings 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.
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.
To Hide Dynamically A Table
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
Selecting 2 F/k's From Same Table?
Sep 13, 2005This is, I'm hoping, a simple thing that I just don't know how to do. But I've searched my books and read a lot of threads but so far, no luck in what I'm trying to do. Here's the basic layout of 2 tables from my d/b (MSDE):
tblCity
cityID (p/k)
stateID (f/k to tblState.stateID)
cityName (varchar 35)
tblStudent
studID (p/k)
cityID (f/k to tblCity.cityID)
hometownID (f/k to tblCity.cityID)
studFName (varchar 25)
studLName (varchar 35)
...
I *hope* that what I tried to do there is allowed. Basically what I'm trying to do is to tie the student to both his current City (by foreign key cityID) and his hometown city (also by foreign key cityID).
So, if New York is (127, 33, 'New York') and Birmingham is (1050, 1, 'Birmingham') (in tblCity), then in the student table (tblStudent), John Smith would be
Quote: 12150, 127, 1050, 'John', 'Smith', ...
How would I format my SQL statement to get both cityNames? I thought this would work
Code:
SELECT s.studID, c.cityID, c.hometownID,
s.studLName + ', ' + s.studFName AS studNameName,
c.cityName, c.cityName AS swapconNum
FROM tblStudent AS s INNER JOIN tblCity AS c
ON (s.cityID = c.cityID)
WHERE s.studID = 12150
Can I do this? If so, what am I doing wrong? Sorry, I'm kinda sorta new to SQL.
RudeDog
Dynamically Access A Column In A Table
May 21, 1999I'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
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.
Dynamically Check If A Table Exists
Apr 10, 2008Hi 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?
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.
Verify Dynamically Specified Table Exists
Jul 23, 2005I 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 RelatedHow To Dynamically Create Columns For A Table
Apr 29, 2008Hi,
how to dynamically create columns for a table
Changing The Dataset Of A Table Dynamically
May 1, 2007Hi all,
Is there a way to change the dataset being used by a table dynamically ?
Regards,
Neil
Selecting From One Table Inserting Into Another
Feb 18, 2007hi, i am sure this is very simple, but its just got me stuck! i have a table which i use for inserting products, now its not very exciting to see the cat_id so i wanted to put a dropdown box on there which would show the cat_description from tbl_cat and then put the tbl_cat.cat_id into tbl_products.cat_id
any help would be greatful
Jez
Selecting From One Table, Inserting Into Another
Jul 3, 2007hi there, i have a page where on my button click i need to select address, pcode from tbl_person where person_id = querystring and update or insert those values into tbl_del where order_ID = querystring 2 below is the code i am using, although im stuck on the sql stringSqlConnection objConnAddy = new SqlConnection(sConnectionStringCustInfo);using (objConnAddy)
{
objConnOID.Open();
// insert values into delivery table, second line updates price + VAT
string sqlAddy = "?????";
SqlCommand objCmdAddy = new SqlCommand(sqlAddy, objConnAddy);
objCmd1.ExecuteNonQuery();
i had a look on the net and here but i cant seem to find exactly what i want, any help would be great!
Cheers Jez
Selecting FieldName From The Table
Sep 23, 2007How can i just select filed name from the name without data. What query should we write foe that.
View 2 Replies View RelatedSelecting One Row And Inserting It Into Another Table One By One
Feb 14, 2008i ve a table named "Employee" in wh i ve fields "Emp_Id","Name" and "Email". i want to select each employee "Emp_Id" one by one from "Employee" table and insert it into another table named "Concerned_Department". like if i ve three rows in "Employee" table with "Emp_Id" 1,2 and 3 respectively,in this case i want to first select "Emp_Id"=1 then "Emp_id"=2 and lastly "Emp_id"=3 and insert them into "Concerned_Department"table.
after that the process starts again like inserting "Emp_Id"=1 in "Concerned_Department" and so on.
plz note that i want to insert "Emp_Id" one by one not all the fields together.
can u plz tell me the query for that wh i ve to write in SQL
Thanks and happy valentine day to all of u
regards
Ahmed Bilal Jan
Randomly Selecting From A Table.
May 3, 2004lets say that this select statement will give me 100 records..
"Select UserID from Users where location = 'FL'"
from these 100 records, I want to randomly get 5 records. whats the best way to go about doing this
Selecting Records In One Table But Not In Another 2
Dec 13, 2004Hi, my sql is not too hot so i hope someone can help me. I need to select all the records from one table that do not exist in 2 other tables. I know it sounds simple enough but for some reason i can not get it working. It may have something to do with the fact that the field i am searching on are datetime fields. Here is a shortened version of my code.
SELECT DateOfStats
FROM table1
WHERE (DateOfStats NOT IN
(SELECT dateofstats
FROM table2)) and (DateOfStats NOT IN
(SELECT dateofstats
FROM table3))
Thanks for the help in advance.
Selecting Only Those Records Which Are In Another Table
May 25, 2005i need to select records from table "A" if only the "PK" of "A" exists in table "B". I need to return a resultset not just a single record. The problem is table "B" is not a table in database instead a user supplied table which can be a datatable in memory.
View 2 Replies View RelatedSelecting Into A Generated Table Name. Can You Even Do This ?
Aug 22, 2001Hello,
We're trying to do the following...
Declare @Name Varchar(15)
Select @Name = 'TableName'
Select @Name = @Name + Rand(SomeSeedValue)
Select *
Into @Name
From Someothertable
This just gives us syntax errors, anyone else had any success with this ?