Page 1 of 1
SQL insert into
Posted: 27 May 2011, 18:38
by doo_much
Should be easy enough.
Trying to insert ID's from one table into another using the name of their ID photo as a reference. Two tables in an Access Database and I'm running the SQL from MSQuery. Which is all I've got available in this environment.
Thought this should work but I'm obviously messing up somewhere.
Code: Select all
Insert into CARD_DATA.ID
select CRDHLD.crdhld_ID
from CRDHLD
where CARD_DATA.CM_PHOTO = CRDHLD.photo
Any help please?
Re: SQL insert into
Posted: 27 May 2011, 18:56
by Tribble
PM RRF - he has been helping me. Jamin has also been very helpful. I think you need the table name in the insert statement - but what do I know?!
This is what I found:
INSERT INTO tblB(Field1, Field2,...)
SELECT Field1, Field2,...
FROM tbla
Re: SQL insert into
Posted: 28 May 2011, 14:24
by RuadRauFlessa
Correctamundo Tribble....
The general form of an insert statement is as follows
Code: Select all
INSERT INTO [TABLENAME]
[(COL1[,COL2[,COL3[...]]])]
VALUES
[(VALUE1[,VALUE2[,VALUE3[...]]])]
So what you are looking for is this
Code: Select all
INSERT INTO [CARD_DATA]
([CARD_DATA].[ID])
VALUES (
(SELECT [CRDHLD].[CRDHLD_ID] FROM [CRDHLD])
)
WHERE [CARD_DATA].[CM_PHOTO] = [CRDHLD].[PHOTO]
Do note that depending on the sub-query it might not allow you to use sub-queries in insert statements.
Re: SQL insert into
Posted: 28 May 2011, 14:44
by Tribble
Told you he could help you
Re: SQL insert into
Posted: 30 May 2011, 05:12
by hamin_aus
Code: Select all
Insert into CARD_DATA (ID)
select CRDHLD.crdhld_ID
from CRDHLD
where CARD_DATA.CM_PHOTO = CRDHLD.photo
I personally don't use VALUES in my insert statements unless I'm putting in values manually... personal preference, and so I have been told, not ideal
The sub query should be fine, but if it moans, use select into instead
Code: Select all
SELECT CRDHLD.crdhld_ID
INTO CARD_DATA.ID
FROM CRDHLD
WHERE CARD_DATA.CM_PHOTO = CRDHLD.photo
Re: SQL insert into
Posted: 30 May 2011, 06:20
by RuadRauFlessa
Hmmm ye coming to think about it... only time I have seen SQL complain about a sub-query an insert is when you use the values clause.
Re: SQL insert into
Posted: 31 May 2011, 11:15
by doo_much
Sorted thanks - client did it themselves...