SP To Add Fields To Table

Feb 14, 2006

I want to let a user a fields and select the data type and size from a short list. For example: nvarchar(?), Money, Int, or Bit. These are most likely the only choices for now. There will be a web page that lets them enter a field name and select a data type.

Can someone suggest how the sproc might look? Here is what I have so far.

CREATE PROCEDURE [dbo].AddCustomField AS
ALTER TABLE MyDataSQL.dbo.tblCustomFields ADD @FieldName @FieldDataType NULL
GO

Thank you,

View 1 Replies


ADVERTISEMENT

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View 8 Replies View Related

Using LIKE To Find Any Of Fields In One Table In Fields Of Another

Jul 31, 2013

I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).

Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.

View 5 Replies View Related

Finding Rows In One Table But Not In Another Table Based On 2 Fields

Apr 23, 2008



I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.

View 7 Replies View Related

Creating Table Fields Dynamically From Another Table Row

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

I Want To Update My Table But It Has 30 Fields So How Can I Modify My Table I Have To

May 29, 2008

I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it


I need that query for alter table

in it


reply me soon

View 14 Replies View Related

HT Insert Table A Record Fields Into Table B

Jun 13, 2008

All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.

How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)

Thanks!

-Kurt

View 1 Replies View Related

How Do I Copy A Fields From Another Table Into A New Table?

Oct 23, 2007



I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.


Table Fields

Tbl_Date_Dimension [Date_Dimension_Year], [Date_Dimension_Period], [Date_Dimension_Fiscal_Week]
Tbl_Report_Level Report_Level_Id
Tbl_Customer Customer_Code
[Sales Fact] [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id


The new table is called Tbl_Sales_Growth
Here's the information on the new table:






Code Block
[Date_Dimension_Year] [int] NOT NULL,
[Date_Dimension_Period] [int] NOT NULL,
[Date_Dimension_Fiscal_Week] [int] NULL,
[Report_Level_Id] [int] NOT NULL,
[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,
[Quantity] [decimal](18, 0) NOT NULL,
[Gross turnover] [decimal](18, 0) NOT NULL
) ON [PRIMARY]




View 2 Replies View Related

Access To Fields In A Table

Jun 25, 2006

I am trying to access the fields in a table using Visual Basic in Web Developer .NET

View 1 Replies View Related

Dynamic Table Name And Fields

Jan 13, 2007

I need to get the field values of a table (name will be dynamic).Then assign those values to properties in a class.Let's say I will get the table name dynamically.dim tblName as string = "tablea"The 2 tables can each have 25 fields or so.I need a way to select the amt and email field values from tblName.  Without saying "select job_amt, job_email from ...Is there someway to get the values based on the column name.  So if the column name has amt and email, then give me those values.Maybe loop through the datatable - then for each column -- if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 thenthen drop  that column from the datatable.ex of table structure<u>tablea</u>job_idjob_amtjob_email<u>tableb</u>dance_idamt_dancedance_email

View 3 Replies View Related

Number Of Fields In A Table?

Jan 4, 2004

My users login from the tbl_users table.

I would like to store all information on them in this table but that amounts to quite a lot of fields (about 50) and some of these will store a lot of HTML text in them.

Is it best to split the table up for performance reasons or will it make little difference?

Thanks,
JB

View 4 Replies View Related

Table Fields Name Query

Aug 9, 2004

Hello all,

How can i write a sql statement that can give me the name of the fileds in a specified table.

Eg: if i have a table name "table1" , which has the fields "field1", "field2",........ How can i get these fields names using sql query.




any help.

View 25 Replies View Related

Number Of Fields In A Table

Mar 6, 2000

I'm new to SQL Server 7. Is there a limit to the number of fields that a table can have?

View 1 Replies View Related

Create Table And Fields

Jan 13, 2007

I have maked this little code but dont know the way to insertt new table and fileds in database DATABASE01...


Private Sub Command1_Click()

Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.ConnectionString = "driver={SQL Server};server=DVD377-14D9E48CSQLEXPRESS;uid=;pwd=;database=master"
objConn.Open
objConn.Execute "CREATE DATABASE DATABASE01"

''''''''''''''''''
'"CREATE table Mytable"
''''''''''''''''''

''''''''''''''''''
'"CREATE filed1" in text format
'"CREATE filed2" in text format
'"CREATE filed3" in text format
'"CREATE filed4" in date format(DD/MM/YYYY)

''''''''''''''''''

objConn.Close

End Sub

View 1 Replies View Related

Adding Fields To A Table

Oct 17, 2007

I am new to SQL Server 2005 and I am trying to add two fields to an existing table. The table has 15 Million records in it and the save is not completing. How do I add the new fields?

View 14 Replies View Related

Comparing Name Fields In Same Table

Nov 29, 2014

I have two name columns in my table, NAME1 & NAME2 that I want to compare to see if they match. Only problem is that the order of the first, last, middle name can be either same or different between the two fields.

For example
NAME1 = JAY JOHN SMITH
NAME2 = JOHN SMITH JAY or SMITH JOHN JAY

Is there a way to somehow reorder these fields and then compare using SQL?

View 2 Replies View Related

How To Relate Two Fields Of The Same Table

Apr 18, 2007

Hi,
I am new to sql and is working with sql server managment 2005 +c# 2005.

My application needs to create a blockdiagram sort of thing say
if in my database i got a table 'Addition' with 'a', 'b', 'c',and the primary key addition_id, and c is related to a and b as c = a+ b.

there is stored procedure name usp_addition which contains this relation. Each time any insert or update is done this sp is executed and all the values are updated for accordingly.
My problem starts in the front end where i need to draw the graphical representation of table addition.

In this graphical representation, I need to draw the labels a, b, c and the arrows from a and b which will connect to c, showing that c has a, b as inputs.

I got the label using dataset and datacolumns but hte problem is how to create the arrows the name of labels (i.e my column names from which the arrow should start and end)

How does I get the information that c as two inputs a, b. I dont need the values since i just want to view the columns in table and which column is input to another column.


Since I need to do this dynamically because my tablename, and the number and name of column would differ does any body knows how to do this.


Priyadarshini

View 1 Replies View Related

Copying Fields In The Same Table

May 5, 2007

I'm kind of new to SQL and would appreciate some help with this. I have a table named Company. In this table there are fields called CompanyID, CompanyName, Address, and Year. (There are other fields too, but they are not relevant for this post.) There are multiple companies in the table and each company has a unique ID number. However, each company can have up to three records in the table. (One for the year 2005, one for the year 2006, and one for the year 2007.) Currently, only a company's 2007 record has data for the address. It's 2005 and 2006 records have addresses that are blank. I want to be able to update the 2005 and 2006 records with address from the 2007 record. Obviously, a very simple update won't work because there are multiple companies and multiple records that need updating. Can anyone fill me in on the SQL statement(s) I would need to make this happen? I would be extremely appreciative.

View 5 Replies View Related

How Can We Have Otional Fields In A Table?

Jan 29, 2008

Hello to all,

i want to make a table in order to keep the data of a hard disk.
e.g. create table disk{
capacity integer,
name varchar(50),
ports integer,
buffer char(1));

For example i want this hard disk to have these fields and i would like from the user to type me only the capacity because it is going to be a required field and the other fields(name,ports,buffer) to be optional!

How do i declare optional fields in a create table command?
and then how do i declare/define these fields in insert too?

Thanks, in advance!

View 3 Replies View Related

To Compare Fields Between Two Table

Jul 23, 2005

Hello,I would like to create a stored procedure that would compare the fieldsof two tables and their types. If they are different the user iswarned.How can I do that ?thx

View 3 Replies View Related

Keep Fields Together In Table Control

Feb 2, 2007

I have a table that display large amounts of text in some fields. The table only has two columns (Field Name and Field Data). If the Field data is too large to fit on the same page as the field above it, it pushes to the next page, and then starts printing at the top of that page (the next page down).

I tried setting the "Keep Together" property of the table = True, but this was of no use.

Has anyone found a way to work around this, and if so could you let me know what you had to do. It may just be a SQL Server default setting that cannot be changed. I just want to research all possibilities before reporting back to the users.

Thank you,

T.J.

View 6 Replies View Related

Is It Reasonable That A Table Has 240 Fields

Aug 16, 2006

Hi,

I have 240 sensors to sample one time per second,then store the data in sql server.

Is it reasonable that the table has 240 fields?If do,it will generate about 70,000 records every day.One month records will be 2,100,000.

If I divide the 240 sampling data into 10 records,every record has 24 fields.Then every day 700,000 records will generate.One month the record number is 21,000,000.It is terrible.

Which method is better,or who can give me another better method or some suggestions.

I need your help.Thank a lot.

View 9 Replies View Related

Number Of Fields In A Table

Mar 19, 2007

I am working on a project which requires having a form that must be filled by the user. The number of the fields on this form is arround 150. Is it a good idea to have a database table with 150 fields (columns)? If its not what would be a better approach for this case? Handling this many fields on a windows form is another issue but first I would like to know about how to deal with the data storage.

Any help is appreciated.

Thanks

View 9 Replies View Related

How To Put Only Changed Fields Into A New Table

Mar 6, 2008

Ha all again.
I have this issue:
I have 2 tables:
t1 (id,field1,field2,field3)
and t2 (id,field_name,field_value)

is possible with trigger to insert only changed fields on t1 to t2 like

(changing field1 on t1 with id 21 cause a new row in t2 with fields id=21,field_name="field1", field_value=field1.value)

i hope you understand what i look for.

Is this trigger solution ?

CREATE TRIGGER MAP_CHANGESON t1AFTER INSERT,UPDATEBEGIN
DECLARE @ID SOMEDATA;DECLARE @FIELD1 SOMEDATA;DECLARE @FIELD2 SOMEDATA;DECLARE @FIELD3 SOMEDATA;SET @ID=(SELECT ID FROM INSERTED)SET @FIELD1=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD1<>T2.FIELD1)SET @FIELD2=(SELECT FIELD2 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD2<>T2.FIELD2)SET @FIELD3=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD3<>T2.FIELD3)IF @FIELD1<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD1')IF @FIELD2<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD2','@FIELD2')IF @FIELD3<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD3')RETURNEND

View 7 Replies View Related

Two Datasets Fields In The Same Table

Feb 9, 2007

Hi

I'm basically new using reporting services, I hope someone can help me

I'm using two datasets in the same table this is because I that was the only way to obtain the data that I need, my problem is that when I try to use the folowing statment =Fields!Total.Value/Fields!Total2.Value,"seconddataset" I got an error because the scope, the expression edtior give me the Sum(Fields!Total2.Value,"seconddataset") wich it works but that is not what I want, I need to divide by the single value not the total Sum

the table is using the firsdataset but I need to use some fields from the seconddataset

I hope someone can help me

thanks in advance

View 3 Replies View Related

Querying The Size Of Table Fields?

Jun 5, 2008

Hello I am writing a stored procedure and instead of "hard coding" the size of the procedure parameters I would like to set them to the size of fields in my database.
 for example instead of declaring a parameter like this:
@ProjectDescription varchar(500)
 
I would like to do something like this:
 
@ProjectDescription varchar(Select size of "ProjectDescription" From Projects);
 
Thanks for the help! 

View 4 Replies View Related

Passwords Fields As Binary In Table

Oct 5, 2001

Has anyone worked with storing alphanumeric passwords as binary fields in databases? I have worked up the following command to convert an NVARCHAR field to a BINARY type and it seems to work fine.

UPDATE USERS SET USERPASS=CAST(CAST('TEST' AS NCHAR(5)) AS BINARY) WHERE USERID='USER1

But, I can just as easily convert it back to NVARCHAR and obtain the password with this command:

SELECT CAST(CAST(USERPASS AS NCHAR(20)) AS NVARCHAR(20)) FROM USERS WHERE USERID='USER1'

I guess my question is how can I encrypt the password to make it more difficult to convert back to NVARCHAR?

View 1 Replies View Related

Search Query Among All Fields In A Table

Jul 14, 2005

I apologize for the newbie sort of question, but I could not find an answer in an SQL book nor via Google.

I wish to search for a text string in ALL fields of a table. This will be used to provide a simple search box in a web application.

So far, the only method I've found to accomplish this is follows:


SELECT *
FROM Inventory
WHERE SerialNumber LIKE '%searchstring%' OR UserName LIKE '%searchstring%' OR Location LIKE '%searchstring%' ... etc


My goal is to accomplish something like the following. This, of course, does not execute properly since * can only be used following SELECT, but you can get an idea of the target behavior:


SELECT *
FROM Inventory
WHERE * LIKE '%searchstring%'


I'm using MSDE with Visual Basic .Net. Any suggestions on how to accomplish this?

Thanks for all help,
Kieran

View 1 Replies View Related

Returning All Fields From Any Table Name Passed .

Nov 30, 1998

I am trying to created a stored procedure/query to return all fields from any of my 43 look-up tables in my database. I have all of my look-up table names listed in a VB grid. I want the user to select a particular table, click edit (which pass's the table name) and have one stored procdure return all of the field in the table and populate them in a second VB form with the fields listed in a new grid.


Can one pass the "SQL string" to excute/ create a temp table.... A regular SP requires a specife table name to query from. Is there a way to do this without creating a temp table

Thanks for the help
EM.

View 2 Replies View Related

Initialize All Fields When Table Is Created

Sep 24, 2006

Is there a way to give fields or rows default data when the table is created?

View 1 Replies View Related

Index On Used Table Common Fields

Jan 6, 2012

I would like to know if SQL Server creates automatic index on the used tables' common fields.

For example:

Code:
CREATE VIEW [dbo].[ORMAN]
AS
SELECT dbo.PARSEL.OBJECTID, dbo.PARSEL.ADAPARSEL, dbo.PARSEL.ORMANADI, dbo.PARSEL.MULKIYET, dbo.PARSEL.ALAN, dbo.PARSEL.YERKOD,
dbo.PARSEL.UYG_KAN, dbo.PARSEL.KomNo, dbo.PARSEL.KadKan, dbo.PARSEL.Mescere, dbo.PARSEL.ACIKLAMA, dbo.PARSEL.URETIM_YONTEMI,

[Code] .....

On the above view example, if table PARSEL has 1 million records do I have to create an index for PARSELDURUM or when I create a view does SQL server create automatic virtual index?

View 6 Replies View Related

Display 2 Fields Using SalesOrderDetail Table?

Aug 4, 2014

I'm trying to Display 2 fields using the SalesOrderDetail table.

CarrierTrackingNumber

Count the number of CarrierTrackingNumber and give alias name of “Count CarrierTrackingNumber.”

I only want to return records where the CarrierTrackingNumber has count = or greater than 2.

Below is the SQL I developed, but I'm getting this error when I try to execute: Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value '4911-403C-98' to data type int.

SELECT
CarrierTrackingNumber,
(
SELECT
COUNT(CarrierTrackingNumber)
FROM SalesOrderDetail
WHERE CarrierTrackingNumber >= 2
) AS CountCarrierTrackingNumber
FROM
SalesOrderDetail

View 4 Replies View Related

Use Of Variables In Table Names And Fields

Jun 22, 2006

Hi, I am new to using SQL for anything more in depth than querying and reporting.

I am trying to create a series of SQL scripts which will be used across several customer sites so need to be easily customisable. What I want to do is have all of the table names, field names and customisable items handled by variables which will be declared and set at the beginning of the script, making them easy to find and change. The problem I am having at the moment is with creating a new table using variables for table name and field names, can anyone help?

quote:
DECLARE
@a_tmptbl varchar(15),
@a_fieldid1 varchar(15)

set @a_fieldid1 = 'newFieldid'
set @a_tmptbl = 'newTable'

create table @a_tmptbl ( @a_fieldid1 varchar(15), value float, counter INT);

insert into @a_tmptbl values ( "foobar", 21.76, 1);

select * from @a_tmptbl;

The error I am getting is:

quote:Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@a_tmptbl'.


Any advice would be gratefully accepted

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved