How To Write A Script To Create A Table (With Unfixed Field Names)?
Mar 28, 2006
Hello All.
You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar.
And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile.
My below script shows error. I have following records in my TempWeekFile
Week_No WeekCnt
10 1
11 2
12 3
13 4
How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you.
Hi,We have a database with some tables with (what I woulddenote as) 'referred field names'.Like this:DataTable1 with fields F1, F2, F3DataTable2 with fields F3, F4, F5DataTable3 with fields F1, F5, F2We also have a table with field namesFieldNameTable with fields FIELD, NAMEcontaining data like:FIELD NAME----------------F1 FieldName1F2 FieldName2F3 FieldName3F4 FieldName4F5 FieldName5Now, we need a way to query the data of these tables, butthe result of the query should show the 'referred field names'from the FieldNameTable.For example, querying DataTable3 should produce the outputFieldName1 FieldName5 FieldName2------------------------------------------... ... ...... ... ...Any idea how (and whether) this can be done with an SQL query?Thanks in advance for tips & tricks.Dirk Vdm
I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated. Randy
How canI get all field names in a table using sql query? Now I am not interested in the data in this table, what I am interested in is just the schema, i.e. all field names. Thanks.
Greetings!I am now doing one type of analysis every month, and wanted to creattable names in a more efficient way.Here is what happens now, everytime I do the analysis, I will create atable called something like customer_20050930, and then update thetable by using several update steps. Then next month I will create atable called customer_20051031. Does anyone know if there is a betterway to do it? like using a macro variable for the month-end date? Noweverytime I have to change the table name in every single update step,which would cause errors if I forget to change one of them. By using amacro, I would only need to change it once.Thanks!
I am trying to create a list of all the tables in one database and then list the size of each table. So for example I want to create a table with the table name and table size for one DB
E.g
Table1 1111KB Table2 123300MB Table3 120448KB
etc for all the tables in a particukar DB
I know there is a stored procedure to list the sizes: 'sp_spaceused' but not sure how to script all this together.
Hi all,My program is a central data processing application built in ASP.We have different companies that use different web pages on another webapplication (from different countries) to load some inventory data(merchandise - clothes).Here is my requirement: Load different types of data (in differentformats) into a common set of tables, to do this I have to firstfilter, do lookup's, use cross-reference tables on this data and thenload it into a couple of tables.Since data is so different everytime, I want to have one main storedprocedure in which I can build the table (I know the format of thisdata so I know what columns/types to create) into which I will feed thedata.After this I will create other stored procs/udf's that reference thistable (probably from the same main stored proc) 'cleanse' the databefore loading into the actual tables.How feasible is my approach? (if you can call it one :-) I do not wantto have seperate tables for each country, that will be too many and sothis plan...If not any other ideas will be really helpful.thanks much
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view ( Name: Product Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc Table 2: Is a lookup table that provides the names of columns Name: lookupTable Structure: tableName, ColumnName, columnValue Values: Product, Attribute1, Color Product, Attribute2, Size Product, Attribute3, Flavor Product, Attribute4, Shape
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)
Now i m working in a existing project. I have to do some updations. Inthat project database table contain a table(usergroups) field name as'Name'. When i am trying to insert a new record in (usergoups) tablelike as follows'insert into UserGroups(Name,modusr,moddt) values ('sam',131,'7/23/200610:02:13 PM')The response ll come as follows,Invalid column name 'FirstName'.Invalid column name 'LastName'.The usergroups table structure is as follows,UserGroupId int 4 (PK field)Name varchar 50modusr int 4moddt datetime 8Plz tell the correct insert query to add records for the abovetable....Thanks in advance
I'm want to store a time from a datetimepicker control(format = time) into a sql table. I'm using c# and windows forms with vs2008. Some example code would be nice. Thanks.
Hello experts, I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?
electric: id_electric indentity primary key, id_room varchar(4), number_first int, number_last int, Sum_Number int, money_electric money, status bit
Water: id_Water indentity primary key, id_room varchar(4), number_first int, number_last int, Sum_Number int, money_water money, status bit
Now what I want to do are statistics on how much money I got in a year or month. Here is my code to calculate incomes of year.
Select Year(day_register) as 'Year' , Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes' From lodgings_Contract a , electric b , Water c Where a.id_room = b.id_room And a.id_room = c.id_room And b.status = 1 And c.status = 1 Group by Year(day_register)
--Then somehow iterate each value in the field dbname to create a view something like Create View viewcreatedfromtable As Select * from foxfire.dbo.abcd Union All Select * from foxfire.dbo.abcde
[Code] ....
I tried this, but it is only printing the last result returned not the entire result set
Code: Declare @database varchar(max), @sql varchar(max) Declare c1 Cursor For Select Program from tocreateviewfrom Open c1
I'm trying to get a SQL 7 and 6.5 DB to interact, but while there is no problem in SQL7, I cannot create a table called "Public" or a field called "Primary"!! Does anyone know why this might be and if so where I might get a list of any other "invalid" names??
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.
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.
Is there anyway to determine what the resulting Field Names are going to be from a SQL Statement?
For example: SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD3, TABLE2.FIELD1 AS ANOTHERNAME FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.PK = TABLE2.FK
resulting field names: FIELD1 FIELD2 FIELD3 ANOTHERNAME
Seems easy enough splitting all values before "FROM" by comma and doing some manipulation to remove table names and anything before the word "AS". However, it gets more difficult when you have complex CASE statements embedded in you query that may also contain commas.
Just a shot in the dark because I don't know if anyone has already done something like this before.
Howdy all,I'm wishing to write a stored proc to return only the field names from a table. What I've tried gets the field names but also returns all of the data in each row. I only want the field names. Is this possible?Thanks!JP
This is a followup to my last post. If a field name is changed in the database, what is the easiest way to determine what stored procedures and triggers that reference that field are now broken?
hello my friends i want write Select without write filed name example mytable(id,name1,family) orginal selected is select id,name1 from mytable but i want write: select field[0],field[1] from my table that field[0] point to id and field[1] point to name1 thanx
I can't seem to find a sample code, either here or on the net - - so I'll go ahead and ask...
What I'm looking for is a sample of how to query a database, so that I can populate a listbox with the table names from a database - - and then, populate another listobx with the field names from the database, in order to build a user-driven sql statement builder....
either of the above (at least the field name part) - either a code sample, or a link, will be greatly appreciated) - -
USE MLS select sc.name,f.field#,fdesc,flong from sysobjects so join syscolumns sc on so.id = sc.id join fld f on f.field# = replace(sc.name,'_','') where so.name = 'dbo.tbl_MLS_Leads_Trans'
I am trying to get the description which is flong and I get the following error message:
Msg 208, Level 16, State 1, Line 2 Invalid object name 'fld'.
Hi,I am trying to create a stored procedures (SQL 7.0), to provide dataina crosstab format.(I'm using Crystal Reports 8.5, but the Crosstab capabilities areterrible, so I have to do as much as possible on the SQL side)I have a table [Occurrences] with the following fields:Year (int)Month (int)Occurs (int)Claims (int)I need a query to give me the following format:Acct_Month 2001 2002 2003Occurs Claims Occurs Claims Occurs ClaimsJanuary 120 180 132 196 110 140February 154 210 165 202 144 178March etc.......Catch! I need the Year field name to be the contents of the fieldYear in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2approach.I got the month name ok...Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+Convert(Char(4),Year))Is it possible to do this easely, without the use of cursors?Any help would be much appreciated.Luis Pinto
is there any "robust" way to find out the name of a field in the pipeline by it's Lineage ID programatically? There is a sample code out there (on one of the blogs) but it seams not to be reliable...
The usecase is easy... What is the field name in an error output of that column that causes the error? We don't want to have hardcoded LineageIDs in the error handling so I think it's the best idea to go with field names... However we only get that LineageID...