August 24, 2006 at 9:44 pm
What is the best way to do this?
Query 1 would not insert since AcctNo "aaa"
exists with a Code "x"
Query 2 would insert since AcctNo "bbb"
exists, but not with a Code "x"
MyTable:
AcctNo Code
aaa x
aaa z
bbb z
Query 1:
INSERT INTO MyTable
(AcctNo, Code)
SELECT
'aaa', 'y'
WHERE ??? MyTable.Code does not have a record = 'x'
Query 2:
INSERT INTO MyTable
(AcctNo, Code)
SELECT
'bbb', 'y'
WHERE ??? MyTable.Code does not have a record = 'x'
August 24, 2006 at 11:06 pm
This works, probably not best way to do this? Any suggestions would be appreciated.
INSERT INTO InsertIfNoMatch
(AcctNo, Code)
SELECT
AcctNo, Code
FROM InsertIfNoMatch_DATA
WHERE AcctNo <>
(SELECT AcctNo FROM InsertIfNoMatch
WHERE Code = 'z'
AND AcctNo = InsertIfNoMatch.AcctNo)
August 24, 2006 at 11:36 pm
Well it worked until I got more than a record or so in it. Knew it looked ugly, lol.
error:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
August 25, 2006 at 12:39 am
IF NOT EXISTS (SELECT * FROM MyTable WHERE AcctNo = 'aaa' and code = 'x')
INSERT INTO MyTable
(AcctNo, Code)
SELECT
'aaa', 'y'
IF NOT EXISTS (SELECT * FROM MyTable WHERE AcctNo = 'bbb' and code = 'x')
INSERT INTO MyTable
(AcctNo, Code)
SELECT
'bbb', 'y'
N 56°04'39.16"
E 12°55'05.25"
August 28, 2006 at 10:01 am
Set a unique index on those columns, and SQL Server will not allow duplicate values. If this column is the primary key, define it as such and SQL Server will enforce uniqueness. Your other choice is to create a stored procedure that checks to see if it exists, and inserts or updates depending on what is found. You could also do this with a Check constraint, but the stored proc method is the best by far.
August 28, 2006 at 10:47 am
declare @AcctNo varchar(10),@Code varchar(1)
set @AcctNo='aaa'
set @Code='y'
if not exists (select AcctNo,Code from myTable where AcctNo=@AcctNo and Code<>'x')
insert into MyTable (AcctNo,Code) values (@AcctNo,@Code)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply