SQL insert into

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

SQL insert into

Post 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?
MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL insert into

Post 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
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL insert into

Post 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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL insert into

Post by Tribble »

Told you he could help you
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: SQL insert into

Post 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
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL insert into

Post 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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: SQL insert into

Post by doo_much »

Sorted thanks - client did it themselves...
MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
Post Reply