INSERT INTO using SELECT and values, and inserting multiple rows

[talktozee]

Hey, everyone!

Basically, I need to insert *multiple rows* into table A from table B based upon some criteria, and I need to insert some static values along with each row from table A.

For example:

insert into tableA (col1,col2,col3 ,col4,col5)
values
('Cheese',
'Blue',
'John Wayne',
select favorite_movie from tableB
where movietype = 'SciFi'
and (moviedate=1965 or 1966)
and B&W !=1,
'Paris')

I've tried just about everything: declared a variable, used different words (MSSQL doesn't like "insert into", just "insert"), SELECTs inside and outside of the values list, not using the word VALUES, etc.

Any ideas? The intent is to copy multiple rows from tableB to A based a set of criteria.

Thanks!

[hariharanmca]

Hey, everyone!

Basically, I need to insert *multiple rows* into table A from table B based upon some criteria, and I need to insert some static values along with each row from table A.

For example:

insert into tableA (col1,col2,col3 ,col4,col5)
values
('Cheese',
'Blue',
'John Wayne',
select favorite_movie from tableB
where movietype = 'SciFi'
and (moviedate=1965 or 1966)
and B&W !=1,
'Paris')

I've tried just about everything: declared a variable, used different words (MSSQL doesn't like "insert into", just "insert"), SELECTs inside and outside of the values list, not using the word VALUES, etc.

Any ideas? The intent is to copy multiple rows from tableB to A based a set of criteria.

Thanks!

You have to Use Two Insert Qry
1. For Multiple Table Rows
2. for User Define Values

[Sangs]

Hey, everyone!

Basically, I need to insert *multiple rows* into table A from table B based upon some criteria, and I need to insert some static values along with each row from table A.

For example:

insert into tableA (col1,col2,col3 ,col4,col5)
values
('Cheese',
'Blue',
'John Wayne',
select favorite_movie from tableB
where movietype = 'SciFi'
and (moviedate=1965 or 1966)
and B&W !=1,
'Paris')

I've tried just about everything: declared a variable, used different words (MSSQL doesn't like "insert into", just "insert"), SELECTs inside and outside of the values list, not using the word VALUES, etc.

Any ideas? The intent is to copy multiple rows from tableB to A based a set of criteria.

Thanks!

You can first insert values for the col4 using the same select query which may insert multiple multiple rows into tableA. Later you can update other columns with the static values mentioned in your query.

The query will be like this:
insert into tableA
(col4)
select
favorite_movie
from tableB
where movietype = 'SciFi'
and (moviedate=1965 or 1966)
and B&W !=1

Once the rows are inserted you can update other columns like this.

update tableA
set col1='Cheese',
col2='Blue','
col3='John Wayne',
col5='Paris'
from table A A,table B B
where
A.favorite_movi e=B.favorite_mo vie
and B.movietype = 'SciFi'
and B.moviedate in (1965,1966)
and B.B&W !=1

Thanks!

[talktozee]

Could I pull the user defined values from another temp table? Would they then be considered user defined values, and then could I use one insert?

[Sangs]

Could I pull the user defined values from another temp table? Would they then be considered user defined values, and then could I use one insert?

No need to use temp table. Since these are static values, u can assign them directly to the columns satisfying the conditions.

Regards!

[talktozee]

All: I ended up using two queries to perform this operation. It increased the number of steps required, but worked and was consistent.

[iburyak]

Try this:

[PHP]
insert into tableA (col1,col2,col3 ,col4,col5)
select 'Cheese',
'Blue',
'John Wayne',
favorite_movie,
'Paris'
from tableB
where movietype = 'SciFi'
and (moviedate=1965 or 1966)
and B&W !=1[/PHP]

[ionphoenix]

where are you using your sql for oracle or mssql/postgre/mysql?
if mssql

--
insert into urtable
values
('val1','val2') ,
('val1','val2') ;

[athamneh]

you can use a virtual table "DAUL"

your sqlstatmnet will be something like :

insert into tableA (col1,col2,col3 ,col4,col5)

(select 'Cheese',
'Blue',
'John Wayne',
favorite_movie from tableB,DUAL
where movietype = 'SciFi'
and (moviedate=1965 or 1966)
and B&W !=1,
'Paris')


i had the problem you are facing :D
it is solved now

Mohammad Athamneh

[andresM]

@Mohamad

Can you explain a little more?? the query you use dont work Thanks