Help with insert query

TblA TblB
---
ColA int Identity ColB char(5)
ColB char(5) FK
ColC char(6)
ColD char(50)
I would like to add rows to TblA where TblA/ColB would be populated
from TblB and ColC and ColD would be literals for each row added. ColC
= 'XXXX' and ColD = 'Suspense'
ColB and ColC in TblA combine to make a referenced key. There are
already some rows which are in TblA.
For example,
INSERT INTO TblA
(ColB, ColC, ColD)
VALUES ((
SELECT ColB
FROM TblB
WHERE ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX'
)), 'XXXX', 'Suspense')
Is there any way to do this?
TIA Lars> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
>
INSERT INTO TblA(ColB, ColC, ColD)
SELECT ColB, 'XXXX', 'Suspense'
FROM TblB
WHERE ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX')
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Hope i understood you right:
Insert Into TblA
Select 'SomethingforA',
ColB,
'XXXX',
'Suspense'
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:df286192dm1u9468c1i6bqfkulc5pfmoh5@.
4ax.com...
> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
> Is there any way to do this?
> TIA Lars|||Try,
INSERT INTO TblA (ColB, ColC, ColD)
SELECT ColB, 'XXXX', 'Suspense'
FROM TblB
WHERE
ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX'
);
AMB
"larzeb" wrote:

> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
> Is there any way to do this?
> TIA Lars
>