July 18, 2002 at 2:41 pm
I want to write an insert statement that uses a select from another table.
both the table to be inserted into and the selection table contain the exact structure.
The selection table may contain the same values as the insert table, but I don't want to insert duplicates.
The insert table has a 3 column primary key. The whole ideal is to append to the insert table, the new values in the select table.
(i.e. how do I write a select statement to get disinct values based on a 3 key table from 2 identical tables?)
Thanks
July 18, 2002 at 2:52 pm
does this do it??
insert into srnntemp
SELECT srnntemp.*
FROM srnntemp where not exists
(select * from srnncopy where SRNNCOPY.ponum <> SRNNTEMP.ponum AND SRNNCOPY.poline <> SRNNTEMP.poline AND
SRNNCOPY.porelease <> SRNNTEMP.porelease);
July 18, 2002 at 5:58 pm
Don't think that will work, but when you say duplicates, I assume you mean duplicates outside the PK? Or does the original table not have a PK?
Steve Jones
July 18, 2002 at 7:07 pm
I created the following tables:
CREATE TABLE [dbo].[test] (
[col1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[col2] [int] NULL ,
[col3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[col4] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[test2] (
[col1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[col2] [int] NOT NULL ,
[col3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[col4] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
) ON [PRIMARY]
GO
and than put some data into test and executed the following query:
INSERT INTO test2 (col1,col2,col3)
SELECT col1,col2,col3
FROM test
WHERE col1 + STR(col2) + col3 NOT IN
(
SELECT col1 + STR(col2) + col3
FROM test2
)
Hopefully this is what you are looking for.
Please note that I used the STR function on col2 to convert that int column to a character type. Otherwise you'll get an error.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply