Can someone please help I'm writting the following query.
SELECT
(SELECT c.column_name FROM information_schema.tables T
JOIN information_schema.columns C
ON t.table_name = c.table_name
WHERE t.table_type = 'base table' and t.table_name like 'L_%' )
INTO #TempTable FROM TableA A LEFT JOIN [Server-Name].DB_Name.dbo.TableB B ON A.ID = B.ID
I'm trying to put commas between column names. How do I go about doing that?
Dear All,
We are using UnPivot task to convert the columns into rows using the Excel File as source. But the Excel file column names are changing frequenly sometimes its having only 4 columns sometimes its 10 columns.
Everytime we are checking and unchecking the column list in Unpivot Task.
can anybody help us to solve this issue that Unpivot task should take the column name dynamically.
Thanks,
Syed
I got a table which gets populated by stor proc where we pivot the Sum(Balance of mortgage) by YYYYMM for the whole duration of the loan term.
I have a requirement to rename the column header where the previous month end balance period be renamed to P0.
if we run the report today, then the balance as at 31/09 should show under column P0 which now shows under 201509 and then P0 keeps shifting with each month run.
How do I dynamically rename the column headers.
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
Hi,I have a C# web app that searches my database table using thefollowing search parametersSearch string, criteria (< =) and the field you want to perform yoursearch on. My understanding is that stored procedure is the way to go.What's the best way of doing this using stored procedures. Can Idefine a placeholder for the field name?Ex.SELECT field1, field2... FROM Table WHERE field1='value1' where field1and value1 are both sent from code.If it's not possible then what is the best way to approach thisproblem? I see so many searches like that on the internet. I can onlydo them with inline SQL and not stored procedure.Thank youMaz.
View 1 Replies View RelatedHello,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 RelatedHi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
 I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.
View 6 Replies View RelatedHow to change column size dynamically depends on content of the column.
View 2 Replies View RelatedI 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]....
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
HI chaps
my scenario is that, i have a table in which i have column such as col01,col02,col03....col31
i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ('col??') and i have to do integer increment in last two character (???01...???31). Is it possible that I can use variable for column name?, if yes how? or is there any other way to achieve this task ?
waiting for your reply
regards
I want to choose a column dynamically depending on a session variable.
In other words, each customer has a different price level. so I need to choose the column that corresponds to thier price level in thier customer record
Any help is appreciated.
below is my futile attempt
SelectCommand="SELECT cove.CATALOG.ITEM_NO,cove.catalog."& <%#= session("pl") %> & ", cove.CATALOG.DESCRIPTION, cove.CATALOG.DESCRIPTION2, cove.CATALOG.DESCRIPTION3, cove.PRODLINE.DESCRIPTION AS Expr1, cove.CATALOG.GRP, cove.COMPANY.PRL, cove.COMPANY.COMPANY FROM cove.CATALOG INNER JOIN cove.COMPANY ON cove.CATALOG.WEB_STATUS <>'[I]' cove.COMPANY.I LEFT OUTER JOIN cove.PRODLINE ON cove.CATALOG.PRODLINE = cove.PRODLINE.CODE WHERE (cove.COMPANY.COMPANY = @company) AND (cove.CATALOG.GRP <> '[ACC]')">
<SelectParameters>
Hey all. I'm trying to create a stored proc that will update a variable column, depending on the parameter I pass it. Here's the stored proc:
CREATE PROCEDURE VoteStoredProc
(
@PlayerID int,
@VoteID int,
@BootNumber nvarchar(50)
)
AS
DECLARE @SQLStatement varchar(255)
SET @SQLStatement = 'UPDATE myTable SET '+ @BootNumber+'='+ @VoteID + ' WHERE (PlayerID = '+ @PlayerID +')'
EXEC(@SQLStatement)
GO
I get the following error:
Syntax error converting the nvarchar value 'UPDATE myTable SET Boot3=' to a column of data type int
The update statement is good, because if I use the stored proc below (hard-coded the column), it works fine.
CREATE PROCEDURE VoteStoredProc
(
@PlayerID int,
@VoteID int,
@BootNumber nvarchar(50)
)
AS
UPDATE
myTable
SET
Boot3 = @VoteID
WHERE
PlayerID = @PlayerID
GO
Is there a way to dynamically choose a column/field to select from? Or is my syntax incorrect..?
Thanks!
am passing column name as parameter, so i wanted to select column name dynamically.
eg: select columname1,columname2 where @param = columnname1,columnname2.
when @param =columnname1,columnname2. select should be of respective column. if @param = columname2,columnname8, then select columname2,columnname8 from table1 where coulmnname2='1' and columnname8='2'.
love all
Hi All,
I have a series of tables need to import to server. When creating the target tables, I want to change the columns name as well, for example:
Source table column Target table column
Name FN_Name
Age FN_Age
The problem is I suppose I don't know the columns name in source table, I want to the tasks scan the source table and make the change programmlly.
Which tasks or approaches can be used to implement this?
Thanks
Micror
I'm looping through Me.ComponentMetaData.InputCollection(0).InputColumnCollection... and get the column name of the inputcollection in a script component. Now, what i want to do is set a value to all of the input columns using a loop. If the type is an int, set it to 1, if string then set it to "1". Anyway to approach this problem?
Thanks!
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
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.
Hi,In my stored procedure I'm doing a SELECT onINFORMATION_SCHEMA.TABLE_CONSTRAINTS. However there is no unique id onthis table, so I was wondering if it was possible to add it dynamicallyin my SELECT, so that I would assign a unique id to each recordreturned by my SELECT?Thanks for your support.
View 3 Replies View RelatedWe have a query in which we have data in fields called TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg etc.
We have a different table in which we define that vale of TS1, TS2, as an example TS1 might equal RED, TS2 might equal BLUE.
We have written a query that puts TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg in a temp table #TEMPA
and we also put the values of RED and BLUE in another temp table #TEMPB
now we want to select * from #TEMPA but rename the headings TS1Min to display RED-TS1Min, TS1Max as RED-TS1Max, TS1Avg as RED-TS1Avg etc...
any ideas on how to do this
Hello,
Im trying to get the column names from a database and display them in textboxes. someone has already helped me by tellnig me that i need to use the FillSchema command. Which works just fine and I can see only the colum names in a datagrid when i bind it to that.
The problem is that I do not know how to extract the name of a column and put it in to a textbox ?
does anybody know how I can fo this ?
Thanks a million
Rob
Hi,How do I display the column names from my Sql server table?In asp3 the recordset allowed thisforeach key in rs ColumnName = key.name ColumnValue = key.valuenextHow do I do this in .Net?I want to use a DataReader so I can read through each record and only display the ones I want.TABLE_ONEColumn_OneColumn_TwoColumn_Threethanks,
View 1 Replies View RelatedHi, i need a query to have the columns names !!
If you have another solution, let me know !!
superj !!!
Hi
When I run this query..
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='TableTest'
I get all the column names, but the result is not in the same order as when I check the design for the table, why is that?
At my work, we are upgrading a number of MS Access 2000 databases to SQL Server 2000. Many of the columns have names that do not follow the rules for Identifiers. For example there are columns with a numeric names 1,2,3,4,5,6,7,8,9. There are also columns with a hyphen in the name and columns that begin with a number and not an underscore or an alpha character. Plus there are columns with names like first, last, position, etc. (There was also columns with reserved words used as the names. I have changed those column names in the databases that I have already converted) Will leaving these names alone create a problem? The database serves as the backend to ASP pages.
Miranda
Hello,
I have to write a trigger to truncate at a scheduled time.Truncate statement is not allowed in the trigger.What should I do.I mean how to go for it.
Thanks for any help.
JAY
Hello,
I have to write a trigger to truncate a table at a scheduled time.Truncate statement is not allowed in the trigger.What should I do.I mean how to go for it.How can I test my trigger if it's working fine or not.
Thanks for any help.
JAY
I have table name employee with attributes like eno,ename dno,salary. My problem is that i need select complete information of the emloyee's when employee getting same salary in same depatment.
Eg: I have the table like this
eno ename dno salary
1 Noor D10 10000.50
2 Ahmed D20 20000.75
3 jaya D30 15000.65
4 hema D10 10000.50
5 rupa D20 20000.75
6 prabha D40 20000.75
7 lalitha D40 12000.75
8 vetri D40 12000.75
and i need the output as fallows
eno ename dno salary
1 Noor D10 10000.50
4 hema D10 10000.50
7 lalitha D40 12000.75
8 vetri D40 12000.75
2 Ahmed D20 20000.75
5 rupa D20 20000.75
so I written the query like this
SELECT * FROM employee
where dno in(SELECT dno FROM employee
group by dno,salary
having count(dno)>1) and
salary in(SELECT salary FROM sub
group by dno,salary
having count(salary)>1)
but i got the output as fallows
eno ename dno salary
1 Noor D10 10000.50
4 hema D10 10000.50
7 lalitha D40 12000.75
8 vetri D40 12000.75
2 Ahmed D20 20000.75
5 rupa D20 20000.75
6 prabha D40 20000.75
so plz somebody help me to get the output what i need........
Noor Ahmed
Keysoft solution
Hallo
We got a €œSql Server€? and an €œAS400€? System. I can Read the information from the AS400 using a DataReader- Source my problem ist that I can not manage to write in the €œAS400€?. Using a DataReader- Destination I am not able to specify the destination table.
Does any one know what I am missing to Configure?
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.
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
View 2 Replies View RelatedHi all,
By using below query i can get no of tables having the give column name in a particular database,
SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno')
but i want to know the table names too?
any one please suggest me how to find table names too......
In other words i know the particular column name and right now i want to know the table names in which this column name exists.