Re: Insert into script

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 11/03/04 

Date: Wed, 3 Nov 2004 06:29:27 -0600

> Are you referring to the fields that I am inserting, i.e.
entitiycategorydatabaseid?

No.
I am referring to the columns within the primary key.

CREATE TABLE #test (col1 int not null, col2 int not null, AnotherColumn
varchar(10))

ALTER TABLE #test ADD PRIMARY KEY NONCLUSTERED (col1, col2)

INSERT INTO #test (col1, col2, AnotherColumn) VALUES (1,2,'test')
INSERT INTO #test (col1, col2, AnotherColumn) VALUES (1,3,'test 2')
INSERT INTO #test (col1, col2, AnotherColumn) VALUES (2,4,'testing')

--this fails
INSERT INTO #test (col1, col2, AnotherColumn) VALUES (2,4,'testing')
--however, if you make the primary key unique the insert will work
INSERT INTO #test (col1, col2, AnotherColumn) VALUES (3,4,'testing')

SELECT * FROM #test

/*it appears that you are trying to do something like this
it fails because you are trying to insert duplicate data*/
INSERT INTO #test (col1, col2, AnotherColumn)
SELECT col1, col2, AnotherColumn FROM #test WHERE col2 <> 4

/*You will be able to insert the data if you make sure that the values
within the primary key are unique*/
INSERT INTO #test (col1, col2, AnotherColumn)
SELECT 9, col2, AnotherColumn FROM #test WHERE col2 <> 4

SELECT * FROM #test 

-- 
Keith
"Sonya" <Sonya@discussions.microsoft.com> wrote in message
news:F6B89B7B-C526-4131-823F-28B16B0F646C@microsoft.com...
> Keith,
>
> Thank you again for being so helpful. Can you explain what you mean by "
> have to include each of the items within the primary key"? Are you
referring
> to the fields that I am inserting, i.e. entitiycategorydatabaseid?

Relevant Pages

  • This Inner join works in MS Access, but not in VB6?
    ... I have a select statement that works fine in MS Access, but fails in VB6, ... and a pointer to the primary key of the Per table. ... Here is the query that works in Access: ...
    (microsoft.public.vb.database.ado)
  • Re: inserting guid does not work when DBPROP_SERVERDATAONINSERT is set
    ... I can't tell if you've set up the GUID column as the PRIMARY KEY ... > This is the table (in MSSQL): ... > With Oracle the Openworks always, but the actual insert-statement fails ...
    (microsoft.public.data.oledb)
  • Re: Hidden error kills my transaction. Help!
    ... This is just "select from" what fails. ... Adding rows is made in stored procedures. ... proper guid value) ... Primary key on 3 fields. ...
    (microsoft.public.sqlserver.programming)
  • Sort based on the name selected under the combo box
    ... I have one form with a drop-down combo box in which I ... And this form is referring to the query and I selected the ... sort: ... My table has primary key and company ...
    (microsoft.public.access.forms)
  • Re: Next Issue - Setting the Order!!
    ... probably not based on a unique key. ... Master List primary key is a ID # & the ... Query Type is a "Select Query" ... If that fails, the relation between the 2 tables is probably not based on ...
    (microsoft.public.access.queries)