Help with simple insert, how to use primary key?

Ive added a primary key called ID to my table, now my insert stored procedure dont no longer work.

i want an unique identifier for each row.

heres my stored procedure:

CREATE PROCEDURE composeMessage

-- Add the parameters for the stored procedure here

@.username varchar(24),

@.sender varchar(24),

@.date dateTime,

@.subject varchar(255),

@.message varchar(2500)

AS

BEGIN

insert into Messages(

"Username",

"Sender",

"Date",

"Subject",

"Message"

)

values (

@.username,

@.sender,

@.date,

@.subject,

@.message

)

END

GO

heres my sqlcreate table:

USE [Messenger]

GO

/****** Object: Table [dbo].[Messages] Script Date: 09/12/2006 15:13:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Messages](

[Username] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,

[Sender] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,

[Subject] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,

[Message] [varchar](2500) COLLATE Latin1_General_CI_AS NOT NULL,

[Date] [datetime] NOT NULL,

[ID] [int] NOT NULL,

CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

As primary keycan't be null, what do i put for primary key for my insert to work?

hope you understand what i mean?

Am i right that i have to set the table designer/identity column to my primary key?

It generates an unique incresing number, so doi i use that?

|||

If you want to have an increasing value, you will have to switch on the identity property on your column.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

Help with primary keys

I have two tables with similar primary keys, table a and table b, and I want to find out all the key values that are disimilar between the tables. Can this be done with a select? if so what would it be.

If I understand the problem correctly one way to do this is to use a FULL JOIN; maybe something like:

declare @.tableA table (aKey int)
declare @.tableB table (bKey int)

insert into @.tableA
select 1 union all select 2 union all select 3 union all
select 5 union all select 6 union all select 8

insert into @.tableB
select 1 union all select 3 union all select 4 union all
select 6 union all select 7 union all select 8

select coalesce (aKey, bKey) as [Key],
case when aKey is null then 'Table B' else 'Table A'
end as sourceTable
from @.tableA
full join @.tableB
on aKey = bKey
where aKey is null
or bKey is null
order by coalesce (aKey, bKey)

/*
Key sourceTable
-- --
2 Table A
4 Table B
5 Table A
7 Table B
*/

Now that I think about it, a better way to do this is probably to do this differently; hang on and I'll get you a better method. This might perform a little better:

declare @.tableA table (aKey int)
declare @.tableB table (bKey int)

insert into @.tableA
select 1 union all select 2 union all select 3 union all
select 5 union all select 6 union all select 8

insert into @.tableB
select 1 union all select 3 union all select 4 union all
select 6 union all select 7 union all select 8

select 'TableA' as SourceTable,
aKey as [Key]
from @.tableA a
where not exists
( select 0 from @.tableB b
where aKey = bKey
)
union all
select 'TableB' as SourceTable,
bKey as [Key]
from @.tableB a
where not exists
( select 0 from @.tableA b
where aKey = bKey
)
order by [Key]

/*
SourceTable Key
-- --
TableA 2
TableB 4
TableA 5
TableB 7
*/

|||

If I understand you correctly, you want to find rows in TableA that do not exist in TableB, and conversely, rows in TableB that do not exist in TableA.

Code Snippet

SELECT
'TableA',
PKColumn
FROM TABLEA
WHERE PKColumn NOT IN ( SELECT PKColumn
FROM TableB
)
UNION

SELECT
'TableB',
PKColumn
FROM TABLEB
WHERE PKColumn NOT IN ( SELECT PKColumn
FROM TableA
)

If you are using SQL 2005, you could use a EXCEPT JOIN.

|||As usual, Arnie makes good points. Be aware of the EXCEPT join. In many cases it will be slower than the query Arnie put together. I try to avoid using the EXCEPT join.|||

Our (Kent's and mine) suggested solutions are virtually identical -since you are seeking PKeys, there are no duplicates -so DISTINCT is not necessary. And the query processor has to read the entire index anyway, so EXISTS and NOT IN have the same effect for this circumstance.

And if you are using SQL 2005, you could use a FULL OUTER JOIN. Example:

Code Snippet

SET NOCOUNT ON

DECLARE @.TableA table
( PKColumn int )

DECLARE @.Tableb table
( PKColumn int )

INSERT INTO @.TableA VALUES ( 1 )
INSERT INTO @.TableA VALUES ( 2 )
INSERT INTO @.TableA VALUES ( 3 )
INSERT INTO @.TableA VALUES ( 4 )
INSERT INTO @.TableA VALUES ( 5 )

INSERT INTO @.TableB VALUES ( 3 )
INSERT INTO @.TableB VALUES ( 4 )
INSERT INTO @.TableB VALUES ( 5 )
INSERT INTO @.TableB VALUES ( 6 )
INSERT INTO @.TableB VALUES ( 7 )

SELECT
'TableA' = a.PKColumn,
'TableB' = b.PkColumn
FROM @.TableA a
FULL OUTER JOIN @.TableB b
ON a.PKColumn = b.PKColumn
WHERE ( a.PKColumn IS NULL
OR b.PKColumn IS NULL
)
ORDER BY ( isnull( a.PKColumn, 0 ) + isnull( b.PKColumn, 0 ))

TableA TableB
-- --
1 NULL
2 NULL
NULL 6
NULL 7

IF this is a regular process, you might wish to check which of the possiblities is most efficient. (I'm betting on the FOJ.)

Help with Normalizing table

I am upgrading an Access DB to SQL Server. I have a table in the database
that looks like this,
tblContacts
RegNo - Primary key
Name
Relationship
Phone
WorkPhone
Name2
Relationship2
Phone2
WorkPhone2
Name3
Relationship3
Phone3
WorkPhone3
Name4
Relationship4
Phone4
WorkPhone4
Name5
Relationship5
Phone5
WorkPhone5
Name6
Relationship6
Phone6
WorkPhone6
I have made a new table, Contacts that looks like this,
ContactID - autoincrementing INT
RegNo
Name
Relationship
Phone
WorkPhone
How can I go about getting the data from tblContacts to Contacts?
Thanks,
DrewNow that I think about it... my subject is off somewhat... I have already
normalized the table, I just need to get the data in there now.
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
>I am upgrading an Access DB to SQL Server. I have a table in the database
>that looks like this,
> tblContacts
> RegNo - Primary key
> Name
> Relationship
> Phone
> WorkPhone
> Name2
> Relationship2
> Phone2
> WorkPhone2
> Name3
> Relationship3
> Phone3
> WorkPhone3
> Name4
> Relationship4
> Phone4
> WorkPhone4
> Name5
> Relationship5
> Phone5
> WorkPhone5
> Name6
> Relationship6
> Phone6
> WorkPhone6
> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
> Thanks,
> Drew
>|||Use six "select" statements (one for each group) connected by "union all".
insert into Contacts (RegNo, Name, Relationship, Phone, WorkPhone)
select RegNo, Name, Relationship, Phone, WorkPhone
from tblContacts
union all
select RegNo, Name2, Relationship2, Phone2, WorkPhone2
from tblContacts
where
Name2 is not null
and Relationship2 is not null
from tblContacts
union all
select RegNo, Name3, Relationship3, Phone3, WorkPhone3
from tblContacts
where
Name3 is not null
and Relationship3 is not null
from tblContacts
...
AMB
"Drew" wrote:

> I am upgrading an Access DB to SQL Server. I have a table in the database
> that looks like this,
> tblContacts
> RegNo - Primary key
> Name
> Relationship
> Phone
> WorkPhone
> Name2
> Relationship2
> Phone2
> WorkPhone2
> Name3
> Relationship3
> Phone3
> WorkPhone3
> Name4
> Relationship4
> Phone4
> WorkPhone4
> Name5
> Relationship5
> Phone5
> WorkPhone5
> Name6
> Relationship6
> Phone6
> WorkPhone6
> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
> Thanks,
> Drew
>
>|||Drew
CREATE TABLE People
(
PeopleID NOT NULL PRIMARY KEY,
[Name] VARCHAR(50) NOT NULL,
Address VARCHAR(100) NOT NULL,
Birthdate DATETIME NOT NULL
)
What does relatioship column mean?
CREATE TABLE Contacts
(
People_Cont NOT NULL PRIMARY KEY,
Contact_Name VARCHAR(50) NOT NULL,
Email VARCHAR(20) NULL
)
If the relatioship between these tables are many-to many so create a
"JUNCTION" table which will contain People_ID and People_Cont as Primary
KEY ( since I don't know your business reqirements , I can only guess)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
>I am upgrading an Access DB to SQL Server. I have a table in the database
>that looks like this,
> tblContacts
> RegNo - Primary key
> Name
> Relationship
> Phone
> WorkPhone
> Name2
> Relationship2
> Phone2
> WorkPhone2
> Name3
> Relationship3
> Phone3
> WorkPhone3
> Name4
> Relationship4
> Phone4
> WorkPhone4
> Name5
> Relationship5
> Phone5
> WorkPhone5
> Name6
> Relationship6
> Phone6
> WorkPhone6
> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
> Thanks,
> Drew
>|||Uri,
The table is used to hold contact information for our clients. The
relationship column holds the relationship of the client to the contact.
I.E. Mother, Father, Brother, Guardian, etc.
Thanks,
Drew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:erW5Lhj2FHA.3600@.TK2MSFTNGP12.phx.gbl...
> Drew
> CREATE TABLE People
> (
> PeopleID NOT NULL PRIMARY KEY,
> [Name] VARCHAR(50) NOT NULL,
> Address VARCHAR(100) NOT NULL,
> Birthdate DATETIME NOT NULL
> )
>
> What does relatioship column mean?
>
> CREATE TABLE Contacts
> (
> People_Cont NOT NULL PRIMARY KEY,
> Contact_Name VARCHAR(50) NOT NULL,
> Email VARCHAR(20) NULL
> )
> If the relatioship between these tables are many-to many so create a
> "JUNCTION" table which will contain People_ID and People_Cont as Primary
> KEY ( since I don't know your business reqirements , I can only guess)
>
>
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
>|||One popular approach is to do:
SELECT RegNo, Name,
CASE n WHEN 1 THEN Relationship
WHEN 2 THEN Relationship2
..
WHEN 6 THEN Relationship6
END AS "Relationship",
CASE n WHEN 1 THEN Phone
WHEN 2 THEN Phone2
..
WHEN 6 THEN Phone6
END AS "Phone",
CASE n WHEN 1 THEN WorkPhone
WHEN 2 THEN WorkPhone2
..
WHEN 6 THEN WorkPhone6
END AS "WorkPhone"
FROM tbl, ( SELECT 1 UNION
SELECT 2 UNION
..
SELECT 6 ) D ( n ) ;
After thoroughly reviewing your data model, you might want to decide whether
an additional identifier is required.
Anith|||It worked the charm! Thanks a bunch for your input!
I did have to make some changes,
insert into Contacts (RegNo, Name, Relationship, Phone, WorkPhone)
select RegNo, Name, Relationship, Phone, WorkPhone
from tblContacts
union all
select RegNo, Name2, Relationship2, Phone2, WorkPhone2
from tblContacts
where
Name2 is not null
and Relationship2 is not null
union all
select RegNo, Name3, Relationship3, Phone3, WorkPhone3
from tblContacts
where
Name3 is not null
and Relationship3 is not null
...
Thanks,
Drew
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2C717449-94D9-4A44-9943-F895BB991247@.microsoft.com...
> Use six "select" statements (one for each group) connected by "union all".
> insert into Contacts (RegNo, Name, Relationship, Phone, WorkPhone)
> select RegNo, Name, Relationship, Phone, WorkPhone
> from tblContacts
> union all
> select RegNo, Name2, Relationship2, Phone2, WorkPhone2
> from tblContacts
> where
> Name2 is not null
> and Relationship2 is not null
> from tblContacts
> union all
> select RegNo, Name3, Relationship3, Phone3, WorkPhone3
> from tblContacts
> where
> Name3 is not null
> and Relationship3 is not null
> from tblContacts
> ...
>
> AMB
>
> "Drew" wrote:
>|||"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
> I am upgrading an Access DB to SQL Server. I have a table in the
database
> that looks like this,
<snip>

> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
>
Drew,
The above table has both Phone and WorkPhone columns. This is not
1NF, as this represents repeating columns for the same type of
information (a phone number).
Contacts
ContactID
RegNo
Name
Relationship
Phones
PhoneID
ContactID
PhoneNumber
PhoneType
Sincerely,
Chris O.

Help with multi Join or multi tier select.

Hello,

I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation are the three primary tables
the other tables are lookup tables for values the other tables.
PlanID is the primary in Plan and

PlanProviderProviderLocationLookups
--------------
PlanIDProviderIDProviderIDLookupType
PlanNamePlanIDProviderStatusLookupKey
RegionIDLastName...LookupValue
...FirstName...

Given a PlanID I want all the Providers with a ProviderStatus = 0

I can get the query to work just fine if there are records but what I
want is if there are no records then I at least want one record with
the Plan information. Here is a sample of the Query:

SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
pl.InvalidData
FROM Plans pln
INNER JOIN Lookups l3 ON l3.LookupType = 'REGN'
AND pln.RegionID = l3.Lookupkey
left outer JOIN Provider p ON pln.PlanID = p.PlanID
left outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
left outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'
AND pl.ReasonMain = l1.LookupKey
left outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'
AND pl.ReasonSub = l2.Lookupkey
WHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum

I know the problew the ProviderStatus on the Where clause is keeping
any records from being returned but I'm not good enough at this to
another select.

Can anybody give me some suggestions?

Thanks

DavidTry moving the predicate "AND PL.providerstatus = 0" into the ON clause:

FROM Plans AS PLN
INNER JOIN Lookups L3
ON L3.LookupType = 'REGN'
AND PLN.regionid = L3.lookupkey
LEFT OUTER JOIN Provider AS P
ON PLN.planid = P.planid
LEFT OUTER JOIN ProviderLocation AS PL
ON P.providerid = PL.providerid
AND PL.providerstatus = 0
LEFT OUTER JOIN Lookups AS L1
ON L1.lookuptype = 'PLRM'
AND PL.reasonmain = L1.lookupkey
LEFT OUTER JOIN Lookups AS L2
ON L2.lookuptype = 'PLX1'
AND PL.reasonsub = L2.lookupkey
WHERE PLN.planid = '123456789'

--
David Portas
SQL Server MVP
--|||No that didn't work becase then it all the providers ... and I think
only the locations with with providerstatus = 0.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<RpOdnffKDN6GPVfdRVn-sQ@.giganews.com>...
> Try moving the predicate "AND PL.providerstatus = 0" into the ON clause:
> FROM Plans AS PLN
> INNER JOIN Lookups L3
> ON L3.LookupType = 'REGN'
> AND PLN.regionid = L3.lookupkey
> LEFT OUTER JOIN Provider AS P
> ON PLN.planid = P.planid
> LEFT OUTER JOIN ProviderLocation AS PL
> ON P.providerid = PL.providerid
> AND PL.providerstatus = 0
> LEFT OUTER JOIN Lookups AS L1
> ON L1.lookuptype = 'PLRM'
> AND PL.reasonmain = L1.lookupkey
> LEFT OUTER JOIN Lookups AS L2
> ON L2.lookuptype = 'PLX1'
> AND PL.reasonsub = L2.lookupkey
> WHERE PLN.planid = '123456789'|||I understood that you wanted to include rows from Plans which didn't have
corresponding rows from ProviderLocation - in which case they won't have a
ProviderStatus. It may be easier to understand your requirements if you post
DDL, sample data INSERTs and show your required result based on that sample
data. (http://www.aspfaq.com/5006)

--
David Portas
SQL Server MVP
--|||>> I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation are the three primary tables
the other tables are lookup tables for values the other tables. <<

Mind posting some DDL? When see data element names as poorily written
as "LookupType", "LookupKey" and "LookupValue", it is a pretty sure
sign that the basic schema design is wrong. To be is to be something
in particular and those names imply that you have a "One True Lookup
Table" (OTLT) flaw. Yes, like many diseases or disasters, it is
common enough to have a name! Google it; I wrote a column on it in
INTELLIGENT ENTERPRISE magazine.|||[posted and mailed, please reply in news]

David Logan (ibflyfishin@.yahoo.com) writes:
> I can get the query to work just fine if there are records but what I
> want is if there are no records then I at least want one record with
> the Plan information. Here is a sample of the Query:
> SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
> p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
> pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
> l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
> pl.InvalidData
> FROM Plans pln
> INNER JOIN Lookups l3 ON l3.LookupType = 'REGN'
> AND pln.RegionID = l3.Lookupkey
> left outer JOIN Provider p ON pln.PlanID = p.PlanID
> left outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
> left outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'
> AND pl.ReasonMain = l1.LookupKey
> left outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'
> AND pl.ReasonSub = l2.Lookupkey
> WHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0
> ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum
> I know the problew the ProviderStatus on the Where clause is keeping
> any records from being returned but I'm not good enough at this to
> another select.

As David said, it is always a good idea to include CREATE TABLE and
sample data. But I think I have a guess what will work for you:

SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
pl.InvalidData
FROM Plans pln
JOIN Lookups l3 ON l3.LookupType = 'REGN'
AND pln.RegionID = l3.Lookupkey
LEFT JOIN (Provider p
JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
AND pl.ProviderStatus = 0
JOIN Lookups l1 ON l1.LookupType = 'PLRM'
AND pl.ReasonMain = l1.LookupKey
JOIN Lookups l2 ON l2.LookupType = 'PLX1'
AND pl.ReasonSub = l2.Lookupkey)
ON pln.PlanID = p.PlanID
WHERE pln.PlanID = '123456789'
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum

The point here is that the thing in parathensis is sort of a logical
table, and you make an outer-join to that logical table.

This is the normal way of doing things when you want to join a
left-joined table with a lookup table (should not be necessary to
left-join the lookup table). In this case it also necessary, to
exclude providers which does not have any location with status = 0.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

help with integer field

hi all,

i have an autonumber field (primary key) and another integer field as part of a table. What i want to do is when a record is created, the default value of the integer field should be the_autonumber+1000 for eg record with pk 82 will have an integer field that's automatically 1082. Would it be possible to do this ? Thanks in advance.Hi, try this trigger...

CREATE TRIGGER trig_MyTableAddTrigger
ON MyTable
FOR INSERT
AS
-- Get the value to put in the second column
DECLARE @.NewValue integer
SELECT @.NewValue = (SELECT MyTableID FROM Inserted) + 1000

-- Update the same table [MyTable] with new value
UPDATE [MyTable] SET SecondColumName = @.NewValue
WHERE MyTableID = (SELECT MyTableID FROM INSERTED)|||thank you very,very much for your help.

Help with INSERT query

I need to append records into a table with a two column primary key from a table that contains many records that already exist in the target table. How do I separate out the records in the source table that don't exist in the target?

When I used to do this in Access, I could write a simple append query that would automatically skip records in source that violated key constraints in the target. I'm trying to duplicate that funcionality.

Thanks.

INSERT INTO Target (field1, field2)
SELECT
field1, field2
FROM Source
WHERE Source.field NOT IN (SELECT Field FROM TARGET)

If you want to do a multi-field check...

INSERT INTO Target (field1, field2)
SELECT
field1, field2
FROM Source
LEFT OUTER JOIN Target
ON Target.field = Source.Field
WHERE Target.field IS NULL

|||

Thanks so much for the quick reply.

I need to check both Source.field1 and Source.field2 against their counterparts in Target since it's the combination that provides the primary key in Target.

What I've tried that gets me a duplicate key error is

INSERT INTO Target (field1, field2)
SELECT field1, field2
FROM Source
LEFT OUTER JOIN Target
ON Target.field1 = Source.Field1 AND Target.field2 = Source.Field2
WHERE Target.field1 IS NULL AND Target.field2 IS NULL

It looks like it should work, but it doesn't.

Kato

|||

In your where clause, all you need to do is check the nullability of one field from the Target table. If the join fails, every field in your left table will be null.

If you get a duplicate key error, try using a SELECT DISTINCT instead of just a select.

If you still get the error, what's your primary key / unique index on in Target?