Insert fields into another table with more column

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

SQL Server 2005 Forums

Transact-SQL (2005)

Insert fields into another table with more column

Author

Topic

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-04-07 : 09:04:04

Hi,
Have a page for inserting fields of table tbACost into another table tbDemInv which has more column fields than tbACost. How can this be done?

primary key of tbACost is Name which is a string
primary key of tbDemInv is TRNO which is a integer

Insert into tbACost(Name, TRNO, Date, EditPerson, EditDate)
(select Name, TRNO, Date, EditPerson, EditDate from tbDemInv
where Name=@name, TRNO=@trno, Date=@date, EditPerson=@editperson, EditDate=@editdate)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 09:07:28

Insert into tbACost(Name, TRNO, Date, EditPerson, EditDate)
select Name, TRNO, Date, EditPerson, EditDate from tbDemInv
where Name=@name, TRNO=@trno, Date=@date, EditPerson=@editperson, EditDate=@editdate

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:15:54

quote:
Originally posted by madhivanan

Insert into tbACost(Name, TRNO, Date, EditPerson, EditDate)
select Name, TRNO, Date, EditPerson, EditDate from tbDemInv
where Name=@name and TRNO=@trno and Date=@date and EditPerson=@editperson and EditDate=@editdate

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 11:40:04

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

Insert into tbACost(Name, TRNO, Date, EditPerson, EditDate)
select Name, TRNO, Date, EditPerson, EditDate from tbDemInv
where Name=@name and TRNO=@trno and Date=@date and EditPerson=@editperson and EditDate=@editdate

Madhivanan

Failing to plan is Planning to fail

Thanks. I missed to notice that

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:43:13

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-04-08 : 01:31:42

i got this error when i use this Query when i want to insert into tbDemInv table: -

Insert into tbDemInv(Name, TRNO, Date, EditPerson, EditDate)
select Name, TRNO, Date, EditPerson, EditDate from tbACost
where Name=@name and TRNO=@trno and Date=@date and EditPerson=@editperson and EditDate=@editdate

Error: "The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns. "

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-08 : 03:09:00

quote:
Originally posted by melon.melon

i got this error when i use this Query when i want to insert into tbDemInv table: -

Insert into tbDemInv(Name, TRNO, Date, EditPerson, EditDate)
select Name, TRNO, Date, EditPerson, EditDate from tbACost
where Name=@name and TRNO=@trno and Date=@date and EditPerson=@editperson and EditDate=@editdate

Error: "The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns. "

The statement looks correct. See if you missed any commas when executing it

Madhivanan

Failing to plan is Planning to fail

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-04-08 : 03:39:56

i miss out a field..but it doesnt seemed to insert into tbDemInv even though there is no error.


public void InsertDemItem(ACost demItem)
{

cm = new SqlCommand("Insert into tbDemInv(Name, TRNO, Date, EditPerson, EditDate)
select Name, TRNO, Date, EditPerson, EditDate from tbACost
where Name=@name, TRNO=@trno, Date=@date, EditPerson=@editperson, EditDate=@editdate")

cm.Connection = cn;

SqlParameter p1 = new SqlParameter("@name", SqlDbType.NVarChar, 200);
SqlParameter p2 = new SqlParameter("@trno", SqlDbType.NVarChar, 200);
SqlParameter p3 = new SqlParameter("@date", SqlDbType.NVarChar, 50);
SqlParameter p4 = new SqlParameter("@editperson", SqlDbType.NVarChar, 200);
SqlParameter p5 = new SqlParameter("@editdate", SqlDbType.NVarChar, 50);

cm.Parameters["@name"].Value = demItem.Name.ToString();
cm.Parameters["@trNo"].Value = demItem.TRNO.ToString();
cm.Parameters["@date"].Value = demItem.Date.ToString();
cm.Parameters["@editperson"].Value = demItem.EditPerson.ToString();
cm.Parameters["@editdate"].Value = demItem.EditDate.ToString();

cm.ExecuteNonQuery();
}

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-04-08 : 03:48:56

quote:

Originally posted by melon.melon

i miss out a field..but it doesnt seemed to insert into tbDemInv even though there is no error.


public void InsertDemItem(ACost demItem)
{

cm = new SqlCommand("Insert into tbDemInv(Name, TRNO, Date, EditPerson, EditDate)
select Name, TRNO, Date, EditPerson, EditDate from tbACost
where Name=@name and  TRNO=@trno and  Date=@date and  EditPerson=@editperson and   EditDate=@editdate")

cm.Connection = cn;

SqlParameter p1 = new SqlParameter("@name", SqlDbType.NVarChar, 200);
SqlParameter p2 = new SqlParameter("@trno", SqlDbType.NVarChar, 200);
SqlParameter p3 = new SqlParameter("@date", SqlDbType.NVarChar, 50);
SqlParameter p4 = new SqlParameter("@editperson", SqlDbType.NVarChar, 200);
SqlParameter p5 = new SqlParameter("@editdate", SqlDbType.NVarChar, 50);

cm.Parameters["@name"].Value = demItem.Name.ToString();
cm.Parameters["@trNo"].Value = demItem.TRNO.ToString();
cm.Parameters["@date"].Value = demItem.Date.ToString();
cm.Parameters["@editperson"].Value = demItem.EditPerson.ToString();
cm.Parameters["@editdate"].Value = demItem.EditDate.ToString();

cm.ExecuteNonQuery();
}

try by using and.

Karthik

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-04-08 : 03:52:55

Still no hope =X

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources