How do I make a INNER JOIN inside a INSERT INTO statement?

Question

0.00/5 (No votes)

See more:

Hi
I need to make a INNER JOIN inside this statement how does I do that??

This is what I have tried so far?

When I send a e-mail to user I want to display Monday instead of 1, so thats why I have to INNER JOIN dage table.

SQL

CREATE TABLE [dbo].[ordre_linie] (
    [ordre_linie_id] INT          IDENTITY (1, 1) NOT NULL,
    [antal]          VARCHAR (50) NOT NULL,
    [navn]           VARCHAR (50) NOT NULL,
    [fk_ordre_id]    INT          NOT NULL,
    [fk_kurve_id]    INT          NOT NULL,
    [total_pris]     VARCHAR (50) NOT NULL,
    [fk_dag_id]      INT          NOT NULL,
    PRIMARY KEY CLUSTERED ([ordre_linie_id] ASC)
);

CREATE TABLE [dbo].[dage] (
    [dag_id]   INT          IDENTITY (1, 1) NOT NULL,
    [dag_navn] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([dag_id] ASC)
);



cmd.CommandText = @"INSERT INTO ordre_linie (SELECT * FROM ordre_linie INNER JOIN dage ON ordre_linie.fk_dag_id = dage.dag_id) 
                                (fk_ordre_id, navn, fk_kurve_id, antal, total_pris, fk_dag_id, dag_navn) 
                               VALUES (@fk_ordre, @navn, @kurv, @antal, @pris, @dag, @dag_navn)";

Hope someone could help me

/Tina

Posted 30-Oct-13 1:32am

tina_overgaard

Updated 20-Apr-20 1:03am

v2

Add a Solution

Comments

IpsitaMishra 30-Oct-13 7:40am

If u r asking for the foramt here is it

INSERT INTO Table3(Col1,col2....)
VALUES(SELECT Col1,Col2 ...
FROM table1
INNER JOIN tabl2
WHERE Condition)

if u want the exact solution please provide all the table structure.

tina_overgaard 30-Oct-13 8:11am

I have updated my question

tina_overgaard 30-Oct-13 8:15am

CREATE TABLE [dbo].[ordre_linie] (
[ordre_linie_id] INT IDENTITY (1, 1) NOT NULL,
[antal] VARCHAR (50) NOT NULL,
[navn] VARCHAR (50) NOT NULL,
[fk_ordre_id] INT NOT NULL,
[fk_kurve_id] INT NOT NULL,
[total_pris] VARCHAR (50) NOT NULL,
[fk_dag_id] INT NOT NULL,
PRIMARY KEY CLUSTERED ([ordre_linie_id] ASC)
);

CREATE TABLE [dbo].[dage] (
[dag_id] INT IDENTITY (1, 1) NOT NULL,
[dag_navn] VARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([dag_id] ASC)
);

thatraja 30-Oct-13 7:44am

what's the purpose? give us more details please

tina_overgaard 30-Oct-13 8:12am

When I send email I just send the value 1 instead of Monday

thatraja 30-Oct-13 8:35am

Share the sample table data, update your question.

thatraja 30-Oct-13 10:08am

Is dage is a master table like pre-defined values which will be used for ordre_linie?
And you're inserting values only in ordre_linie table, right?

[no name] 30-Oct-13 8:04am

Always use stored procedure in place of such parameterized queries..

tina_overgaard 30-Oct-13 8:11am

I have not learn to use stored procedures

IpsitaMishra 30-Oct-13 8:38am

please take a look on this http://www.c-sharpcorner.com/UploadFile/gtomar/storedprocedure12052007003126AM/storedprocedure.aspx

Member 14600877 5-Oct-19 3:52am

i want to create a table with id and date , if we enter the today date in date column then date column field fill automatically with the day name after entering the date , how to do this ? plz give me solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2013-10-30T02:47:00

Solution 1

IpsitaMishra gave one sample but I personally don't like using VALUES when you have to use JOINs in your insert statement. I would recommend

SQL

INSERT INTO Table1 (Field1, Field2)
SELECT a.Field1, b.Field2
FROM TableA a 
INNER JOIN TableB b ON a.ID = b.ID

Posted 30-Oct-13 2:47am

ZurdoDev

Comments

tina_overgaard 30-Oct-13 9:23am

But I nees the values so it insert the values in the database

ZurdoDev 30-Oct-13 9:28am

I re-read your question and do not understand. Explain in English what you need.

tina_overgaard 30-Oct-13 9:59am

I am insert values in my table ordre_linie and send the information to my email.

But in my email there is stand 1 on fk_dag_id and I want to show what 1 stand for in the table dage.
ordre_linie table
fk_dag_id = 1

dage table
dag_id = 1
dag_navn = Monday

dag_id = 2
dag_navn = Thursday

ZurdoDev 30-Oct-13 10:06am

That's not clear. Where are you stuck?

tina_overgaard 30-Oct-13 10:15am

This is how my email is look when it send to me:

Ordre nr Antal Navn
609 14 Blommer
609 7 Blomkål
Total pris på ordre 149 kr.
Leveringsdag 1 ????????????????????????? This one I want to show Monday from the table dag_navn

TinaOvergaard
Damgårdsvej 4
2100Kbh
2568965

ZurdoDev 30-Oct-13 10:21am

OK. So use what I posted and just modify.

INSERT INTO Table1 (Field1, Field2)
SELECT b.Field2, @parameter, @somevalue, @whateveryouneed.
FROM TableA a
INNER JOIN TableB b ON a.ID = b.ID

tina_overgaard 30-Oct-13 10:30am

Can I do this??

INSERT INTO Table1 (Field1, Field2)
SELECT TableA.Field2, @parameter, @somevalue, @whateveryouneed.
FROM TableA
INNER JOIN TableA ON table1.id = TableA.ID

ZurdoDev 30-Oct-13 10:32am

You need table 1 in your select statement.

INSERT INTO Table1(Field1, Field2)
SELECT TableA.Field2, @parameter, ...
FROM Table1
INNER JOIN TableA ON Table1.id = TableA.ID

ZurdoDev 30-Oct-13 10:32am

Basically, just write a SELECT statement that gives you the data you want and then you can put INSERT INTO on top of it after you have your select statement working.

tina_overgaard 30-Oct-13 10:45am

INSERT INTO ordre_linie
(SELECT * FROM dage, fk_ordre_id, navn, fk_kurve_id, antal, total_pris, fk_dag_id, dag_navn)
INNER JOIN dage ON ordre_linie.fk_dag_id = dage.dag_id
VALUES (@fk_ordre, @navn, @kurv, @antal, @pris, @dag, @dag_navn)

It comes and say
Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.

ZurdoDev 30-Oct-13 10:46am

Right. Follow the syntax I gave you.

INSERT INTO order_linie (fields you are inserting into)
SELECT ...

tina_overgaard 30-Oct-13 10:49am

Now it is the values

Incorrect syntax near the keyword 'VALUES'.

ZurdoDev 30-Oct-13 10:51am

LOOK AT MY SQL. THERE IS NO VALUES. Sorry, for shouting but you don't seem to be listening very well. :)

tina_overgaard 30-Oct-13 10:58am

I have done in you way, but I need the values

tina_overgaard 30-Oct-13 10:59am

This is your way:
INSERT INTO ordre_linie (fk_ordre_id, navn, fk_kurve_id, antal, total_pris, fk_dag_id)
SELECT * FROM dage, dag_navn, @fk_ordre, @navn, @kurv, @antal, @pris, @dag, @dag_navn
INNER JOIN dage ON ordre_linie.fk_dag_id = dage.dag_id
"

And I get this error:
Must declare the table variable "@fk_ordre".
Must declare the table variable "@navn".
Must declare the table variable "@kurv".
Must declare the table variable "@Antal".
Must declare the table variable "@pris".
Must declare the table variable "@dag".
Must declare the table variable "@dag_navn".

ZurdoDev 30-Oct-13 11:18am

So, declare your variables. You'll need to pass them in.

thatraja 30-Oct-13 9:34am

That's why I asked you sample data

tina_overgaard 30-Oct-13 10:00am

I am insert values in my table ordre_linie and send the information to my email.

But in my email there is stand 1 on fk_dag_id and I want to show what 1 stand for in the table dage.
ordre_linie table
fk_dag_id = 1

dage table
dag_id = 1
dag_navn = Monday

dag_id = 2
dag_navn = Thursday

Vishnu Prajapati · Accepted Answer · 2013-10-31T23:02:00

Solution 3

This one is Helpful to us....

http://stackoverflow.com/questions/7040390/sql-insert-into-with-select-and-inner-join[^]

Hope :
---------------------
Vishnu Prajapati

Posted 31-Oct-13 23:02pm

Vishnu Prajapati

coded007 · Accepted Answer · 2013-10-30T20:39:00

SQL

CREATE Table #Temp(SON nvarchar(50),ProNo nvarchar(50))

INSERT INTO #TEMP
SELECT SOH.SalesOrderNumber,PP.ProductNumber FROM Sales.SalesOrderDetail SOD (NOLOCK)
INNER JOIN Sales.SalesOrderHeader SOH (NOLOCK) ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product PP (NOLOCK) ON PP.ProductID = SOD.ProductID

SELECT * from #Temp

Drop TABLE #Temp