SQL Server 2012 :: Inserting Values With SP

Mar 17, 2014

I try to do all insert-actions in one SP. But it doesn't work. I couldn't insert any values into the DB. Is this possible or the wrong way?

use env
go
create proc [SP$insert](
@p1 nvarchar(100),
@p2 nvarchar(100),
@id int output,
@debug bit = 0

[Code] ....

View 9 Replies


ADVERTISEMENT

SQL 2012 :: Trigger Not Inserting Any Values?

Aug 11, 2014

I have a trigger that searches for duplicates before inserting values.

I have written the trigger however its not inputting values into the column at all.

see my trigger:

ALTER TRIGGER DUPLICATES
ONAMGR_User_Fields_Tbl
AFTERINSERT,
UPDATE
AS
Declare @AlphanumericCol varchar (750)
Declare @Counter integer

[code]....

View 5 Replies View Related

SQL 2012 :: How To Check Existing Values In A Column For Duplicates Before Inserting

Aug 12, 2014

I have the following objective:

1. I want to check a column to see if there are values (to Eliminate dups)
2. Once checked the values in a column, if not found insert the new value

Here is my code for this:

ALTER TRIGGER DUPLICATES ON AMGR_User_Fields_Tbl
-- When inserting or updating
AFTER INSERT, UPDATE AS

-- Declare the variables
DECLARE @AN varchar(200)

[Code] ....

View 1 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

SQL Server 2014 :: Inserting Values In The Database?

Aug 12, 2015

I am trying to insert a value in the table. Sometimes the value has this symbol at the end. I just want insert the entire value except this symbol. The values can be like this that I am insert in the table

Insert into Table1(col1)
Values (12345§ )

The values can be, below is just an example

12345§
3456DER§
5678D
FGR564

I want to insert only these values from the above data

12345
3456DER
5678
FGR564

View 6 Replies View Related

Inserting Varchar Values Which Is Int Value In Int Column In Server

Aug 6, 2015

IF I have a table like the below one and i have to insert a number value which is inserted as varchar in an int column  then what is expected behavior of this statements .

create
table stud
(id
int)
insert
into stud
values ('1').

I thought it should fail but it succeeds...

View 4 Replies View Related

SQL Server 2014 :: Inserting Distinct Values From One To Another Table

May 28, 2015

I have the following table

Table A

Col1

0118F
0118R
5678
0118F
0118R
5678
5678
5678
0118F
0118R

I want to insert only distinct values of Col1 from table A to another table

Insert into TableB

(
UserName, ProjectName, processdate, Recordprocess, Comments, RecordProcessExt
)
values(@UserName, 'Test Project', getDate(), distinct Col1 from Table1, 'Test Comments', distinct col1+ 'TR' from TableA)

How can I accomplish the above. I need to insert distinct column from TableA to RecordProcess and col1+'Tr' to recordprocessExt.I can do it with cursor. I don't know any other way.

Also, there are other columns in Table A. I am using sql server 2005.

View 1 Replies View Related

SQL Server 2012 :: Inserting Information From One Table Into Another

Aug 12, 2015

I am working on moving information from one of our databases into a newer one the company has recently setup. I am working in MS SQL Server 2012 and am trying to Inset/Update (depending on the information) a part table. I will list the two tables I am working on as Old (where I am getting the information) and New (where it is to be inserted into).

The old table has about 250 columns of information, most of which is not needed. The needed information is as follows: Name, ID, Component1, Component1_PIN, Component1_SN, Component1_Description, Component2, Component2_PIN, Component2_SN. The component section repeats up to Component12.

The new table has columns setup as such: Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description, etc.

The issue I am having is that in the old table each component can have Case, Processor, Memory, etc in that one column. I need to insert Case, Memory, etc into the correct column in the new table while keeping intact the rest of the information.

Example:

Old Table
Name | ID | Component1 | Component1_PIN | Component1_SN | Component1_Description | Component2 | Component2_PIN | Component2_SN | Component2_Description
Nest8 | 5682 | Case | 901834 | 237848117 | Black, rectangular | Memory | 9081234 | 5398798134 | NULL
Nest8 | 5978 | Case | 901463 | 237848138 | Black, rectangular | Processor | 2394875 | 2903857809 | Bad
Reds3 | 5683 | Memory | 2405 | 89752342 | Crucial | HardDrive | 92387595 | 457982234 | NULL
Bass | 5644 | HardDrive | 79872346 | 5321675789 | NULL | Case | 10984528 | 3498769872 | NULL

I am not sure how to loop through and grab each part and place it in the column it needs to be while keeping it with the ID.

View 4 Replies View Related

SQL Server 2012 :: Inserting New Columns Based On Condition

Feb 25, 2014

I have a very simple query like the following…

SELECT table2.column_code2,
table2.column_description2,
table2.column_code1,
table1.column_description1
FROM database_001.table2 table1 LFET OUTER JOIN database_001.table2 table1 on (table2.column_code1 = table1.column_code1)

From this query, its returning me a result set of something like below:

--------------------------------------------------------------------------------------------------
column_code1 column_description1 column_code2 column_description2
--------------------------------------------------------------------------------------------------

RO1 BOOK RL1 PDF/ECOPY
RO2 PAPER RL2 CONFERENCE
RO5 JOURNAL RL11 OTHER

Now, on the above query I want to insert three extra columns with the name (status, location and contact) where the results in the extra three columns would be based on the conditions I want to define in the query based on the above results…

Something for example (I am not trying to write a condition: my question is how to write it),

if column_code1 = RO1 and column_description2 = PDF/ECOPY on status column it should return a value ‘ONLINE’ & on location column it should return ‘WEB’ and on contact column it should write ‘BOB’.

Also, if column_code1 = RO5 and column_description1 = JOURNAL on status column it should return a value ‘ON PRESS FOR PRINT’ & on location column it should return ‘S.R STREET, LONDON’ and on contact column it should write ‘SMITH’ like below result…so the final output should be the top four columns and the extra three columns…

See the attachment for better formatting...

---------------------------------------------------------------------------------------------
status location contact
---------------------------------------------------------------------------------------------
ONLINE WEB BOB
ON PRESS FOR PRINT S.R STREET, LONDON SMITH

View 7 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

SQL Server 2012 :: Inserting Record In Table - Trigger Error

Aug 6, 2014

I am inserting a record in XYZ table(DB1). Through trigger it will update ABC table(DB2).

I am getting error when doing above thing. What are the roles to be set to user to avoid above problem.

View 3 Replies View Related

SQL Server 2012 :: Inserting Dummy Records Using Loop Statement

Jul 17, 2015

I have the following attributes in this Table A.

1) Location_ID (int)
2) Serial_Number (nvarchar(Max))
3) KeyID (nvarchar(max)
4) Reference_Address (nvarchar(max)
5) SourceTime (datetime)
6) SourceValue (nvarchar)

I am trying to create 1000000 dummy records in this this table A.How do i go about do it? I would like my data to be something like this

LOCATION_ID
1

Serial Number
SN-01

KeyID
E1210

Reference_Address
83

SourceTime
2015-05-21 00:00:00 000

SourceValue
6200

View 7 Replies View Related

SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?

Mar 19, 2015

From my query I am getting results like below in one of the column:

'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'

this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.

I need something that shows Date on one side representing particular amount on the other

For Immediate Due it will be current date and the amount besides it.

how can I achieve this.

View 8 Replies View Related

SQL Server 2012 :: Float Value Converting To Exponential While Inserting To Varchar Field?

Aug 6, 2015

Am converting varchar field to float and summing using group by and next inserting to varchar field(table).

while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726

My question is why it is converting while inserting ? and how to avoid it.

select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue

View 4 Replies View Related

SQL Server 2012 :: Variable Declared For Inserting Records Is Setting To Empty From Second Execution Onward?

Jul 9, 2015

I am facing a strange problem in executing stored procedure. Basically my sproc will take a values from Java application and create a Insert statement. see stored procedure below.Just to give some more background- I am re writing the procedure which was written in oracle already.

Problem I am facing now is with the statement below . When I execute the procedure for first time it works fine however when I execute for second time onwards it is setting to empty. Not sure what is the problem with my declaration and setting up with values. For reference I have pasted my complete stored procedure code below.

select @L_STMT= 'INSERT INTO '+ @l_table_name + '(' + LTRIM(RTRIM((substring (@L_INS_STMT,2,len(@L_INS_STMT))))) + ') VALUES (' + LTRIM(RTRIM((substring (@L_INS_STMT1,2,len(@L_INS_STMT1))))) +')';
ALTER PROCEDURE [dbo].[PKG_OBJ_API$CREATE_OBJ]
(
@P_TYPE VARCHAR(4000),
@P_SCOPE VARCHAR(4000),
@Arrlist varchar(max),

[code]....

View 3 Replies View Related

SQL Server 2012 :: Sum Values With If?

Oct 28, 2014

I need to generate a report from a table using the values from a filed to determine what to add up.

create table fun_test(
packtype nvarchar(50),
price money)
insert into fun_test
values ('Single',''),('Monthly','25.00'),('Monthly','27.00'),('Monthly','23.50'),('Single',''),('Deposit',''),('Deposit','')

[code]....

order by PackTypeWhat I need is if the PackType is Deposit and need to multiply the number of records by 35, If the PackType is Monthly I need to multiply the records by 25 and if the PackType is Single I need to sum the values in the price column.

View 4 Replies View Related

Millisecond Values Missing When Inserting Datetime Into Datetime Column Of Sql Server

Jul 9, 2007

Hi,
I'm inserting a datetime values into sql server 2000 from c#

SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime

C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?

thanks,
Mani

View 3 Replies View Related

SQL Server 2012 :: Read All Values From XML Node?

Nov 21, 2013

I have the following code and trouble reading values of Bank Accounts. If i remove the line it says "xmlns="http://applications.apch1.com/webservice/schema/" then i my query is working. But i cant remove this becasue that is what i will get response from a web service. All the records are stored in the database with this line included.

DECLARE @MyXML XML
SET @MyXML = '<GetEmployeeDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<return xmlns="http://applications.apch1.com/webservice/schema/">
<CustomerID> A8339 </CustomerID>
<BankAccounts>

[Code] .....

View 2 Replies View Related

SQL Server 2012 :: Get Sum Multiplied Values From Function

Jan 18, 2014

I have an existing function and need to alter function to give result of the values multipiled until its parent is reached.need two seperate functions for city and amt columns..need to also display the parent-description

--CREATE TABLE
CREATE TABLE [dbo].[CityData](
[Id] [int] NULL,
[ParentID] [int] NULL,
[City] [nchar](20) NULL,
[Location] [nchar](50) NULL,
[Amt] [int] NULL
) ON [PRIMARY]

[code]...

View 8 Replies View Related

SQL Server 2012 :: How To Match Values From A PIVOT

Jan 31, 2014

I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events and who was responsible for them. I can get the max(date) field in columns using PIVOT, but can't see a way to get the 'who' field without lots of LEFT JOINs.

Here's some test data and code which shows the principle:

CREATE TABLE #t
(
ref INT ,
id INT ,
who VARCHAR(10) ,
checkin DATE

[Code] ....

The result set is:

ref 1 who1 2 who2 3 who3 4 who4
123 2014-01-18 carol 2014-01-18 andy 2014-01-16 bill 2014-01-17 carol
456 NULL NULL 2014-01-17 NULL NULL NULL NULL NULL

Is there some way to avoid all the LEFT JOINs, maybe by using another PIVOT, to produce the same result?

View 4 Replies View Related

SQL Server 2012 :: Get List Of Last Non Null Values

Feb 10, 2014

I have two tables, a dates table and a values table. They are joined on the date column.The date table has a range, say from today as far as 20 days from now, incrementing by 1 day each row.The values table may have a row for a day, and may not. If the day has a value I want to display that value.If the day does not have a value in the values table I want to display the last known value.

I think this can be done with windowing functions in a set based manner but have not been able to work it out. I have done it procedurally but im not happy with that at all, and really want to see if this is possible in a set based manner.Below is some simplified code to allow testing with sample data.

create table DimDate
(
DateCol date
)
create table TotalsData
(
DateCol date

[code]....

View 6 Replies View Related

SQL Server 2012 :: Finding Breaks In Key Values?

Jun 12, 2014

The following is sample data I am dealing with.

SELECT * INTO TEMP
FROM
(SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '01-01-2014' AS STARTDATE, '01-31-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '02-01-2014' AS STARTDATE, '02-28-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '03-01-2014' AS STARTDATE, '03-31-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A2000' AS CODE, '04-01-2014' AS STARTDATE, '04-30-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '05-01-2014' AS STARTDATE, '05-31-2014' AS ENDDATE) X

I need to extract the date that the value in CODE column changes to another code for each value of CATEGORY and if there is no change, to record the original CODE value and its startdate for each CATEGORY.

View 3 Replies View Related

SQL Server 2012 :: Joining 2 Selects Where A Value Not In Values?

Aug 12, 2014

I have 2 queries where I select all accounts with bill code in 'bmonit' example ('12','39','124','1FA') then I also have a Select where the same account might have additional services, that are not in example ('12','39','124','1FA') and for these accounts I need to just put a 'Y' if stop_date is null.

View 4 Replies View Related

SQL Server 2012 :: Finding First And Repeated Values

Aug 26, 2014

I'm trying to come up with a query for this data:

CREATE TABLE #Visits (OpportunityID int, ActivityID int, FirstVisit date, ScheduledEnd datetime, isFirstVisit bit, isRepeatVisit bit)

INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd)
SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL
SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL
SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL

[Code] ....

Here are the expected results:

OpportunityIDActivityIDFirstVisitScheduledEndisFirstVisitisRepeatVisit
110012014-08-172014-08-17 12:00:00.00010
110022014-08-172014-08-17 17:04:13.00001
210032014-08-182014-08-18 20:39:56.00001
210042014-08-182014-08-18 18:00:00.00010

[Code] ....

Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.

I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:

SELECT*,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 1
ELSE 0
END AS isFirstVisit,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 0
ELSE 1
END AS isRepeatVisit
FROM#Visits

View 3 Replies View Related

SQL Server 2012 :: Pass In Null Values

Sep 20, 2014

The following t-sql 2012 works fine in sql management studio. However when I place it in a .net 2010 web form application, I am told the sql does not work when the parameter values are null. Thus can you tell me what I can change in the sql below that will accept null as 3 possible input values?

SELECT i.[lastName]
,i.[firstName]
,i.[middleName]
,i.[suffix]
,a.[userid]

[code]...

View 1 Replies View Related

SQL Server 2012 :: Reconcile Values In 2 Databases

Oct 9, 2014

I am trying to reconcile two different databases. Each database holds customer information and some of this information is represented as drop down tables. Here is how it is linked

Structure

Tables Fields

Attribute names Name, AttID, AttValue
Attribute Values AttID, AttValue, CustID, CustCode
Customer DBID, DBCode,CustID, CustCode, FName, LName

Database 1 Sample

Attribute name Attribute Values Customer
Color 8, 0 8, 1, 1234, 6 892, 14, 1234, 6, John, Doe
red 8, 1 8, 2, 1234, 6
blue 8,2

Database 2 Sample

Attribute name Attribute Values Customer
Color 5, 0 5, 3, 1234, 6 892, 14, 7434, 5, John, Doe
red 5, 3 5, 4, 1234, 6
blue 5,4

Within a database, the ATTID and Attcode link the attribute and attribute value tables and they link to the Customer table with CustID and Cust Code. One Customer may have multiple entries within the Attribute value table

The Customer tables in each database are linked by the DBID and DBCode values. The other ID's are not necessarily the same across databases.

The object is to compile a list of Customers where these attribute values vary between databases. There are approximately 56,000 Customers in the customer table and 710,000 entries in the attribute values table.

My attempts to do this have produced cross joins with millions of values returned

View 7 Replies View Related

SQL Server 2012 :: Rounding UP Values In Column

Nov 28, 2014

I need to round UP values but they should never be rounded down, below is my expected output in RoundVal column.

SELECT 89 AS Val, 100 AS RoundVal UNION ALL
SELECT 329, 1000 UNION ALL
SELECT 6329, 10000 UNION ALL
SELECT 43299, 100000 UNION ALL
SELECT 155329, 1000000

View 1 Replies View Related

SQL Server 2012 :: Combine One Value To Equal Two Values

Feb 13, 2015

I have a report that I am running in visual studio 2010, that gets its data from a few different stored procedures in a SQL 2012 Database. The variables are Date and Office Code. We currently have 6 different office codes. One of the Stored Procedures gets call information for each office. With the report we can select any single office or any combination of offices to compile data.

The problem I have is two of these share phone information, so when you select either one or both of them the same data gets returned. So for example we have office codes of AAAAA, BBBBB, CCCCC, DDDDD, EEEEE, and FFFFF. Now AAAAA, and BBBBB share phone information so if you select office code AAAAA, the phone info that is returned is for AAAAA, and BBBBB, and visa versa. So I am not sure how to accomplish that either in the report or in the stored procedure.

View 1 Replies View Related

SQL Server 2012 :: Duplicate Values In UNPIVOT

Feb 18, 2015

I have a set of data in which i have a product number going through 6 stages and each stage has a date. Since the each stages are in columns, I have created a unpivot query to transpose the columns into rows.

The actual result data needs to be in format of

ProductNumber Event_NameEvent_Date Event_Days
101 Stage 1 2/13/2014 1
101 Stage 2 2/13/2014 0
101 Stage 3 2/18/2014 5
101 Stage 4 2/23/2014 5

However the result data i am getting is like

ProductNumber Event_NameEvent_Date Event_Days
101 Stage 1 2/13/2014 1
101 Stage 1 2/13/2014 0
101 Stage 1 2/18/2014 5
101 Stage 1 2/23/2014 5
101 Stage 2 2/13/2014 1
101 Stage 2 2/13/2014 0
101 Stage 2 2/18/2014 5
101 Stage 2 2/23/2014 5

The unpivot query is working fine however I am getting duplicate values in the result. For each productnumber there must be only 6 results however i am getting 24 rows for each product number due to duplication.

I have attached the code and the source data for reference

Code:
SELECT distinct ProductNumber ,
Event_Name ,
Event_Date ,
Event_Days
FROM(
SELECT ProductNumber ,

[Code] .....

View 2 Replies View Related

SQL Server 2012 :: How To Compare A List Of Values

Aug 3, 2015

how would I compare a list of concrete values?

---table with items

SET NOCOUNT ON;
DECLARE @items TABLE (ITEM_ID INT, ITEM_NAME VARCHAR(10))
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 10,'ITEM 1'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 11,'ITEM 2'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 12,'ITEM 3'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 13,'ITEM 4'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 14,'ITEM 5'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 15,'ITEM 6'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 16,'ITEM 7'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 17,'ITEM 8'
SELECT * FROM @items

-- table with categories

SET NOCOUNT ON;
DECLARE @categories TABLE (CAT_ID INT, CAT_NAME VARCHAR(10))
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 100,'WHITE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 101,'BLACK'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 102,'BLUE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 103,'GREEN'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 104,'YELLOW'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 105,'CIRCLE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 106,'SQUARE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 107,'TRIANGLE'
SELECT * FROM @categories

--table where categories are assigned to master categories

SET NOCOUNT ON;
DECLARE @master_categories TABLE (MASTERCAT_ID INT, CAT_ID INT)
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,100
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,101
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,102
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,103
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,104
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,105
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,106
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,107
SELECT * FROM @master_categories

-- items-categories assignment table

SET NOCOUNT ON;
DECLARE @item_categories TABLE (CAT_ID INT, ITEM_ID INT)
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,11
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,11

[code]....

So now I need to query the table @t4 in and to determine the items that are assigned to category 'WHITE' in master category 1 and to 'CIRCLE' in master category 2.The important thing is to return items that are assigned solely to 'WHITE' in master cat 1 and solely to 'CIRCLE' in master cat 2.In the above example it would be only the ITEM 1 (id=10) that is returned:

1. ITEM 2 (id=11) is not returned because it has the assignment to category 'SQUARE' in master cat 2 additionally

2. ITEM 3 (id=12) is not returned because it has the assignment to category 'BLACK' in master cat 1 additionally

3. ITEM 4 (id=13) is not returned as it does not have assignment to category 'CIRCLE' in master cat 2 but only to 'WHITE' in master cat 1

3. ITEM 5 (id=14) is not returned as it does not have assignment to category 'WHITE' in master cat 1 but only to 'CIRCLE' in master cat 2

View 3 Replies View Related

SQL Server 2012 :: Null Values With Joins?

Aug 14, 2015

CREATE TABLE A (ID INT IDENTITY (1,1))
CREATE TABLE B (ID INT, EMPID VARCHAR(10))
INSERT INTO A DEFAULT VALUES
GO 5
INSERT INTO B VALUES (1,'E23')
INSERT INTO B VALUES (1,'E24')
INSERT INTO B VALUES (2,'E23')

from the above code i would like to get output like

ID EMPID
1 23
2 23
3 null
4 null
5 null
1 24
2 null
3 null
4 null
5 null

I'm trying like

select a.id, b.empid from (
select * from a cross join (
select distinct empid from b) b
) a

left outer join b on a.id = b.id but i get repetitive rows.

View 7 Replies View Related

SQL Server 2012 :: Retrieving Null Values

Sep 10, 2015

Question in review today is Creating a Report showing the FName, LName of all Employees not Specified in a region; I would assume "No Value to be Null" Correct?

Or is there another way for me to do this?

This is what I have so far...What am I missing that this is not showing me results?

Select Firstname, Lastname, Region
From Employees
WHERE Region LIKE 'null%';

View 3 Replies View Related







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