Combining Time with a Date

Let's say that I have two tables, one of which has a StartDate field (say,
TableA) as well as a foreign key reference to TableB which has a field
called BeginTime. Suppose that values in TableA.StartDate field have varying
dates together with a time of 00:00:00.000 (as it's a datetime field) and
TableB.BeginTime fields have varying time values (e.g. 07:00:00.000). NOTE:
TableB.BeginTime field is currently an nvarchar field.
What I want to do is compare the current date (using GETDATE()) to the
datetime value that results from combining TableA.StartDate with
TableB.BeginTime. For example, if TableA.StartDate = "03/29/2005
00:00:00.000", and TableB.BeginTime = "07:00:00.000", I want to compare the
current date to "03/29/2005 07:00:00.000". I will ultimately be trying to
determine if the difference between them is greater than a certain # of
minutes. How could I do this using SQL?Bob
Look at DATEDIFF system function.
"BobRoyAce" <bob@.decisioncritical.com> wrote in message
news:%23byP4xCNFHA.3844@.TK2MSFTNGP14.phx.gbl...
> Let's say that I have two tables, one of which has a StartDate field (say,
> TableA) as well as a foreign key reference to TableB which has a field
> called BeginTime. Suppose that values in TableA.StartDate field have
varying
> dates together with a time of 00:00:00.000 (as it's a datetime field) and
> TableB.BeginTime fields have varying time values (e.g. 07:00:00.000).
NOTE:
> TableB.BeginTime field is currently an nvarchar field.
> What I want to do is compare the current date (using GETDATE()) to the
> datetime value that results from combining TableA.StartDate with
> TableB.BeginTime. For example, if TableA.StartDate = "03/29/2005
> 00:00:00.000", and TableB.BeginTime = "07:00:00.000", I want to compare
the
> current date to "03/29/2005 07:00:00.000". I will ultimately be trying to
> determine if the difference between them is greater than a certain # of
> minutes. How could I do this using SQL?
>|||I am familiar with the DATEDIFF function, but that will not combine separate
DATE and TIME values together to give me a DATETIME. That's the piece I'm
missing here.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u1zyYHDNFHA.3076@.tk2msftngp13.phx.gbl...
> Bob
> Look at DATEDIFF system function.
>
> "BobRoyAce" <bob@.decisioncritical.com> wrote in message
> news:%23byP4xCNFHA.3844@.TK2MSFTNGP14.phx.gbl...
> varying
> NOTE:
> the
>|||Since you express datetime as a string, it is just a matter of building a st
ring expression which
can safely be converted to datetime. I didn't follow your first post, but le
ts assume that one value
is datetime and the other is a string:
DECLARE @.a datetime, @.b nvarchar(40)
SET @.a = getdate()
SET @.b = '07:00:00'
SELECT CAST(CONVERT(char(8), @.a, 112) + ' ' + @.b AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"BobRoyAce" <bob@.decisioncritical.com> wrote in message
news:%23mQ5VNDNFHA.244@.TK2MSFTNGP12.phx.gbl...
>I am familiar with the DATEDIFF function, but that will not combine separat
e DATE and TIME values
>together to give me a DATETIME. That's the piece I'm missing here.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:u1zyYHDNFHA.3076@.tk2
msftngp13.phx.gbl...
>|||What's the 112 for?|||>> What's the 112 for?
It is the argument for CONVERT to return the ISO format( yymmdd ) for dates
represented as a string. See the topic CAST and CONVERT in SQL Server Books
Online.
Anithsqlsql

Combining Row Data

I am adding data to a table from a table of identified duplicates.
These duplicates are not Key Violations, they are actual double entries
where one was deactivated prior to the new record being added (not very
clean but necessary given the system the data originates from). What I
would like to do is to take the data and combine some fields of the
duplicate rows.
I have created my mapping table (the table I want to add data to) which
looks like:
UniversalID (Identity)(PK)
Original_ID1 <-key from 1st row
Original_ID2 <-key from 2nd row
2ndSystemID
My data would look like this:
OriginalID Name 2ndSystemID
123 Smith, John KHGK39
124 Smith, John KHGK39
..
What I want is
UniversalID Original_ID1 Original_ID2 2ndSystemID
1 123 124 KHGK39
...
I know that this is doable, and I also know that I should know how, but
just can't seem to see the forest because all of the trees are in my
way.
Thanks for any help you can provide.
MTAnd if there are triplicates?
Don't try fixing old flaws by introducing new ones - such as breaking normal
form. I'd sugesst either deleting unwanted rows or adding a table to store
duplicate values of OriginalID referencing a proper primary key (e.g
2ndSystemID). Normalize now and prevent further difficulties.
ML
http://milambda.blogspot.com/|||Not really trying to break normal form. I need all of these values to
reference back to the original systems where the data lives. This is
why I need all of them. The UniversalID will be the new Key, the old
IDs, will become simple references.
I truly wish I could use only one, but unfortunately due to the nature
of the data I am dealing with, I am unable to and must find a work
around.
MT|||MT wrote:
> Not really trying to break normal form. I need all of these values to
> reference back to the original systems where the data lives. This is
> why I need all of them. The UniversalID will be the new Key, the old
> IDs, will become simple references.
> I truly wish I could use only one, but unfortunately due to the nature
> of the data I am dealing with, I am unable to and must find a work
> around.
> MT
Won't your proposal fail if there are more than 2 original ids? How
many do you expect to have to cope with?
Unfortunately you haven't given much information about keys. Here's a
guess at what I'd do:
CREATE TABLE users (universalid INTEGER PRIMARY KEY, name VARCHAR(50)
NOT NULL UNIQUE, systemid INTEGER NOT NULL);
CREATE TABLE original_ids (original_id INTEGER NOT NULL PRIMARY KEY,
universalid INTEGER NOT NULL REFERENCES users (universalid));
INSERT INTO users VALUES (1, 'Smith, John', 'KHGK39');
INSERT INTO orginal_ids VALUES (123,1);
INSERT INTO orginal_ids VALUES (124,1);
A join will map any number of original ids to the universal one.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Combining Data from MySql and SQL2005

Problem:
I have a MySql database that houses status information that is linked
by a primary key from a SQL2005 database
Using Report Designer in VS2005, I have created 3 datasets pointing to
the 2 datasources. I need to create a relationship between the MySql
based datasets and a dataset that is based on a SQL2005 datasource, so
that I can have all of the data available in one table...
MySQL Table1
DocID, JobID, FKeyToSQL
MySQL Table2
JobID,Status
SQL2005 Data
PKeyToMySQL,Description,etc.
I would like to be able to see a status report that shows...
PKeyToMySQL,Description,JobID,Status
Thanks for any help.Can you try adding MySQL database as a LinkedServer?
"Kevin.Ailes@.gmail.com" wrote:
> Problem:
> I have a MySql database that houses status information that is linked
> by a primary key from a SQL2005 database
> Using Report Designer in VS2005, I have created 3 datasets pointing to
> the 2 datasources. I need to create a relationship between the MySql
> based datasets and a dataset that is based on a SQL2005 datasource, so
> that I can have all of the data available in one table...
> MySQL Table1
> DocID, JobID, FKeyToSQL
> MySQL Table2
> JobID,Status
> SQL2005 Data
> PKeyToMySQL,Description,etc.
> I would like to be able to see a status report that shows...
> PKeyToMySQL,Description,JobID,Status
> Thanks for any help.
>|||Jaraba wrote:
> Can you try adding MySQL database as a LinkedServer?
>
> "Kevin.Ailes@.gmail.com" wrote:
> > Problem:
> > I have a MySql database that houses status information that is linked
> > by a primary key from a SQL2005 database
> >
> > Using Report Designer in VS2005, I have created 3 datasets pointing to
> > the 2 datasources. I need to create a relationship between the MySql
> > based datasets and a dataset that is based on a SQL2005 datasource, so
> > that I can have all of the data available in one table...
> >
> > MySQL Table1
> > DocID, JobID, FKeyToSQL
> > MySQL Table2
> > JobID,Status
> >
> > SQL2005 Data
> > PKeyToMySQL,Description,etc.
> >
> > I would like to be able to see a status report that shows...
> >
> > PKeyToMySQL,Description,JobID,Status
> >
> > Thanks for any help.
> >
> >
I was able to embed a sub-report and submit a parameter to it and it
seems to have accomplished what I wanted. It seems like a round about
way to do it though. I would have thought there would be some way to
build relationships between datasets since you can't combine data from
2 different datasources into 1 dataset. Oh well, now if I could just
get this sub-report to be centered in the column.(TextAlign=center on
column properties doesn't seem to be working)

Combined Primary Key - Why?

In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen>> If (b), then how do I ensure that a combination of ProductID and OrderID
In t-SQL, you'd use a NOT NULL UNIQUE CONSTRAINT on those values.
Anith|||I would normally go with ProductID, OrderID for primary key, as that is the
natural key. If I have to go with a surrogate key, then I'd make sure
there's a unique index/constraint on ProductID, OrderID combination.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen|||You can create a UNIQUE constraint on the combination of ProductID and
OrderID. Although my preference is to have a Primary Key on ProductID and
OrderID, and, if I think a single column (Identity) key is useful, to have
the UNIQUE constraint on the Identity column. In this scenario there is
usually not much need to have an Identity column on the ProductOrders table
though.
Jacco Schalkwijk
SQL Server MVP
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition
> for
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID
> are
> unique?
> Thanks,
> Naveen|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||I wasn't talking about the order of columns within the compound key - but
yes, that's a good point.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23NHEglEPFHA.2520@.tk2msftngp13.phx.gbl...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||There is only one key, (order_id, product_id) by definition. This is
basic RDBMS; a key must be made up of attributes that exist in the data
model. There is no ProductOrderID(mostly identity) in the real world;
it is derived from the internal state of the hardware at insertion
time:
CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Inventory(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
order_id INTEGER NOT NULL
REFERENCES Orders(order_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (order_id, product_id));
Newbies screw up and do it the other way. They can then get duplicates
and destroy the data integrity of the schema.|||This Product Orders table is just a OrderDetails, OrderItems or
InvoiceDetails table..
If you are going have any other tables "hanging off" this table, where you
will need to have a FK referring back to the ProductOrders (I'd call it
OrderDetails)
Then I'd go with b) strictly to avoid having to use composite FKs in other
table(s).
In that event, add another "Alternate" key (using Unique Index, or Unique
Constraint, on (OrderID, ProductID) to ensure uniqueness.
Regardless of whether you also have a single column surrogate key, make the
Composite key the Clustered Index, and if more queries will select data by
OrderID than by ProductID from this table, (THis is normally true for such
tables) use (OrderID, ProductID) as the sequence. But also put another inde
x
on ProductID by itself, for Joins back to Product Table
"Naveen" wrote:

> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition fo
r
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID a
re
> unique?
> Thanks,
> Naveensqlsql

Combined Index not using in SQL 7.0 SP4

Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binusqlsql

Combined Index not using in SQL 7.0 SP4

Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks Binu
The optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>
|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu

Combined Index not using in SQL 7.0 SP4

Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index no
t
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu

Columns with multiple values ?

Hi,
The values I need to store in the table are

Student ID
Student Name
Subjects

The "Student ID" is the primary key.

A student can take more than 1 subject.

For example:
Student ID: 100
Student Name: Kelly Preston
Subjects: Geography, History, Math

How can I store these values in a database table?
I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?

My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.

Or I can create a new field called "RowNumber" and keep that the primary key..
For example:

Row Number StudentID StudentName Subject
1 100 Kelly Geography
2 100 Kelly History
3 100 Kelly Math

If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?...If this is the only way to store the multiple sibjects...It isn't. I'm afraid that your design is really flawed. You would want to be certain that your design is correct before you even start thinking about SQL.

Have a read of this and then come back and let us know what you think your design should now be
http://r937.com/relational.html

EDIT: Oops - first post - welcome to the forum :D

HTH|||Pay particular attention to the section on many-to-many relationships, which I think is applicable to your situation.|||Table # 1: Student Details
---------
Student ID (primary key)
Student Name

Table # 2: Subjects
-------
RowID (primary key)
Student ID
Subject

For example:
We have 2 students, Kelly and Mary.
Kelly's subjects are History and Math
Mary's subjects are Math and English

Table # 1
----
Student ID Student Name
100 Kelly
200 Mary

Table # 2:
----
RowID StudentID Subject
1 100 History
2 100 Math
3 200 Math
4 200 English

When I want to display Kelly's details:
select * from 'Student Details' where StudentID = '100' UNION select * from Subjects where StudentID = '100'

The SQL query might not be of the right syntax, but is this idea right?

I believe this is a one-to-many relationship..|||No. You do not need a UNION query to do this, and it won't even work unless the designs of the two tables are identical.

Again, your database design is flawed. Please read the section on how to create many-to-many relationships.|||The SQL query might not be of the right syntax, but is this idea right?You've got a few details left to resolve, but you've definitely got the right idea. You want to use a JOIN instead of a UNION, but you're headed the right way now.

-PatP

Columns IDENTITY property

Hi all.
I have inherited some table full of data. It has a primary key [int]
clustered on one column. But this column was not created with IDENTITY. Now
I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
loosing the data but can't figure out the syntax of proper script :( I know
that EM allows you to do just that but I need a script).
Could anyone help me with that, please? Is it possible at all? Books Online
doc is not clear about this situation.
Thanks!!Here is the script that EM generates to add Identity to a column:
As you can see, you must create a new table,
copy the data from the old table to the new,
drop the old table and rename the new one.
This is a very simple example.
For a table with constraints, triggers, foreign keys etc., the script gets
longer.
On another table I have, the same operation generates about 300 lines of
code.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_numbers
(
number int NOT NULL,
test int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_numbers ON
GO
IF EXISTS(SELECT * FROM dbo.numbers)
EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
SELECT number, test FROM dbo.numbers TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_numbers OFF
GO
DROP TABLE dbo.numbers
GO
EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
GO
COMMIT
"Kikoz" <kikoz@.hotmail.com> wrote in message
news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY.
Now
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I
know
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books
Online
> doc is not clear about this situation.
> Thanks!!
>
>|||You can not do it using ALTER TABLE. Set the identity property in EM and
click "Save change script" button (third from left to rigth).
AMB
"Kikoz" wrote:

> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY. No
w
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I kno
w
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books Onlin
e
> doc is not clear about this situation.
> Thanks!!
>
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>

Column Name in a Primary key constraint

Hi all

Would there be a easy way to find the column name(s) which constitute
a Primary constraint for a table through navigating the system
catalogs.

I found that the PK Constraint object in syscontraints is showing the
colid = 0.

TIA
Norman"Norman Leung" <normanl@.interlog.com> wrote in message
news:3d8f97f8.0312031321.685fd5b7@.posting.google.c om...
> Hi all
> Would there be a easy way to find the column name(s) which constitute
> a Primary constraint for a table through navigating the system
> catalogs.
> I found that the PK Constraint object in syscontraints is showing the
> colid = 0.

colid = 0 indicates that it's a composite PK.

To find the columns of the PK, just use the system stored procedure
"sp_pkeys", for example,

sp_pkeys sometablename

However, if you insist on using the system tables to do this, have a look at
the source code for the sp_pkeys procedure. Apparently, you need to join
the syscolumns table with the sysindexes table, filter with a 0x800 mask (I
assume this selects for a primary key index), then select for columns based
on column name using the procedure index_col().

- Dave

...
from
sysindexes i, syscolumns c, sysobjects o
where
o.id = @.table_id
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@.full_table_name, i.indid, c1.colid)
and (c.name = index_col (@.full_table_name, i.indid, 1) or
c.name = index_col (@.full_table_name, i.indid, 2) or
c.name = index_col (@.full_table_name, i.indid, 3) or
c.name = index_col (@.full_table_name, i.indid, 4) or
c.name = index_col (@.full_table_name, i.indid, 5) or
c.name = index_col (@.full_table_name, i.indid, 6) or
c.name = index_col (@.full_table_name, i.indid, 7) or
c.name = index_col (@.full_table_name, i.indid, 8) or
c.name = index_col (@.full_table_name, i.indid, 9) or
c.name = index_col (@.full_table_name, i.indid, 10) or
c.name = index_col (@.full_table_name, i.indid, 11) or
c.name = index_col (@.full_table_name, i.indid, 12) or
c.name = index_col (@.full_table_name, i.indid, 13) or
c.name = index_col (@.full_table_name, i.indid, 14) or
c.name = index_col (@.full_table_name, i.indid, 15) or
c.name = index_col (@.full_table_name, i.indid, 16)
)

> TIA
> Norman|||normanl@.interlog.com (Norman Leung) wrote in message news:<3d8f97f8.0312031321.685fd5b7@.posting.google.com>...
> Hi all
> Would there be a easy way to find the column name(s) which constitute
> a Primary constraint for a table through navigating the system
> catalogs.
> I found that the PK Constraint object in syscontraints is showing the
> colid = 0.
> TIA
> Norman

SELECT
KCU.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON KCU.TABLE_NAME = CCU.TABLE_NAME AND
KCU.COLUMN_NAME = CCU.COLUMN_NAME

JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE
KCU.TABLE_NAME = 'MyTable' AND
TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

Simon

Column is constrained to be unique. Value 123 already

I'm using sqlDataAdpater to call a stored procedure for insert
statements.
The stored procedure inserts into all columns except primary key, which
it then selects back at end.
The program does a number of these inserts and then reads back from
database, this all works okay. Later when I go to do more inserts I
get the error message
Column is constrained to be unique. Value 123 already
Below is the stored procedure.
Any help would be greatly appreciated.
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int =null output, @.SelectionOrig_ID int =null,
@.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
=null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
=null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
@.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
)
as
insert into [dbo].[Selection]
(
SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
string2, string3, string4, string5, string6, string7, Hidden, NameLong,
Team_ID, Percentage
)
values
(
@.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
@.Hidden, @.NameLong, @.Team_ID, @.Percentage
)
select * From Selection Where Selection_ID = @.@.IDENTITY
GOChange to this:
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- --
select @.Selection_ID Selection_ID
Return(0)
-- ---
but also consider the following, you can use the SP to do both Inserts and
Updates, switching based on whether or not you pass in a null or non-null
@.Selection_ID as follows
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
-- ---
This has the benefiy of ONLY Updating the columns for which you actually
pass non-null values to the SP.
"dermot" wrote:

> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>
"dermot" wrote:

> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Missed one word in Raiserror statement
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has deleted or removed Selection %d.',
16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
"CBretana" wrote:
> Change to this:
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- --
> select @.Selection_ID Selection_ID
> Return(0)
> -- ---
> but also consider the following, you can use the SP to do both Inserts and
> Updates, switching based on whether or not you pass in a null or non-null
> @.Selection_ID as follows
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> If @.Selection_ID Is Null
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- ---
> Else If Exists (Select * From [dbo].[Selection]
> Where Selection_ID = @.Selection_ID)
> Update [dbo].[Selection] Set
> SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
> SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
> Event_ID = IsNull(@.Event_ID, Event_ID),
> Name = IsNull(@.Name, Name),
> Type = IsNull(@.Type, Type),
> Odd = IsNull(@.Odd, Odd)
> string1 = IsNull(@.string1, string1),
> string2 = IsNull(@.string2, string2),
> string3 = IsNull(@.string3, string3),
> string4 = IsNull(@.string4, string4),
> string5 = IsNull(@.string5, string5),
> string6 = IsNull(@.string6, string6),
> string7 = IsNull(@.string7, string7),
> Hidden = IsNull(@.Hidden, Hidden),
> NameLong = IsNull(@.NameLong, NameLong),
> Team_ID = IsNull(@.Team_ID, Team_ID),
> Percentage = IsNull(@.Percentage, Percentage)
> Where Selection_ID = @.Selection_ID
> -- ---
> Else
> Begin
> Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
> Return(-1)
> End
>
> Select @.Selection_ID Selection_ID
>
> Return(0)
> -- ---
> This has the benefiy of ONLY Updating the columns for which you actually
> pass non-null values to the SP.
> "dermot" wrote:
>
> "dermot" wrote:
>|||Script out the entire table if you could, just to make sure. I assume there
is no triggers, and that you know where the value 123 comes from?
If you can repeat the operation, use profiler and capture a trace of it
failing. Post that and we can see.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"dermot" <dfrench@.tommyfrench.co.uk> wrote in message
news:1109971489.057346.206560@.l41g2000cwc.googlegroups.com...
> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.