Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Insert data from another table

Author  Topic 

kazineel
Starting Member

16 Posts
Posted - 2010-06-21 : 09:27:25
Hello Guys,
I want to insert data from another table, here is my sql command, when I execute my command I got this error "#1136 - Column count doesn't match value count at row 1 "


$sql = "INSERT INTO ReportTm (Lab_id, Test_iteration_id, TestCycle_id, TestPlan_id, TTC, P, B, F, N, U, X, Def ) VALUES (
(SELECT Lab_id, Test_iteration_id, TestPlan_id, TestCycle_id,COUNT( TestCase_id ) TTC FROM TestExecution WHERE Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) P FROM TestExecution WHERE status='P' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) B FROM TestExecution WHERE status='B' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) F FROM TestExecution WHERE status='F' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) N FROM TestExecution WHERE status='N' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) U FROM TestExecution WHERE status='U' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) X FROM TestExecution WHERE status='X' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) Def FROM TestExecution WHERE status='Def' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id)

)";


Can u help me where is the problem?

Sachin.Nand

2937 Posts
Posted - 2010-06-21 : 09:46:41
Because you have not proided any data for columns marked in red.

(Lab_id, Test_iteration_id, TestCycle_id, TestPlan_id, TTC, P, B, F, N, U, X, Def) VALUES (
(SELECT Lab_id, Test_iteration_id, TestPlan_id, TestCycle_id,COUNT( TestCase_id )




Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH
Go to Top of Page

kazineel
Starting Member

16 Posts
Posted - 2010-06-21 : 09:56:47
quote:
Originally posted by Idera

Because you have not proided any data for columns marked in red.

(Lab_id, Test_iteration_id, TestCycle_id, TestPlan_id, TTC, P, B, F, N, U, X, Def) VALUES (
(SELECT Lab_id, Test_iteration_id, TestPlan_id, TestCycle_id,COUNT( TestCase_id )




Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH


Can you help me how to insert those values from TestExecution table to ReportTm table
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-06-21 : 10:06:07
Just put 0 for the column data for the columns which are numeric or '' for columns which are varchar for which you dont have values for.

Example

(Lab_id, Test_iteration_id, TestCycle_id, TestPlan_id, TTC, P, B, F, N, U, X, Def) VALUES (
(SELECT Lab_id, Test_iteration_id, TestPlan_id, TestCycle_id,COUNT(TestCase_id),0,0,0,0,0,0,0



Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-06-21 : 10:19:29
No!
P,B,F and so on are provided in the subselects.
The problem is the first subselect and it should not be done using VALUES()


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-06-21 : 10:27:04
Not beautyful but it should work:
$sql = "INSERT INTO ReportTm (Lab_id, Test_iteration_id, TestCycle_id, TestPlan_id, TTC, P, B, F, N, U, X, Def )
SELECT
Lab_id,
Test_iteration_id,
TestPlan_id,
TestCycle_id,
COUNT( TestCase_id ) TTC
(SELECT COUNT( TestCase_id ) P FROM TestExecution WHERE status='P' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) B FROM TestExecution WHERE status='B' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) F FROM TestExecution WHERE status='F' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) N FROM TestExecution WHERE status='N' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) U FROM TestExecution WHERE status='U' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) X FROM TestExecution WHERE status='X' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) Def FROM TestExecution WHERE status='Def' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id)

FROM TestExecution WHERE Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id

";



No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

kazineel
Starting Member

16 Posts
Posted - 2010-06-21 : 10:29:01
quote:
Originally posted by Idera

Just put 0 for the column data for the columns which are numeric or '' for columns which are varchar for which you dont have values for.

Example

(Lab_id, Test_iteration_id, TestCycle_id, TestPlan_id, TTC, P, B, F, N, U, X, Def) VALUES (
(SELECT Lab_id, Test_iteration_id, TestPlan_id, TestCycle_id,COUNT(TestCase_id),0,0,0,0,0,0,0



Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH


Where I put 0,0,0,0,0,0,0 values? Sorry I screw up myself
$sql = "INSERT INTO ReportTm (Lab_id, Test_iteration_id, TestCycle_id, TestPlan_id, TTC, P, B, F, N, U, X, Def ) VALUES (
(SELECT Lab_id, Test_iteration_id, TestPlan_id, TestCycle_id,COUNT( TestCase_id) TTC FROM TestExecution WHERE Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) P FROM TestExecution WHERE status='P' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) B FROM TestExecution WHERE status='B' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) F FROM TestExecution WHERE status='F' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) N FROM TestExecution WHERE status='N' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) U FROM TestExecution WHERE status='U' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) X FROM TestExecution WHERE status='X' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) Def FROM TestExecution WHERE status='Def' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id)
)";
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-06-21 : 10:36:28
Just ignore my replies & do what webfred has suggested.


Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH
Go to Top of Page

kazineel
Starting Member

16 Posts
Posted - 2010-06-21 : 11:13:07
quote:
Originally posted by webfred

Not beautyful but it should work:
$sql = "INSERT INTO ReportTm (Lab_id, Test_iteration_id, TestCycle_id, TestPlan_id, TTC, P, B, F, N, U, X, Def )
SELECT
Lab_id,
Test_iteration_id,
TestPlan_id,
TestCycle_id,
COUNT( TestCase_id ) TTC
(SELECT COUNT( TestCase_id ) P FROM TestExecution WHERE status='P' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) B FROM TestExecution WHERE status='B' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) F FROM TestExecution WHERE status='F' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) N FROM TestExecution WHERE status='N' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) U FROM TestExecution WHERE status='U' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) X FROM TestExecution WHERE status='X' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) Def FROM TestExecution WHERE status='Def' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id)

FROM TestExecution WHERE Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id

";



No, you're never too old to Yak'n'Roll if you're too young to die.


I got this error

Error

SQL query: Documentation

INSERT INTO ReportTm (Lab_id, Test_iteration_id, TestCycle_id, TestPlan_id, TTC, P, B, F, N, U, X, Def ) SELECT Lab_id, Test_iteration_id, TestPlan_id, TestCycle_id, COUNT( TestCase_id ) TTC (SELECT COUNT( TestCase_id ) P FROM TestExecution WHERE status='P' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id), (SELECT COUNT( TestCase_id ) B FROM TestExecution WHERE status='B' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id), (SELECT COUNT( TestCase_id ) F FROM TestExecution WHERE status='F' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id), (SELECT COUNT( TestCase_id ) N FROM TestExecution WHERE status='N' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id), (SELECT COUNT( TestCase_id ) U FROM TestExecution WHERE status='U' AND Lab_id LIKE '%2%' AND Test_iteratio[...]

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT COUNT( TestCase_id ) P FROM TestExecution WHERE status='P' AND Lab_id LI' at line 8
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-06-21 : 11:43:42
There is a comma missing after TTC

SELECT
Lab_id,
Test_iteration_id,
TestPlan_id,
TestCycle_id,
COUNT( TestCase_id ) TTC ,
(SELECT COUNT( TestCase_id ) P ...



No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

kazineel
Starting Member

16 Posts
Posted - 2010-06-21 : 11:50:57
quote:
Originally posted by webfred

There is a comma missing after TTC

SELECT
Lab_id,
Test_iteration_id,
TestPlan_id,
TestCycle_id,
COUNT( TestCase_id ) TTC ,
(SELECT COUNT( TestCase_id ) P ...



No, you're never too old to Yak'n'Roll if you're too young to die.


SORRY, I got this error message, sorry brother

Error

SQL query: Documentation

INSERT INTO ReportTm (Lab_id, Test_iteration_id, TestCycle_id, TestPlan_id, TTC, P, B, F, N, U, X, Def ) SELECT Lab_id, Test_iteration_id, TestPlan_id, TestCycle_id, COUNT( TestCase_id ) TTC, (SELECT COUNT( TestCase_id ) P FROM TestExecution WHERE status='P' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id), (SELECT COUNT( TestCase_id ) B FROM TestExecution WHERE status='B' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id), (SELECT COUNT( TestCase_id ) F FROM TestExecution WHERE status='F' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id), (SELECT COUNT( TestCase_id ) N FROM TestExecution WHERE status='N' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id), (SELECT COUNT( TestCase_id ) U FROM TestExecution WHERE status='U' AND Lab_id LIKE '%2%' AND Test_iterati[...]

MySQL said: Documentation
#1242 - Subquery returns more than 1 row



Here is my query:

INSERT INTO ReportTm (Lab_id, Test_iteration_id, TestCycle_id, TestPlan_id, TTC, P, B, F, N, U, X, Def )
SELECT
Lab_id,
Test_iteration_id,
TestPlan_id,
TestCycle_id,
COUNT( TestCase_id ) TTC,
(SELECT COUNT( TestCase_id ) P FROM TestExecution WHERE status='P' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) B FROM TestExecution WHERE status='B' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) F FROM TestExecution WHERE status='F' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) N FROM TestExecution WHERE status='N' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) U FROM TestExecution WHERE status='U' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) X FROM TestExecution WHERE status='X' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id),
(SELECT COUNT( TestCase_id ) Def FROM TestExecution WHERE status='Def' AND Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id)

FROM TestExecution WHERE Lab_id LIKE '%2%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY TestPlan_id
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-06-21 : 11:58:21
Please ask in a MySQL Forum.
This is an MS SQL Server Forum and maybe there is something special in MySQL that I don't know.

Sorry

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site