INSERT INTO with SELECT

[optek]

I am using a MSSQL 2000 db.

I am trying to copy one record from a table, update one field then insert this new record into the table.

This statement will copy and insert a new record.

INSERT INTO mla_Rev
(LanguageID, AppID)
SELECT LanguageID, AppID
FROM mla_Rev
WHERE (RevID = 8)

I am wondering if there is a way to insert a new value into the LanguageID column while still copying the AppID and any other data in the other columns of the record?

I hope this makes sense.

[Demaestro]

Assuming you have a sequence for the id the following will work. You may have to fudge the syntax as I don't know if mine will differ from MSSQL 2000 db.

But here you go.

INSERT INTO mla_Rev
(LanguageID, AppID)
SELECT nextval('sequence_name') as LanguageID, AppID
FROM mla_Rev
WHERE (RevID = 8)

[optek]

I am not sure what you mean by sequence?

What I would like to do is copy a row from a table, then update one of the fields with a user input from a web form then add this new row to the same table.

Something like this:

INSERT INTO mla_Rev
(LanguageID, AppID)
VALUES('userinput', (SELECT AppID FROM mla_Rev WHERE (RevID = 8)))

But of course this statement does not work.

Does this make sense?

[Demaestro]

It does make sense. I am just curious how you get a new id when you do a straight insert? Is it generated automatically? If so then this will work:

INSERT INTO mla_Rev
(AppID)
SELECT AppID
FROM mla_Rev
WHERE (RevID = 8)

Don't pass in a value for LanguageID and it should just do it automagically.

Now if that doesn't work, using a sub-select, get the max id from that table then add 1 to it and use that value for the new id. SQL looks like this:

INSERT INTO mla_Rev
(LanguageID, AppID)
SELECT
(select max(LanguageID) + 1 from mla_Rev) as LanguageID,
AppID
FROM mla_Rev
WHERE (RevID = 8)

This 2nd one is less desirable if you have more then one machine doing this poeration, depending on how your DB does record locking there could be some concurrency issues.

Let me know how it goes.

[Demaestro]

Whoa sorry. Disregard the above. I mis-read what you needed. I thought the issue was you were getting a primary key violation and needed a new id for that field.

The only thing I see wrong with the SQL you just posted is the brackets. Otherwise looks good to me.

You have:

INSERT INTO mla_Rev
(LanguageID, AppID)
VALUES
(
'userinput',
(SELECT AppID FROM mla_Rev WHERE (RevID = 8))
)

Try
INSERT INTO mla_Rev
(LanguageID, AppID)
VALUES
(
'userinput',
(SELECT AppID FROM mla_Rev WHERE RevID = 8)
)

No brackets around the "RevID = 8" portion. Also maybe this was just your code example but I would assume that LanguageID is an integer value. The single quotes around 'userinput' suggests you may have mistakingly tried to pass a string value. Try that and if not see if you can get an error message or what is being inputed and Imight be able to help more with that info.

[optek]

My example is probably a little to "striped down" to give the full picture. The LanguageID is actually a foreign key from another table. I use this Language Table to populate a drop down menu.

The main function of this system is to translate data to another language. So this document is originally in English, then a user can select a language to translate the document into.

This is where I would like to copy all the "old" records then update the languageID and finally re-insert the record.

Right now the way I have it working is that I query and pull out the "old" record with 1 sql statement, then do an Insert statement using parts of the record with the updated items.

It seems that there is a way to do this with one statement, but I guess 2 statements gets the job done too.

[optek]

I tried your 2nd idea and I get a "Subqueries are not allowed in this context. Only scalar expressions are allowed" error.

[Demaestro]

well that stinks, my db allows sub-selects there.

Maybe a select into is what you need.
SELECT
userinput,
AppID
INTO
mla_Rev
FROM
mla_Rev
WHERE
RevID = 8

This systax is wrong though I know I am missing something about the fields to be populated on the table being inserted into sorry but it's been a while since I used a select into. I think maybe that if you have a value in the top select portion for every field in the table to be inserted it will just do them in "natural" order, not sure how to account for fields you want to disregard.

Good Luck

If you have a refernce PDF or something try searching for select into and see what you can find.

Also you sure that this doesn't work:

INSERT INTO mla_Rev
(LanguageID, AppID)
SELECT
userinput, AppID
FROM
mla_Rev
WHERE
RevID = 8

[aspdaddy]

The easiest way is to just pass a parameter to your original SQL:

CREATE PROCEDURE [dbo].[myProc]
@Language smallint
AS
INSERT INTO mla_Rev
(LanguageID, AppID)
SELECT @Language, AppID
FROM mla_Rev
WHERE (RevID = 8)
GO