Clash Royale CLAN TAG#URR8PPP

Multiple INSERT with same column (MS ACCESS)

I'm trying to append from table like this (DesignID, ink1, ink2, ink3, mesh1, mesh2, mesh3) to the new table (ID, DesignID, ink, mesh) in order to reduce repeating values... I'm quite new to to SQL and couldn't find an answer... I'm trying to figure out how I would do this in one query instead of running in 3 times...

So far I tried this:

INSERT INTO Ink ( DesignID, Mesh, Ink )
SELECT Design.DesignID, Design.Mesh1, Design.Ink1
FROM Design
WHERE (((Design.Ink1) Is Not Null))

UNION ALL

INSERT INTO Ink ( DesignID, Mesh, Ink )
SELECT Design.DesignID, Design.Mesh2, Design.Ink2
FROM Design
WHERE (((Design.Ink2) Is Not Null));

UNION ALL

INSERT INTO Ink ( DesignID, Mesh, Ink )
SELECT Design.DesignID, Design.Mesh3, Design.Ink3
FROM Design
WHERE (((Design.Ink3) Is Not Null));


Any help is highly appreciated!

3 Answers
3

Try using a SELECT ... INTO with a union of the two selects:

SELECT ... INTO

SELECT t.* INTO Ink
FROM
(
SELECT DesignID, Mesh1, Ink1
FROM Design
WHERE Ink1 IS NOT NULL
UNION ALL
SELECT DesignID, Mesh2, Ink2
FROM Design
WHERE Ink2 IS NOT NULL
) t;


A SELECT ... INTO statement will delete & recreate the destination table when executed, which may not be desired behaviour based on the INSERT INTO statements used in the question.

SELECT ... INTO

INSERT INTO

To avoid three separate INSERT INTO statements, I would suggest a single INSERT INTO statement which sources data from three unioned SELECT queries:

INSERT INTO

INSERT INTO

SELECT

INSERT INTO Ink ( DesignID, Mesh, Ink )
SELECT * FROM
(
SELECT DesignID, Mesh1, Ink1
FROM Design
WHERE Ink1 IS NOT NULL
UNION ALL
SELECT DesignID, Mesh2, Ink2
FROM Design
WHERE Ink2 IS NOT NULL
UNION ALL
SELECT DesignID, Mesh3, Ink3
FROM Design
WHERE Ink3 IS NOT NULL
)


Just to be clear for someone who is looking for the answer. Here is the correct query. As @Lee_Mac stated plus a little add:

INSERT INTO Ink ( DesignID, Mesh, Ink )
SELECT * FROM
(
SELECT DesignID, Mesh1 as Mesh, Ink1 as Ink
FROM Design
WHERE Ink1 IS NOT NULL
UNION ALL
SELECT DesignID, Mesh2, Ink2
FROM Design
WHERE Ink2 IS NOT NULL
UNION ALL
SELECT DesignID, Mesh3, Ink3
FROM Design
WHERE Ink3 IS NOT NULL
)


This will work and run with no problems. First SELECT should be selected as names of the fields you are inserting in to in order to run and work.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.