Insert into values question - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Insert into values question

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2007-01-25 : 15:33:12

Trying to run this query and getting an error saying "missing values".

insert into table1 (val_1,val_2,val_3,val_4)
values (select value1,value3,value3,value4from table2)

Any ideas why?

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2007-01-25 : 15:34:08

sorry, it says "missing expression" not missing values

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-01-25 : 15:38:56

[code]insert into table1 (val_1,val_2,val_3,val_4)
select value1,value3,value3,value4 from table2[/code]

Srinika

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2007-01-25 : 16:19:13

quote:
Originally posted by Srinika

insert into table1 (val_1,val_2,val_3,val_4)
select value1,value3,value3,value4 from table2

Srinika

The problem with doing it this way is that no values are inserted into table2.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 16:23:11

Of course not!

You are SELECTing from table2 INTO table1. Learn your SQL Syntax.

These two are each other's opposites

insert into table1 (val_1,val_2,val_3,val_4)
select value1,value2,value3,value4 from table2

insert into table2 (val_1,val_2,val_3,val_4)
select value1,value3,value3,value4 from table1

Peter Larsson
Helsingborg, Sweden

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2007-01-25 : 16:24:00

I want to insert some values from a table1 into table2 that contains similar columns to table1, but also contains some columns that table1 doesn't have.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 16:26:31

How do you propose that should happen?
If the target does not have the columns as the source have, where do you want the remaining columns to go?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 16:29:26

If the columns in table2 are nullable, dont write them in the insert statement! Let them be NULL.

insert into table2 (colA, colB, colX, colY)
select colBlue, colGreen, colYellow, colBlack from table1

Peter Larsson
Helsingborg, Sweden

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2007-01-25 : 16:31:19

table2 contains 7 columns, and I only want to insert 4 columns of data from table1.

I don't want any data populated into the other 3 columns.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 16:36:43

[code]
INSERT INTO Table2 ( Col1, Col2, Col3, Col4, Col5, Col6, Col7 )
SELECT ColA, NULL, NULL, ColB, ColC, NULL, ColD FROM Table1[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 16:37:25

[code]
INSERT INTO Table2 ( Col1, Col4, Col5, Col7 )
SELECT ColA, ColG, ColP, ColZ FROM Table1[/code]

Peter Larsson
Helsingborg, Sweden

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2007-01-25 : 16:43:25

thanks Peter, got it... I really appreciate you help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 16:47:06

Good luck!

Peter Larsson
Helsingborg, Sweden