Loop Through All Tables And Columns
Oct 26, 2005Is there a way to change the collation against all the tables and columns within a database?
View 2 Replies
ADVERTISEMENT
SSIS Script Transformation: Loop Through Columns In A Row
Mar 17, 2008
HI,
How do I loop through all columns in a row using a script
transformation? For example if I want trim all columns.
If I want to trim one column this is a simple script:
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As
MyAddressInputBuffer)
Row.City = Trim(Row.City)
End Sub
End Class
But what if I want to do that for all columns? I don't want to name
them all like this:
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As
MyAddressInputBuffer)
Row.Column1 = Trim(Row.Column1)
Row.Column2 = Trim(Row.Column2)
Row.Column3 = Trim(Row.Column3)
...
...
Row.Column997 = Trim(Row.Column997)
Row.Column998 = Trim(Row.Column998)
Row.Column999 = Trim(Row.Column999)
End Sub
End Class
Is there a simple foreach column in Row.columns option?
-- Joost (Atos Origin)
Transact SQL :: How To Add Columns To Temporary Table In Loop
Aug 27, 2015How to add columns to temporary table in loop with sample code.
View 12 Replies View RelatedLoop Through A Recordset To Populate Columns In A Temp Table
Jul 23, 2005I want to take the contents from a table of appointments and insert theappointments for any given month into a temp table where all theappointments for each day are inserted into a single row with a columnfor each day of the month.Is there a simple way to do this?I have a recordset that looks like:SELECTa.Date,a.Client --contents: Joe, Frank, Fred, Pete, OscarFROMdbo.tblAppointments aWHEREa.date between ...(first and last day of the selected month)What I want to do is to create a temp table that has 31 columnsto hold appointments and insert into each column any appointments forthe date...CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2nvarchar(500), Day3 nvarchar(500), etc...)Then loop through the recordset above to insert into Day1, Day 2, Day3,etc. all the appointments for that day, with multiple appointmentsseparated by a comma.INSERT INTO#Appointments(Day1)SELECTa.ClientFROMdbo.tblAppointments aWHEREa.date = (...first day of the month)(LOOP to Day31)The results would look likeDay1 Day2 Day3 ...Row1 Joe, PeteFrank,FredMaybe there's an even better way to handle this sort of situation?Thanks,lq
View 11 Replies View RelatedIntegration Services :: For Each Loop To Import Files With Missing Columns
Aug 17, 2015I have to load on SS2012 hundeds of excel files produced by an application over the last five years, during time few columns have been added to the initial set.I created on SS2012 a table to match with the full set of columns and want to load all the files inside the table leaving the missing cells to NULL. I think SSIS can do the job but every trial failed do far.
View 4 Replies View RelatedLoop Throught Columns Of An Excel File Using Dtsx Package!
Dec 29, 2006Hello,
I'd need an help because I'm stucked!!
I have to import an Excel file into my DB.
The Excel file is made by 2 worksheets but I need only one and inside this worksheet I have to loop through the columns and for each column I define a Data Flow that trasform the data as necessary and then insert into the table.
I started with a "Foreach ADO.NET Schema Rowset Enumerator" with connection=excel file and the schema was set to "Columns" but the loop go also through the worksheet that I don't need..
after 4 hours of tries I'm lost...
Someone could give me an advice?
ThankX
Marina B.
How To Run A Loop For All Columns In Script Component Input Column Collection For Each Row
Jan 22, 2008Hi All,
I want to run a loop for all the input columns in the script component. My requirement is, I have nearly 50 columns in the input columns list. For each row and for each column I need to do some operation. How Can I run a loop for each column. Please note in the script component I need to get the column names in the middle for some operations. Please see below.
Process Each Input Row
For each column in Input column list
....
....
If column.Name Starts with "Test" then set NULL to the column value
....
....
End Loop
End Loop for each input row
Can anybody tell me how can I do that.
Regards
Venkat.
Joining Many Tables In Loop
Aug 9, 2006Hello,
I am very new to TSQL however I do have a little programming in my background. I am trying to run a query to select several datafields from about 45 tables within a database. I have written the below code to loop through all of the tables and extract the information however I need to find some way to join the results using PCTimeStamp as the key. Any help anyone can give me is greatly appreciated!
while (@Table_Number<18240) /*Loop to read data from all Turbine all Tables*/
BEGIN
if (@Table_Number=17990 or @Table_Number=18234) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
set @Table_Number=@Table_Number+1;
END;
set @Table_Insert = cast (@Table_Number as NVARCHAR); /*Converts Table Number to Format usable in String below*/
set @SQLString = N'SELECT Id, Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladecPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection1 FROM T_'+@Table_Insert+'_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_'+@Table_Insert+'_10MINDATA)';
set @Table_Number = @Table_Number+1; /*Increment @Table_Number to control Loop*/
if (@Table_Number <18006 or @Table_Number>18235) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
EXEC(@SQLString); /*Execute String to retrieve data from table*/
END;
END;
I tried to format that in this text box as best I could but I apologize for the result. In summary Table_Number is used as the key to loop through the sequentially numbered tables (with some numbers missing) and extract the required fields from the most recent entry (PCTimeStamp=max).
Other notes: the code as written outputs all of the required data with no error messages but the data column titles are displayed i.e:
id windspeed direction time
1 12 000 1200
id windspeed direction time
2 12 010 1200
...................
looking for
id windspeed direction time
1 12 000 1200
2 12 010 1200
Thanks again
Simple T-SQL Question (tables In Loop)
Nov 2, 2000Can anybody tell me, how to write a T-SQL loop,
that goes through all tables in a database to
realize some updates there?
Thanx a lot in advace
Gert
Select From 2 Tables Cause A Loop... What Am I Doing Wrong?
Apr 24, 2006I have two tables that I wish to return values for and populate a text file with the returned values. This part works well, however, I get strange values when I rung the Select statement against both tables.
For example, if I run this statement:
Select count(page_count), sum(Page_count) from Contracts
this will return the values I expect
If I were to add another table to this query then things go crazy. For instance, if I took the same query and added the Maps table to it as such:
Select count(page_count), sum(Page_count) from Contracts, Maps
I get crazy numbers that are many times more then the actual numbers
My goal is to get the page count, number of records for Contracts and number of records from Maps all in one query
Integration Services :: Using Foreach Loop Container To Elaborate A Table With Two Columns - SSIS 2012
May 6, 2015In order to update an Oracle table target from a SQL Server table source I need to use a Foreach Loop Container, so I can loop on the rows of the SQL Server table source. This source table has two columns: the old identifier to update and the new identifier to apply. I must use the value of the old identifier to filter the Oracle rows to update, while the new identifier is the new value to assign to the filtered old identifier.
I already know how to use the Foreach Loop Container when it is necessary to loop on an unique column of a table/view (using an object variable, using a Foreach ADO enumerator, etc.), but I need to loop on two columns.
Transforming Structurally Identical Tables In A Loop
Dec 12, 2007I'd like to extend a package functionality.
I created it drag/drop way with hard-coded table names.
Now for the same source and destination connections I'd like somehow in a loop transform 20 source tables of the same structure to 20 destination tables of the same structure providing table names in a loop.
I also have in the package preparation SQL tasks such as dropping destination table if exists, and then re-creation , so it needs to consume a table name as parameter from my loop.
Is it doable ?
Multiple Tables - Loop Through Query Switching Table Name?
Mar 8, 2012I would like to run the same query on multiple tables. So say I have a list of tables
@tableList = a|b|c|d
And then I have my query looping through the tables
for (@table in tableList)
{
update from @table
set = ''
}
Is there a simple way to do this in an mssql query, if so how do I get to loop through the query switching the table name?
SQL Server 2012 :: How To Loop Unmatched Rows Data From Two Tables
Jan 12, 2015We are having folder table and team table as like below structure.
Folderlist (F)Table: (
==============
id ,folder_name, parent_id
1, c, 101
2,b,202
3,c,203
Teamlist table (T)
============
team_id, Team_name, Parent_folderid
101 , mobile,101
202 ,Tab,200
200, Phone,200
203,apple,205
205,nokia,208
208,samsung,208
If F.parent_id(101)=T.team_id(101) and T.team_id(101)=T.parent_folder_id (101)
then output should come as 'Mobile/c' (this is for f.parent_id=101)
If F.Parent_id=T.team_id and T.team_id!=T.parent_folder_id
then parent_folder_id have to start search on team_id column where it got match and pick the Team_name from that corresponding id
Ex: F.parent_id=202 is matching with T.Team_id (202) but this T.team_id(202) is not matching with T.parent_folderid(200) , so this T.parent_folderid (200) have to search on T.id (200) ,if now T.id(200) is matching with T.Parent_folder_id(200) then it have to give the names from the starting hirache
like phone/tab/b (this is for F.parent_id=202)
SQL 2012 :: Calculated Columns Conditional On Calculated Columns Multiple Tables
Apr 20, 2014I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
To Many Columns Or To Many Tables?
Feb 6, 2007my supervisor is trying to help move our existing database in IBM U2 into SQL Sever he has a file/table that as 12001 columns, 1 column is the key300 columns are different field errors that can be checked true or falseand for each of those 300 columns there are three extra columns where extra information can be stored is there a size limitation when it comes to columns in a SQL table?what is an efficient way of doing this?create one giant table with 12000 columns?create 300 tables, each table associated with a error association? which will then need to be joined?any suggestions? comments? tips?
View 3 Replies View RelatedHow To Add Tables Name With 3 Columns
Aug 6, 2015When I run below query I get 3 columns, but when I try to add table name ind.object_name (object_id) it's giving me an error "Ambiguous column name 'object_id".How do I add tables name with 3 columns?
SELECT ind.name, ic.index_id , ind.type_desc from sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
Name Of Tables And Columns
Aug 2, 2007Hi,
i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case?
Thx
Frank
SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?
May 28, 2010difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies View RelatedHow To Index Through A Tables Columns
May 8, 2005I am trying to index through the columns of MyTable so I can do the same work on all columns. I know how to get the column names from MyTable but when I use @MyColName in the SELECT statement to get MyTable Column 0 Row values I get a table with the column name in each row cell. I can't get the syntax correct to return the value in each cell for that column.
This is a extremely simplified example !!!!!!DECLARE @MyColName nvarchar(30)
--Get the MyTable Column 0 NameSELECT @MyColName = Col_Name(Object_ID('MyTable'), 0)
--Display the MyTable Column 0 Row valuesSELECT @MyColName FROM MyTable --This is the syntax I can not get correct
Can anyone help ?
Thanks
Cant Add New Columns To Data Tables
Sep 19, 2005Hi All,I am having a problem for which i dont find any reasons ..I hope to get a solution from here.I have 2 tables ..1 with around 150 columns and the other with around 80 columns.I have view based on these tables.The problem i m facing from yesterday is that i cant add /delete/change on these tables.If i make a change on these tables and hit the save button the sql server enterprise just hangs..i also tried to add the columns through the query but no results.I cant even drop this view.Please any help on this??Thanks
View 1 Replies View RelatedSearch Tables And Get Columns
Nov 10, 2005I drew short straw on some reports work and am spending an eternity searching through catalogs looking for tables and columns. I'm a little new to SQL. Is there a way to search a catalog for table column names?
View 2 Replies View RelatedListing All Tables And Their Columns?
Jun 14, 2006Hey all. I apologize, but I'm a developer, not a DBA. I need to run a query that will list each table in a DB as well as the columns i nthose tables.
I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.
Any help would be greatly appreciated. Thanks!
Search All Columns Of All Tables
Feb 5, 2008How to search all columns of all tables for a keyword?:o
that in MS sql
thanks in advance..
Sum Columns From Multiple Tables
May 5, 2012I have several tables that I need to sum up a colomn and display the results. I have been struggling with this for some time. Here are my Tables and their columns:
Ingredients_tbl
IngredientID
Description
Inventory_tbl
IngredientID
AmountRemaining
Renconciled
Active
Order_Details_tbl
IngredientID
OrderAmount
Status
Order_Details_Details_tbl
IngredientID
OrderAmount
Status
I want to display all the ingredients from the ingredients table, sum the amountremaining of the items that are reconciled = 'false' from the inventory_tbl, sum the orderamount from both the order_details_tbl and Order_details_details_tbl for each ingredient based on status. Here is some sample data and results I am looking for:
Ingredients_tbl
IngredientID -- Description
1 -- Corn
2 -- Beer
3 -- SBM
4 -- Wine
Inventory_tbl
IngredientID -- AmountRemaining -- Reconciled
1 -- 500 -- False
1 -- 500 -- False
1 -- 100 -- True
1 -- 500 -- False
2 -- 1000 -- False
2 -- 1000 -- False
4 -- 500 -- False
4 -- 500 -- False
Order_Details_tbl
IngredientID -- OrderAmount -- Status
1 -- 100 -- ORDERED
1 -- 100 -- MIXED
1 -- 100 -- DELIVERED
2 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
4 -- 100 -- ORDERED
4 -- 100 -- DELIVERED
Order_Details_Details_tbl
IngredientID -- OrderAmount -- Status
1 -- 100 -- ORDERED
1 -- 100 -- DELIVERED
1 -- 100 -- DELIVERED
2 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
4 -- 100 -- ORDERED
I would like the results to be like this:
IngredientID
Description
Inventory = Sum of AmountRemaining of the records in Inventory_tbl where Renconciled = 'false'
Production1 = sum of OrderAmount of the records in the Order_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED'
Production2 = sum of OrderAmount of the records in the Order_Details_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED'
IngredientID -- Description -- Inventory -- Production1 -- Production2
1 -- Corn -- 1500 -- 200 -- 100
2 -- Beer -- 2000 -- 100 -- 100
3 -- SBM -- 0 -- 300 -- 200
4 -- Wine -- 1000 -- 100 -- 100
I thought this was going to be as easy as a few simple joins and aggregate sum on the colomns like this:
SELECT Ingredients_tbl.IngredientID, Ingredients_tbl.Description,
Ingredients_tbl.LowInventory, SUM(Inventory_tbl.AmountRemaining) AS Inventory, SUM(Order_Details_tbl.OrderAmount) AS Production1, SUM(Order_Details_Details_tbl.OrderAmount) AS Production2
FROM Ingredients_tbl
LEFT OUTER JOIN Inventory_tbl ON Inventory_tbl.IngredientID = Ingredients_tbl.IngredientID AND Inventory_tbl.Reconciled = 'False'
[Code] .....
But this obviously has its issues with incorrect returned sum values.
Combine 2 Columns From 2 Tables
May 23, 2008Hi,
I have 2 tables called Table A, Table B,
In Table A i am having Data1, Data2 like 2 datas in Column 1
In Table B i am having Data2, Data3 Like 2 datas in Column 1
Now want a output like
Data1,
Data2,
Data3
Please help me to get this....
Thank you,
Senthil
How To Compare 3 Columns From 2 Tables
Jul 26, 2013I have requirement like this
1 st column 'A1Ctest' 2 nd column 'diagnoising heart disease' and my 3 rd column is combination of both columns
'A1Ctest for diagnoising heart disease'.Here i need to comapre 'A1Ctest' from 1st column and 'diagnoising heart disease' from 2 nd column
Finding Two Columns In All Tables
Oct 29, 2013I have several tables that have the POLICY_NUMBER and POLICY_DATE_TIME in them.. All the tables with these two columns should have a POLICY_ NUMBER and a corresponding POLICY_DATE_TIME.I would like to find all tables that have POLICY_NUMBER = 123456 but do not have the corresponding POLICY_DATE_TIME..
View 1 Replies View RelatedGet Value From Columns Using List Of Tables
Feb 5, 2014how do I get value from column using list of tables?
For example, I have list:
schema_name, table_name, column_name
How do I get:
schema_name, table_name, column_name, column_value
Combining Columns From 2 Tables
Aug 7, 2014I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.
I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B
SELECT
a.DATE,
a.STATE,
b.CITY
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = b.ID
I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.
Get Common Columns Name Between Tables
Aug 8, 2006Hi,
Do anyone know or have a suggestion how to get commun columns name between several table in a SQL server database
For example I have table_1(name, age, school) table_2(name, address, city) and table_3(name, department, company)
name is the common column name (I don't care about the data) in this case. Is there a way to do it simple and easy ?
THANK YOU
Getting The Matching Columns From Two Tables
Mar 1, 2006Hi all..I have two tables such as cisco and ciscocom. and i wan to compare eachrow of ciscocom with cisco having same column values. i wan to get thecount of matching columns for each row in cisco...eg:Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco hascolumns:fw,ports,sec,des,tput etc. i wan the number of matching columfor each row in ciscocom. please provide me with the procedure....Waiting for your response....
View 2 Replies View RelatedTables Have Too Many Nullable Columns
Jul 20, 2005Hi,I've enherited a big mess, a SQL Server 2000 database withapproximately 50 user tables and 65+ GB data, no explicitrelationships among entities (RI constraints whatsover), attempt tocreate an ERD would more than likely kill the relatively complexedapp, the owner would want to drop out of window, so,I don't intend to do that, you get the picture, sorry no DDLs thistime, and many tables have many nullable columns, joins slows down thesystem quite a bit, what's my best bet to improve its performance?change most of these nullable columns into non-nullable with defaultvalue of something like ' ', any thoughts along this line would beappreciated.
View 6 Replies View Related