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_movie=B.favorite_movie
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]

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.

[davebrads]

I had the same problem as the opening poster. I kept getting run time error 3134 Syntax error in INSERT INTO statement, with the same cause. The problem is caused by the VALUES keyword, if you omit this the query works. I found the syntax for the query on a MySQL forum, and I guess MySQL needs the VALUES keyword while SQL Server does not.

I hope this saves someone the headache I am currently suffering from trying to solve this problem.

[NeoPa]

DaveBrads:
I hope this saves someone the headache I am currently suffering from trying to solve this problem.

Unlikely I'm afraid. Like many that have posted here recently you seem to be thoroughly confused about what the question is. Unlike many, at least you are suggesting a solution (rather than trying to throw your own problem into the mix).

Using the VALUES keyword is one of the possible formats supported. For that format it is not optional. For the other format it is not required. The format that uses VALUES requires the keyword VALUES.

What you post is far from clear so it's impossible for me to identify for you where you are confused, but certainly what you contend is not accurate in detail, even if it may seem so from your perspective. If you look through the thread you'll see examples of both of these formats. Better yet, MSDN has the full T-SQL syntax explained in detail.

NB. This is specifically an MS SQL Server forum, so MySQL and other flavours are not valid answers here.

[davebrads]

I'm sorry, I don't understand your explanation. Can you explain which format requires VALUES keyword and which doesn't?

For example, this is my original statement:

INSERT INTO MyTable(ForeignKey1, ForeignKey2, Qty)
VALUES
SELECT <an integer>, ForeignKey2, Qty FROM MyTable
WHERE ForeignKey1 = <another integer>

I removed the VALUES keyword and it worked.

[NeoPa]

DaveBrads:
Can you explain which format requires VALUES keyword and which doesn't?

I think I can do this, hopefully clearly.

The APPEND type query has an INSERT INTO clause which specifies where the data should go. That's straightforward enough. However, there are two different ways of specifying where the data should come FROM :

1. SELECT and FROM clauses with other optional clauses just like a standard SELECT query.
2. A VALUES() list. This includes literal data for each field, and certainly in T-SQL, can handle values for multiple records.

Your example SQL has the VALUES keyword in it, but why is unclear. This clearly seems to be using the SELECT option. I suspect someone added the VALUES keyword as they'd seen it somewhere without a clear understanding of how it can be used.

The full syntax can be found at INSERT (Transact-SQL), but is not easy reading unless you're used to interpreting such information.

Please note that any further communication of a non-technical nature should be handled either by PM (where you can be as critical as you like) or by reporting a post. We have rules about what can be said in a thread and this type of conversation doesn't fall within them.

[gaurav21r]

Thank you so much Watty! Exactly what I was looking for in mysql...

@watty1964

[mit09]

Thank @watty1964 the solution is 100% accurate..