andre writes "I have two questions:
1.)How do i determine if fields are not unique??
2) How to update one table with values from another table when the row names are diffrent??
Thank you!!"
I need to write a script which will respond differently if a table's index forces uniqueness.
I am using SQL 7.0 and I am using the [sysindexes] table of my database to examine those indexes. I am using the [id] field from the [sysobjects] table for the table I am examining and I am using that [id] field to find like-valued [id] fields in the [sysindexes] table.
My problem is that I can't seem to determine, from an examination of the [sysindexes] table, whether, or not, an index is unique.
When adding an index, and checking the unique property of that index, the values that I get in the [status] field of [sysindexes] are different from the values which are supposed to show, at least according to the sysindexes (T-SQL) entry in SQL Server Books Online.
For instance, I have a table which combines two text fields which comprise the only key on a table. The unique and clustered boxes are checked in the index setup screen. When I look in the [sysindexes] table, the value in the [status] field is 2113554, which is not a value I see in the books online page. According to my books online page, a unique index should have a value of 2 in the [status] field and a clustered index should have a value of 16 in the [status] field. My assumption is that I should see a value of 18 in that [status] field, not 2113554.
I looks like the books online entry might be out-of-date because the field that is labeled [reserved1] in my books online page, is labeled [StatVersion] in my actual [sysindexes] table. That [StatVersion] field looks suspiciously like a Status Version field, possibly indicating that the Status field has undergone some sort of version revision?
Is anyone familiar with this stuff?
Thanks.
Ken
I would need to rewrite SQL code to determine that id is unique in the Customer table.
My two tables are:
CREATE TABLE CUSTOMER(
[CUSTNO] varchar(5) NOT NULL,
[ID] CHAR(9) NOT NULL,
[NAME] VARCHAR(128) NOT NULL,
[ADDRESS] VARCHAR(128) NOT NULL,
[DATEOFBIRTH] DATE NOT NULL,
[Code] ....
> This is a common problem with some solution[color=blue]>>[/color]/************************************************** *********************************** Problem:* Determine the Duplicated Records in a table using single SELECT.** We shall be using Northwind database, add some duplicate records.** Here we want to know if 2 columns (CompanyName,* PHone) are duplicated in a table.*** ShipperID CompanyName Phone* ----------- ------------------------- ------------------* 1 Speedy Express (503) 555-9831* 2 United Package (503) 555-3199* 3 Federal Shipping (503) 555-9931* 4 Federal Shipping (503) 555-9931* 5 Speedy Express (503) 555-9831* 6 Federal Shipping (503) 555-9931***************************************************** **/================================================== SOLUTION 1: Gives me the IDs that are duplicated.================================================== SELECTShipperID, CompanyName, PhoneFROMSHIPPERSWHEREEXISTS (SELECTNULLFROMSHIPPERS bWHEREb.CompanyName = SHIPPERS.CompanyNameAND b.Phone = SHIPPERS.PhoneGROUP BYb.CompanyName, b.PhoneHAVINGSHIPPERS.ShipperID < MAX( b.ShipperID ))/* ********************* Output results********************/ShipperID CompanyName Phone----------- ----------------------------------------------------------------1 Speedy Express (503) 555-98313 Federal Shipping (503) 555-99314 Federal Shipping (503) 555-9931(3 row(s) affected)================================================== ===========SOLUTION 2: Gives me the data which are duplicate butnot the IDs================================================== ===========SELECTCompanyName, PhoneFROMSHIPPERSGROUP BYCompanyName, PhoneHAVINGCOUNT(*) > 1/* ********************* Output results********************/CompanyName Phone---------------------------------------- ------------------------Speedy Express (503) 555-9831Federal Shipping (503) 555-9931(2 row(s) affected)
View 1 Replies View RelatedHello,
I m writing a stored procedure to query a table Population that has the following fields - CityId, CityName, Plus0, Plus10, Plus20, Plus30, Plus40, Plus50, Plus60, Plus70, Plus80. The field Plus0 contains the number of people of age > 0 living in the city, Plus10 contains the number of people of age > 10 living in the city and so on. Given the city id and age groups like 20To40, 50To60, 40Plus, etc., I should be able to query the number of people in the city corresponding to the requested age group. Note that if the requested age group was 20To60, I need to make use of only 2 fields Plus20 and Plus60 in the table to compute this value. And if the requested age group was 40Plus, then I need only the value in the field Plus40. The problem is that a wide variety of age groups can be requested like 0Plus, 10Plus, ... , 80Plus, 0To10, 0To20, 0To30, .... 70To80.
Which is the most effecient way to handle this ?
1. Have a stored procedure that returns all the fields even though only 1 or 2 of them would be actually used ?
In this case, if I returned data for a large number of cities then there would be a lot of unnecessary information that was returned by the query. Lots of data would be passed through the network though most of it would not be used.
2. Have a stored procedure that takes in parameters @Plus0, @Plus10, @Plus20, .. @Plus80 that are bits indicating whether the field was required or not and then using a CASE statement to return values for a field only if the corresponding bit parameter was set, and returning NULL if the corresponding bit paramter was not set ?
In this case, I would be returning NULL for all those fields that were not required for a particular age group. This would save some network bandwidth, wouldn't it ?
3. Pass in the age group itself (ex: 0To20) as a parameter to the stored procedure and have lots of IF statements, one for each age group, that return only the fields that are needed for that age group.
This leads to a lot of code repitition.
4. Use a similar approach as above but use dynamic SQL queries to avoid code repitition.
But using dynamic SQL queries can affect the performance of the stored procedure as they need to be compiled each time.
5. Any other possible approaches ??
Looking forward to your responses,
Thanks much,
bmgun.
Hi,
I have a few different files in a directory.
I want to take each file and determine which columns and data types it has.
How can I know how many columns there are in a file?
Thank you.
Does anyone have a script that can do this?
To keep drilling down as far as possible regardless of complexity?
e.g.
A proc uses a couple of columns from a table and a another few from a view (drill down to view to find source columns), also a table function (drill down to find columns used) and so on
have a table with 2 fields i want them to be unique. idont want duplicate.
how will i do it
I want to add a unique constraint on 3 fields, to only allow the value in the field ONE time. The value will NEVER be the same for anything else. This is the table structure
Code:
Create Table Employees
(
P_Id int NOT NULL,
InstructorName varchar(255) NOT NULL,
CourseName varchar(100) NOT NULL,
DataTableName varchar(100) NOT NULL
)
I want to create a unique constraint across the fields Instructorname, CourseName, DataTableName as their is ONLY 1 instructor per course per table so those 3 fields will ALWAYS hold unique values. I think the constraint syntax would go like so, but want to check before I go butchering some sql syntax.
constraint syntax:
Code:
Alter Table Employees
Contstraint uniqueEmployee Unique (InstructorName ,CourseName ,DataTableName )
This is the logic I need to incorporate in to sql
if the getdate() < term start date then R
if the getdate() > term date date and getdate() < term end date then C
if the getdate() > term end date then H
I have come up with the following type of case statement that will allow me to determine the first two values (to a degree), however , I need to evaluate the end date as well.
Is there a way to look at multiple columns in a case statement?
CASE
WHEN DATEDIFF(DD,GETDATE(),TRM_BEGIN_DTE) >0 THEN 'R' else 'C' end
I feel sure that this is a very naive question, but it is freeky me out that I can solve it. With SQL server what is the easiest way to make a field other than the Primary key unique?Would appreciate help with this. ThanksPaul
View 3 Replies View RelatedPlease see below post
View 2 Replies View RelatedI'm designing an app for stock keeping. In my DB, I have a field called "ItemSerialNo" and I made it unique(but not the table's primary key). On my front end, I have a text box for Item Serial No and a combo box loaded with the item brands and also a save button. I know that if I try to save a serial no already existing in my DB, the app won't allow me because of the unique property of the field named "ItemSerialNo". But I want to be able to save a serial no already existing in my DB but with a different brand name.
For example, I want to be able to save information like:
1. ITEM SERIAL NO = 12345
BRAND NAME = AA
2. ITEM SERIAL NO = 12345
BRAND NAME = BB.
What I need to do is re-populate a unique number into multiple fields,
Let me explain, An employee can appear in the first table only once but can be in the second table multiple times with multiple employee numbers .There is a field called TFN that is unique and we are using it to create a unique id called KRid so what I have done is created 2 tables namely TEST_TBL and TEST2_TBL . In TEST_TBL I am populating a KRid with a unique no being produced by the TFN field only once i.e 12345 being the resulting unique id number. If an employee has 2 employee numbers i.e empno 1 and empno 1000,only employee no 1 will have the unique KRid created but nothing for 1000 because the record already exists , so what has me stumped is that the TFN for employee empno 1 and the TFN for empno 1000 are the same. How do I get the KRid (12345 from empno 1) to populate empno 1000 in TEST2_TBL , The second table has all records in so I can group the second table by TFN id but how do I populate employee 1000 in the second table with the KRid 12345.
Please help!!!!! Below are how the tables are set up and an example of the result.
TABLE 1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST_TBL]
GO
CREATE TABLE [dbo].[TEST_TBL] (
[Empl_Num] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[First_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mid_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hours_Day] [numeric](18, 2) NULL ,
[Hours_Wk] [numeric](18, 2) NULL ,
[KR_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[Date_Term] [datetime] NULL ,
[Empl_Type] [int] NULL ,
[Cost_Centre] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Empl_Status] [int] NULL
) ON [PRIMARY]
GO
TABLE 2
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST2_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST2_TBL]
GO
CREATE TABLE [dbo].[TEST2_TBL] (
[EmpNumber] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[KR_ID] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[EmpStatus] [int] NULL ,
[EmpType] [int] NULL ,
[CommonName] [char] (32) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Query goes as follows for table 1:
SELECT NPE000.EmpNumber, NPET00.RecordStatus, NPE000.KR_ID, NPE000.Surname, NPE000.FirstName, NPE000.SecondName, NPE000.Class, NPE000.DateEmployed, NPE000.DateOfBirth, NPE000.HoursPerDay, NPE000.HoursPerWeek, NPE000.PassportNo, NPE000.AwardCode, NPE000.EmailPayslipTo, NPE000.Location, NPE000.Grade, NPE000.DateTerminated, NPE000.EmploymentType, NPE000.DistCode, NPE000.EmpStatus, NPET00.TaxRefNo FROM NPE000 NPE000, NPET00 NPET00 WHERE NPET00.RecordStatus = 0 and NPET00.TaxRefNo <> ' 111111111' and NPET00.TaxRefNo <> ' 000000000' AND LENGTH(NPET00.TaxRefNo) >= 9 AND LENGTH(NPE000.KR_ID) >= 0 AND NPE000.EmpNumber = NPET00.EmpNumber
Query goes as follows for table 2:
SELECT NPE000.EmpNumber, NPE000.FirstName, NPE000.Surname, NPE000.Class, NPE000.Location, NPE000.EmploymentType, NPE000.EmpStatus, NPET00.TaxRefNo, NPE000.Paypoint, NPE000.KR_ID, FROM NPE000, NPET00 WHERE Recordstatus = 0 and (EmploymentType = 1 AND EmpStatus = 1 AND NPE000.EmpNumber = NPET00.EmpNumber
From this you can see that in table 1 it will only create 1 KR_ID for only one employee number but in table 2 I am bringing through all employee records. In table 2 I can group by NPET00.TaxRefNo which will bring all NPET00.TaxRefNo's togeather. From that I would like to populate the other employee numbers with the unique KR_ID.
Example:Table 1
000001,Jackson,James,Sam,7.6,38,12345,475431212
Example:Table 2
000001,Jackson,James,Sam,7.6,38,12345,475431212
001000,Jackson,James,Sam,7.6,38,,475431212
I hope this helps
Thanks in advance
I have a simple query which displays items from inventory with their latest annual test date. I want to create another unique reference in my results to use as a certificate number. The number should be a combination of the item+month+year from the test date. What is the easiest way to accomplish this?
My query and my desired results are below:
select item, test_date
from inventory
where cat = 'TELE' and itemised_status > 15
item test_datecert_no
-------------------- ----------------------------------------
05MC0002 2014-06-10 00:00:0005MC0002-06-2014
06MT0001 2014-05-13 09:02:0006MT0001-05-2014
06MT0002 2014-05-13 09:03:0006MT0002-05-2014
06MT0003 2014-05-13 09:03:0006MT0003-05-2014
06MT0004 2014-05-09 14:12:0006MT0004-05-2014
With an INSERT statement I add a record to a table.
Then I want to get the (autonumber) ID of the newly created record.
What is the fastest and best way to do this?
I am working on creating a report which is retrieving data from a SQL 2005 database and being displayed in a C#.net web page. Presently, I am binding the dataset to a ASP:Gridview on the web page.
I am currently retrieving most of the needed fields using the following SQL statement:PROCEDURE [dbo].[pr_getReportTickets] @DateCreated nvarchar(15) ASBEGIN
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure hereSELECT tbl_Queue.num_TicketNumber AS TicketID , tbl_Users.str_Name AS Technician , tbl_Queue.str_QueueLocator AS TechNTID , tbl_Queue.dat_ReceivedRequest AS dateCreated , tbl_Queue.dat_DueDate AS DueDate , tbl_Queue.str_TaskName AS TicketTitle , tbl_Queue.str_Requestor AS RequestorNTID --, I need most current dat_TimeStamp from tbl_Notes here for this record --, I need most current int_PercentComplete from tbl_Notes here for this record --, I need to sum up all of int_MinutesWorked fields from tbl_Notes here for this record , MasterEmp.dbo.fn_FormatFullName(tbl_employee.str_fname, tbl_employee.str_lname) AS RequestorName , tbl_Queue.str_TicketType AS TicketType , tbl_Status.str_TaskStatus AS TicketStatus , tbl_Severity.str_Priority , tbl_Complexity.str_Complexity , tbl_Severity.str_Priority + N' / ' + tbl_Complexity.str_Complexity AS Priority , tbl_Queue.dat_CompleteDate as DateCompleted
FROM dbo.tbl_Queue LEFT OUTER JOIN MasterEmp.dbo.tbl_employee AS tbl_employee ON dbo.tbl_Queue.str_Requestor = tbl_employee.str_ntid LEFT OUTER JOIN tbl_Users ON tbl_Queue.str_QueueLocator = tbl_Users.str_ntid LEFT OUTER JOIN tbl_Status ON tbl_Queue.num_Status_CD = tbl_Status.num_Status_CD LEFT OUTER JOIN tbl_Severity ON tbl_Queue.str_Severity = tbl_Severity.str_Severity LEFT OUTER JOIN tbl_Complexity ON tbl_Queue.int_ComplexID = tbl_Complexity.int_ComplexID
WHERE (dbo.tbl_Queue.dat_ReceivedRequest > CONVERT(DATETIME, @DateCreated, 102))
ORDER BY TicketType, tbl_Queue.str_Severity
I also have another table called tbl_Notes. This table contains an unlimited quantity of records for every “num_TicketNumber�. This table contains the following fields: num_TicketNumber, str_TechRep, str_Notes, dat_TimeStamp, int_PercentComplete and int_MinutesWorked
I need to add two more fields to the query, but I do not know how to tell the SQL statement how to retrieve the data for the specfic record and inbed the fields into the Dataset being returned from the database to the web page.1) The first field I need is the most current recorded Date field from the tbl_Notes table for the each of the records returned in the above SQL statement. Similar to: SELECT TOP (1) dat_TimeStamp FROM tbl_Notes WHERE(num_TicketNumber = xxxx) ORDER BY dat_TimeStamp DESC2) The other field I need to return with the Dataset is the sum of the int_MinutesWorked for each of the tickets being retrieved. Similar to: SELECT SUM(int_MinutesWorked) AS TotalMinutesFROM tbl_Notes WHERE (num_TicketNumber = 49)
I have the following table (Table does not have unique key id )
Last Name   First Name     DATE     Total-Chrg
Jaime        KRiSH      5/1/2015     -4150.66
Jaime        KRiSH      5/1/2015      1043.66
Jaime        KRiSH      5/1/2015      1043.66
Jaime        KRiSH      5/1/2015      4150.66
Jaime        KRiSH      5/3/2015      4150.66
Peter         Jason       5/1/2015      321.02
Peter         Jason       5/1/2015      321.02
Peter         Jason       5/23/2015     123.02
I want the results to be in following way
Uniq ID   Last Name   First Name      DATE     Total-Chrg
1         Jaime       KRiSH         5/1/2015   -4150.66
2         Jaime       KRiSH         5/1/2015   1043.66
2         Jaime       KRiSH         5/1/2015   1043.66
3         Jaime       KRiSH         5/1/2015    4150.66
4        Jaime       KRiSH         5/3/2015    4150.66
5         Peter        Jason         5/1/2015    321.02
6        Peter        Jason         5/1/2015    321.02
7        Peter        Jason        5/23/2015   123.02
May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values. Â There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates).Â
Table Name: Denominator
Already has the following constraint:
PK_Denominatorclustered, unique, primary key located on PRIMARY DenominatorID
How can I add a unique key that will cover the 3 fields --> MemberID,MeasureID,TimePeriodID
I also want to know whether we can include the " WITH ( IGNORE_DUP_KEY=ON ) "
I have two tables in my database called CartItems and OrderItems. Istore all of a session's shopping cart items in the CartItems tableusing the sessionID as the identifier (called cartID in my DB). Afteran order is placed and is approved, I would like to copy all of theitems in the CartItems table for that given cartID to the OrderItemstable given a new orderID.I will know the cartID and orderID ahead of time and would like tosend them both into a stored procedure and have the transfer takeplace.Example:take this data...CartItems (table)--------------------------------------cartID | itemID | quantity | price--------------------------------------12345 2 1 12.9512345 7 2 17.95and make it this data...OrderItems (table)--------------------------------------orderID | itemID | quantity | price--------------------------------------00001 2 1 12.9500001 7 2 17.95via some stored procedure that I send (@cartID,@orderID)Any help would be greatly appreciated!!
View 5 Replies View RelatedAn insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
Hi Guys, I came here again to ask something about my application, Is there a way to make 2 columns unique with each other even if they don't have a primary key? If there's a way how? I wanna make a rule so, my application wont replicate its record. Graciaz,Nhoyti
View 3 Replies View RelatedI use the identity = yes for my unique columns in most of my tables that need it. I am trying to decide if I should change identity = no, and instead manually update my unique number column myself by adding one when I insert new rows.
The reason I want to do this is for maintainabilty and ease of transfering data for backup to other sql servers. I always have trouble keeping the identity numbers to stay the same as they are in the original database because when they are transfered to a db that has identity = yes, the numbers get rearranged.
It will also make it easier to transfer data from original db to another sql server db and use the data right away without having to configure the destination db to disable identity and then enable it, etc.
Any pro's con's appreciated,
Dan
I have result set as the attached in screenshot, where the candidateid gets duplicated, but then the column university and careerchoice column has different values for the same candidate. (the CareerChoice and University comes from different tables and i have used union clause)
What want is e.g. CandidateId 186 repeats three times, it should be one row, with three columns all filled with the values which are in different rows.
So that I don't have duplicate candidate ids and i have single row with all column data.
I find to be able to have multiple NULL entries in the following constraint:
Code:
CREATE UNIQUE INDEX my_uidx ON my_table(my_col1,my_col2) WHERE ??? IS NOT NULL;
But is not possible to check multiple columns in "WHERE".
I using SQL Server 2012 Express
Hi i am designing a database. It will be used by field guys who will insert new records and will replicate at the end of the day with a central database using merge replication. Can anyone tell me if I am choosing wisely or not with the following 3 points:
1. i am going to use a uniqueidentifier value in a column (using newid()) for the merge replication. (I don't care that it's an ugly value as i never plan to look at it.
2. I am using an identity column (INT)to create a unique value within the table
3. I want to create a unique column comprising of data from other columns (e.g. date+identity+salesrep) This gives me an intelligent candidate for a primary key. But I think i have to create this with an instead of trigger (is that right)
many thanks in advance
ICW
Using DISTINCT with SELECT have effect only for one column.
But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.
Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows
Select DISTINCT a4 From Y ---> " 85 rows
Any thoughts?
Jorge3921
Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.
I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)
CREATE TABLE T_FK (ID1 INT, ID2 INT)
And I want to do:
ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)
I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.
Thank you.
Have a nice day.
my table :
CREATE TABLE [dbo].[users] (
[ID] [int] NOT NULL ,
[A1] [nvarchar] (100) NULL ,
[A2] [nvarchar] (100) NULL ,
[A3] [nvarchar] (100) NULL
) ON [PRIMARY]
i must keep ID columns as primary key
ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
but now A1+A2 must be unique
how can i do it ?
thank you
hi guys,
i have 3 tables (T1, T2, T3), each with the same structure:
ID1 -> char(10)
ID2 -> char(12) NULL
ID3 -> char(10)
Value1 -> money
Value2 -> money
Value3 -> money
Note1 -> Text
Note2 -> Text
ID1+ID2+ID3 is the clusterd unique key in each table
what i want:
ID1, ID2, ID3 (with distinct occurencies of all 3 tables), T1.Value2, T2.Value2, T2.Value3, T3.Value1, T3.Note1
what i tried is to get all possible rows with
SELECT ID1,ID2,ID3 FROM T1
UNION
SELECT ID1,ID2,ID3 FROM T2
UNION
SELECT ID1,ID2,ID3 FROM T3
but i dont know how to join or add the other columns. maybe with
WITH RowList (ID1,ID2,ID3) AS ( ... code above.... ) SELECT ...
any ideas?
thx
Can you create a unique constraint on multiple columns, or does it haveto be implemented as a unique index?If possible can someone please post some sample code?Thanks,
View 4 Replies View RelatedHi,I would like to add a unique index that consists of two fields in atable.e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combinationmust be Unique.Can anyone tell me the actual sql syntax to create this index?Thanks,June.
View 3 Replies View RelatedPerhaps one too many 2000 DTS packages have permanently damaged my ability to think clearly - however, I've find myself very frustrated attempting to create a SSIS Data Flow which replaces a very simple 2000 DTS package.
Take data from table1 in database1, put it in table2 in database2. Table2 in Database2 has an additional column as part of the primary key - so I need to add an arbitrary unique value in each row as it's inserted. Previously, I did this in the transformation script through a variable I incremented.
What's the recommend method to do this now - since row level processing of variables seem to be a no-no?