Insert into... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert into...

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-08-20 : 16:44:41

I have the following:

insert into RS_CustomerID
(accountid, accountname, accountopendate,accountzipcode,accountbankid)
select accountid, accountname, accountopendate, accountzipcode, accountbankid from RS_CustomerID_TEMP where AccountID not in (select accountid from RS_Customerid)

The problem is I can have the same account number in the list twice, but each would unique bankid's I would like to do something like this, but am not sure how to craft it:

insert into RS_CustomerID
(accountid, accountname, accountopendate,accountzipcode,accountbankid)
select accountid, accountname, accountopendate, accountzipcode, accountbankid from RS_CustomerID_TEMP where AccountID AND [accountbankid] not in (select accountid,accountbankid from RS_Customerid)

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-08-20 : 16:57:03

quote:

Originally posted by duhaas

I have the following:

insert into RS_CustomerID
(accountid, accountname, accountopendate,accountzipcode,accountbankid)
select accountid, accountname, accountopendate, accountzipcode, accountbankid from RS_CustomerID_TEMP where AccountID not in (select accountid from RS_Customerid)

The problem is I can have the same account number in the list twice, but each would unique bankid's I would like to do something like this, but am not sure how to craft it:

insert into RS_CustomerID
(accountid, accountname, accountopendate,accountzipcode,accountbankid)
select accountid, accountname, accountopendate, accountzipcode, accountbankid from RS_CustomerID_TEMP where AccountID AND [accountbankid] not in (select accountid,accountbankid from RS_Customerid)

I suppose I could do something like this???

select accountid + '-' + accountbankid AS test, accountname, accountopendate, accountzipcode from RS_CustomerID_TEMP 
where (accountid + '-' + accountbankid) not in (select accountid + '-' + accountbankid from RS_Customerid)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 17:13:16

[code]insert RS_CustomerID
(
accountid,
accountname,
accountopendate,
accountzipcode,
accountbankid
)
select accountid,
accountname,
accountopendate,
accountzipcode,
accountbankid
from RS_CustomerID_TEMP
WHERE NOT EXISTS (SELECT * FROM RS_Customerid WHERE RS_Customerid.AccountID = RS_CustomerID_TEMP.AccountID AND RS_Customerid.accountbankid = RS_CustomerID_TEMP.accountbankid)[/code]

E 12°55'05.25"
N 56°04'39.16"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-08-20 : 20:35:04

quote:

Originally posted by Peso

insert	RS_CustomerID
	(
		accountid,
		accountname,
		accountopendate,
		accountzipcode,
		accountbankid
	)
select	accountid,
	accountname,
	accountopendate,
	accountzipcode,
	accountbankid
from	RS_CustomerID_TEMP
WHERE	NOT EXISTS (SELECT * FROM RS_Customerid WHERE RS_Customerid.AccountID = RS_CustomerID_TEMP.AccountID AND RS_Customerid.accountbankid = RS_CustomerID_TEMP.accountbankid)

E 12°55'05.25"
N 56°04'39.16"

duha

Thanks for the advice, does a solution like mine pose any problems that you can think of?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 02:43:59

Yes. Most probably any present index can't be used.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources