Menu
Forgive me as I'm relatively new to SQL. But I'm trying to take modified data from a SELECT query on one table, and use it to populate data into another table.
SELECT ID FROM TABLE WHERE VALUE=10 I want to insert the resultant ID's into another Table, but with modifications such that the value is:
1stString.ID.2ndString I've found answers for how to use the ID from the SELECT in the insert, but I can't seem to get it working when trying to concatenate. There are also other values that I'm inserting, but they're literals (I'm trying to initialize default key settings for the ID's given in another table.
Using MS SQL Studio 2008, btw.
840
You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.
Right click on the database > Tasks > Generate Scripts. Next. Select "Select specific database objects" and check the table you want scripted, Next. Click Advanced > in the list of options, scroll down to the bottom and look for the "Types of data to script" and change it to "Data Only" > OK.
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
INSERT INTO table (field) SELECT '1stString.' + cast(id as varchar(50)) + '.2ndString' FROM table2 WHERE id = 10 Edit - response to comment: You're on the right track, but you want to select your hard-coded strings from your table, like this:
INSERT INTO table1 (field1, field2, field3) SELECT '1stVal', '2ndVal', '1stString.' + cast(id as varchar(50)) + '.2ndString' FROM table2 WHERE id = 10 This is also illustrated in Dustin Laine's answer.
114
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
