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

[NeoPa]

Andres, That was the one and only post from that member and it was posted over three years ago. Also, this is not a thread for your question (thus hijacking). I suggest you post your (fully explained) question (that can potentially include a link to this thread) in a new thread which will be dedicated to answering your problem.

[andresM]

Finally i got it

Nobody explain that you have to put the same name of the column

Example:

table car2(
idcar
model
window
color
power
)

table car1(
idcar
model
)

now yo want to copy some data from car1 to car2

you only have to do this:

insert into car2(idcar,model,color,power)
select idcar,model,'Black' as color, '100hp' as power from car1 where id='001'

and done, the trick is in tag the new column with the same name that is in the table

hope this help someone

[NeoPa]

andresM:
Nobody explain that you have to put the same name of the column

That's because it's not true I'm afraid.

It's certainly good practice, but not necessary technically.

Your example SQL :

insert into car2(idcar,model,color,power)
select idcar,model,'Black' as color, '100hp' as power from car1 where id='001'

has exactly the same effect as :

INSERT INTO [Car2]
           ([IDCar]
          , [Model]
          , [Color]
          , [Power])
SELECT      [IDCar]
          , [Model]
          , 'Black'
          , '100hp'
FROM        [Car1]
WHERE      ([ID]='001')

[watty1964]

I was looking for an answer to the same question when I stumbled across this thread. Unfortunately, no-one has really answered the question here but I did find a very simple answer elsewhere so will post it here.

INSERT [INTO] tablename1 (field1, field2, field3, field4)
SELECT field1, field2, field3, A_VALUE dummy_field
FROM tablename2

Here we are inserting into tablename1 some values from the corresponding fields field1, field2, field3 in tablename2. That much is obvious.

However, we either don't have the data in tablename2 for field4 or we want to use a constant value for that field. So in the SELECT statement we refer to a field that does not exist in tablename2 (dummy_field) with a substitute value (A_VALUE) before the non-existent field. This value will be used to insert into tablename1 for all the rows.

[NeoPa]

You seem to be exhibiting the same confusion that AndresM was suffering from in post #12 Watty. It is simply not true that a field needs to be named (or ALIASed) in the SELECT clause for the SQL to work correctly.

As for there being no extant answer in the thread already, I would suggest that post #13 illustrates everything that your suggestion has, except the confusion. It illustrates the basic requirements clearly and unambiguously, without introducing any optional items as being necessary. Certainly, you can ALIAS the fields if you want to, but most people would tend to ALIAS them to match the destination field, rather than some other, random, name that can only lead to confusion for those who struggle to appreciate the method anyway.

Suggestions are always welcome of course, but it's also important to clarify when someone suggests something misleading or incorrect.