Add A Table For Values That Will Never Change?

Feb 3, 2015

What the right table setup is when you have a table with values that will never change.

I'm building a small project for our sales team so they can track their leads & customers. I have a table called "Contacts", which contains the names of every prospect they've ever reached out to. In that table is a field called "ContactTypeId", which is a reference to a table called "ContactTypes".

My ContactTypes table looks like this:

ContactTypeId | Name | Description
----------------------------------------------
1 | Lead | A contact who has shown interest in our service
2 | Qualified Lead | A more deeply engaged, sales-ready contact
3 | Customer | A contact paying for our services

So, I set it up this way because I figured it was cleaner to associate a ContactTypeID# to each Contact in the Contacts table, rather than actually spelling out "Lead", "Customer", etc for each record in the Contacts table.

But is this right? If these values in my ContactTypes table are NEVER going to change, should I still have this table at all or is it better to just insert "Lead", "Qualified Lead", or "Customer" into the Contacts table? Just looking to take the correct approach.

View 4 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Can It Change Columns Of A Table Values To Rows

May 14, 2014

I have a table with this info:

NrCard numberPersonAuto123456789101112
11111111111111111111User1VW Jetta6,46,46,46,45,825,825,825,825,825,825,826,4
22222222222222222222User2Honda CR-V 13,2113,2113,2112,0112,0112,0112,0112,0112,0112,0113,2113,21

How I can get this result:

NrCard numberPersonAutomonthvalue
11111111111111111111User1VW Jetta16,4
11111111111111111111User1VW Jetta26,4
11111111111111111111User1VW Jetta36,4
11111111111111111111User1VW Jetta45,82
11111111111111111111User1VW Jetta55,82
11111111111111111111User1VW Jetta65,82

[code]....

Should I use unpivot or pivot?

View 2 Replies View Related

T-SQL (SS2K8) :: Dynamically Change Values In Script Based On 3 Values

Feb 27, 2014

I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the

1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:

where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)

2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.

3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)

If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.

Again, the series are usually discontinuous and specific to each specialty.

Here's the portion of the script (it's at the end) that I'm talking about:

--change values in square brackets below for each specialty as needed and change the denom number in the very last query.

if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T

[Code] ....

If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.

View 6 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

Check Constraint - Compare Sum Of Two Values In One Table Against Values Located In Another Table

Jul 26, 2014

I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:

check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from
hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where
h.freight_wagon_serial_number = fw.freight_wagon_serial_number and
fw.freight_wagon_type = fwt.freight_wagon_type and
h.train_number = t.train_number) <
(select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where
lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))

The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.

I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.

View 2 Replies View Related

Transact SQL :: Update One Table Based On Another Table Values For Multiple Values

Apr 26, 2015

I have two tables  A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B. 

View 5 Replies View Related

How To Change Values

May 3, 2006

Suppose a very basic question, but how can I easily update values in a record. I can easily READ the value with this code.

Set Rsx = Server.CreateObject("ADODB.RecordSet")
sSQL= "SELECT * from prodfeatures"
Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

While Not Rsx.eof
i=i+1
mte(i)=Rsx("id")
mfnum(i)=Rsx("featureother1")
Rsx.movenext
Wend

..now I try an UPDATE sql to insert the values from the array into another field in the corresponding records - I can get it to work, but it is very stupid done. Actually I must open and close the database for every record to get it to work:

For j=1 To i

Set Rsx = Server.CreateObject("ADODB.RecordSet")
sSQL= "UPDATE prodfeatures SET featurenum=" & mfnum(j) & " WHERE id=" & mte(j)
Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText
next

...I suppose this is silly programming, but it actually works well, but it takes a really lot of time to execute...

...someone who has a faster way of doing this???

View 5 Replies View Related

How To Change The Default Values

Nov 13, 2006

The columns fro which we do not enter any values.Fro eg in my table i did not insert any values to Balance col,in my table.
In SQL 2005,management studio,when i said modify table and changed the default to 0 from NULL and also unchecked the checkbox saying Allow Nulls.

I did the above changes and said save.It gave an error saying

'AccountsBalance' table
- Unable to modify table.
Cannot insert the value NULL into column 'Balance', table 'AccountBalanceSheet.dbo.Tmp_AccountsBalance'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Plz help

Poornima

View 2 Replies View Related

Change Default Colume Values

Feb 8, 2001

Hi, all, does anyone know of a way to change default values inside of a table without affecting existing values?

I have a table with default = 0 in usermask column. Some rows already have non 0 values in them. I now need to change the default to 1 but still want the non 0 values be there. I also want the new default to take effect when new rows are entered. What's the best and quickest way to do it?

Thanks for advice.


Richard

View 2 Replies View Related

Change One Of The Values Of An Existing Line

Jun 17, 2008

Hi everyone!

How can i change one of the values in an existing line (id = 10)

thanks a lot to this forum

landau66

View 1 Replies View Related

Change Datatype Values - From INT To MONEY

May 10, 2015

I am running Microsoft SQL Server 2012.

I had created a database named "Company-Data" that contains a table named "tblStock". This table contains several columns such as ID, Product, Quantity. The datatype for the column "Quantity" is INT.

I had entered 100 records to the table, now I want to change the datatype for the column "Quantity" from INT to MONEY.

How can we do this without loosing the data that has been entered the the column previously.

View 7 Replies View Related

Calculating Change Of Values Between Rows

Aug 23, 2007

Hello forum.


I have three columns in my table with the following datatypes:

Date - DateTime
Height - Decimal
Change_From_last_Height - Decimal

I am using "SQL Server 2005 Express Edition". I'm fairly new to SQL and would greatly appreciate any help or advice I can get.

The "date" column increases by an extra day in every new row and I then enter the new height of the plant. What I want to know is how I can get SQL server express to automatically enter the difference in height between the current row's height and that of the previous row.

Is it possible to automate the entry in the Change_From_Last_Height column in SQL?

Put another way, I know how to find the difference between two values in the same row but different columns, but how do I calculate the difference between values in adjacent Rows (ie. Rows next to each other)?


Thank you.

View 3 Replies View Related

I Need To Change Those NAN Values To 0 In Ssrs Report

Dec 2, 2007



When I use calculation in generating reports, I get "NaN" value in preview , I wanted to avoid this and display "0" if the result of calculation is null or infinity..

I used the below calculation

=CDbl(Fields!POST_TEST_COUNT.Value/Fields!ENROLL_Actual.Value), here i taken input as percentage for every field.

Please let me know how to avoid "NaN" and "infinity", while displaying in reports



when ever i see the prview NAN values are displayed and remaining fields are shows appropriate query results like percentage values Like 98%.00 some thing but i have a problem with NAN values i need to change those NAN values to 0


plese help me.

View 11 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 Replies View Related

Change In Query For Inserting Float Values

Jan 2, 2008



Hi

I was using a simple query to insert data in fields of type decimal


INSERT INTO Table (stringValue,intvalue) VALUES ('myString ',

" + numericvalue + ")



a query looked like this

INSERT INTO Table (stringValue,intvalue) VALUES ('myString',125)

I had to make a change to accept float point values ( 0,012 ) so I change the fields from decimal to float in the DB

Now I'm having problems in the query because of the comma. The new query looks like this

INSERT INTO Table (stringValue,intvalue) VALUES ('myString',0,012) so because of the comma the engine see three values instead two

How can write the query to insert this float values?

thanks

View 4 Replies View Related

Change NULL Values To Default In SELECT Statement

Dec 22, 2006

I have a stored procedure with a SELECT statement, that retrieves 1 row.
SELECT name FROM tblNames WHERE nameID = "1"
I want all the NULL values in that row to be change in some default values.
How do I do this?
 
 

View 4 Replies View Related

Change Self Reference Values In A Data Flow Task

May 9, 2006

Hi,

We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).

When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.

We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.

Please help me to solve this problem.

Regards,

Gopi









View 3 Replies View Related

SQL Server 2008 :: Dynamic Query Pivot Values Change

Aug 6, 2015

I have below script

CREATE TABLE dbo.TestPivot(
CollectionDate DATETIME,
Products VARCHAR(40),
ItemCount INT
)
INSERT INTO dbo.TestPivot
SELECT '4/1/2015','Benz' , 20

[Code] ....

-- Original Output
ProductsApr 2015May 2015Jun 2015
Benz10-800NULL
Toyota5NULL-180

****Required output where ever we have negative values we need to display message Invalid out put message for those negative rows

ProductsApr 2015May 2015 Jun 2015
Benz10Invalid NULL
Toyota5NULL Invalid

View 2 Replies View Related

Reporting Services :: Ssrs 2012 Change Parameter Values

Apr 22, 2015

In an existing SSRS 2012 report, I have a requirement for a user to be able to select by multiple school(s) and/or multiple grade(s). This is fine except certain schools like elementary have grade levels of KG to 06, Middle school has grades of 06 to 09 and high school has grades of 10 to 12. Thus for example, if a user has initially selected grades 11 and 12 and then they select an elementary school that has grades KG through 06, the 'grade level' selection would need to change.In other words,  I am thinking of initially having the available and default values for the parameter called 'Grade' set to KG through 12. However if a school is selected that only has specific grade levels like elementary school, how can I override the original grade level and only allow the user to select grades that the particular school contains?

View 3 Replies View Related

SQL Server 2012 :: Replace All Values In String With Values From Look Up Table

Mar 19, 2014

I have a table that lists math Calculations with "User Friendly Names" that look like the following:

([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])

I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)

The new formulas need to look like the following:

([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])

I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.

How can I accomplish this?

Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.

DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )

[Code] .....

View 3 Replies View Related

How To Delete All The Values In A Table Before Inserting New Values.

Jun 9, 2008

Gurus,
I have two list boxes, user can move items back n forth, from second listbox I am inserting values into a table. So far everything is working fine.
Now I want to delete all the existing  values from the table before inserting evertime..Please help me in this I dont know what to do.
thanks
 kalloo
 

View 8 Replies View Related

Checking To See If Values Are In A Table Or Not -- If Not Then Inserting The Values.

Jun 22, 2005

I'm trying to checking my production table table_a against a working table table_b (which i'm downlading data to)Here are the collumns i have in table_a and table_bDescription | FundID (this is not my PK) | Money I'm running an update if there is already vaule in the money collumn.  I check to see if table_a matches table_b...if not i update table a with table b's value where FundID match up.What i'm having trouble on is if there is no record in table_a but there is a record in table_b.  How can I insert that record into table_a?  I would like to do all of this (the update and insert statement in one stored proc. if possible.  )If anyone has this answer please let me know.Thanks,RB

View 3 Replies View Related

Select Values From One Table Based Upon Values In Another...

May 19, 2006

How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?

View 3 Replies View Related

'Insert Into' For Multiple Values Given A Table Into Which The Values Need To Go

Sep 1, 2007

Please be easy on me...I haven't touched SQL for a year. Why given;



Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF


do I get

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near ','.
for the following;




Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');


Thank you,
hazz

View 3 Replies View Related

I Have Created A Table Table With Name As Varchar And Id As Int. Now I Have Started Inserting The Rows Like, Insert Into Table Values ('arun',20).

Jan 31, 2008

I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50.                 insert into Table values('arun's',20)  My sqlserver is giving me an error instead of inserting the row. How will you solve this problem? 
 

View 3 Replies View Related

Add Values To A Table Based On Values In Another Table

Mar 23, 2005

When a record is inserted or updated records in Table1 I want a record to be inserted into table3 for each record ID that is in table2 if the table2.id does not already exist in table3. What is the best way to do this? Could this be done with a trigger? If it could how would I write such a trigger. I have never written one before and this sounds like a hand full for my first effort. Your help will be greatly appreciated.

Thanks

View 1 Replies View Related

How To Create A System Type Table/ Change User Table To System Table.

May 23, 2007

Is there any Posibility to change a User Table to System Table.

How to create one system table.

I am in Big mess that One of the Table I am using is in System Type.

I cant Index the same. Is there any Mistake we can change a user table to system table.....

View 9 Replies View Related

SQL Server Admin 2014 :: Insert A Row To A Table Based On Table Values?

Jun 10, 2015

Here is my table:

My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?

View 0 Replies View Related

Transact SQL :: Verify Inserted Values From One Table (in CSV File) With Another Table (in Database)

Aug 4, 2015

I am looking for a Sql query to verify the inserted values from one table(in CSV file) with another table(in sql database)

For example: I have below Values column that is present in once CSV file, after my data migration the values get stored in Results table under Message column.

I need to very whether values(1X,1Y) are inserted in Message record "successfully inserted value 1X"

Values (CSV)
1X
1Y

Results Table(SQL)
CreatedDate                   Message
2015-08-04 08:45:29.203  successfully inserted value 1X
2015-08-04 08:44:29.103  TEst pass
2015-08-04 08:43:29.103  successfully inserted value 1X
2015-08-04 08:42:29.203  test point
2015-08-04 08:35:29.203  successfully inserted value 1Y
2015-08-04 08:30:29.203  Test Pass
2015-08-04 08:28:29.203  successfully inserted value 1Y

If all values are inserted:

Output:
All values from values table are inserted successfully
Total count of values inserted: 2
If only few values are inserted, example only 1X from Values table is inserted in Message

Example:
Results Table CreatedDate     Message
2015-08-04 08:45:29.203  successfully inserted value 1X
2015-08-04 08:44:29.103  TEst pass
2015-08-04 08:43:29.103  successfully inserted value 1X
2015-08-04 08:42:29.203  test point

Output:
All values from values are not inserted successfully in result table.
Total count of values inserted: 1
Missing Values not inserted in results table are: 1Y

View 3 Replies View Related

Column Name Or Number Of Supplied Values Does Not Match Table Definition When Trying To Populate Temp Table

Jun 6, 2005

Hello,

I am receiving the following error:

Column name or number of supplied values does not match table definition

I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:

SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp

INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'

The SP UserPersist_GetByCriteria does a
"SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture",
so why am I receiving this error when both tables have the same
structure?

The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.

Thanks,
Greg.

View 2 Replies View Related

What's The Accepted Way To Retrieve Records In A SQL Table With Null Values Using A Visual Studio 2005 Table Adapter?

Jan 21, 2008

I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
 I have a very simple dilemma.  In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND   (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null>  (accounting for about 95% of the records in the database). Thanks in advance Simon
 

View 9 Replies View Related

Multiple Table Query - How To Get List Of All Values For Each Table

Jun 7, 2012

I have a database that has 370 tables that match %_REF_% naming. All of these tables have a column call ds_name.

I need to get a list of all values of ds_name for each table.

View 3 Replies View Related

How To Insert Rows To Table B Based On Values In Table A.

Mar 7, 2008



I need Insert rows in the OrderDetails Table based on values in the Orders Table

In the Orders table i have a columns called OrderID and ISale.
In the OrdersDetails i have columns called OrderID and SaleType


For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD.
That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD



Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD
That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.


I need a SQL Script to accomplish this. thanks

View 6 Replies View Related







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