Microsoft SQL Server insert from select query
insert, select, sql-server, sql-server-2008
Solution
You can use the following syntax for inserts
INSERT INTO dbo.Destination (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM dbo.Source
If you had tables with the same columns or a result set that had the same columns as your destination, you don't have to specify columns in the INSERT.
INSERT INTO dbo.Destination
SELECT *
FROM dbo.Source
Both of these are predicated on your Destination table already being created. These are NOT the same as `SELECT * INTO dbo.Destination FROM dbo.Source`
Problem
What I'm trying to do: read the logs and insert the necessary data into 3 different tables that get information from each other. `LOG_ITEM201303` is found on gamelogs db. `Mail_Item_Table`, `Mail_List_Table`, `Mail_Message_Table` is found on game db. The Mail Tables are connected via the Indexes. `CHAR_KEY`, `NAME`, `ITEMNUM` are the values I need to use for my queries. The query for me to get the data from the logs: ``` SELECT CHAR_KEY, NAME, ITEMNUM FROM LOG_ITEM201303 where ( ITEMNUM = 14317 OR ITEMNUM = 14318 OR ITEMNUM = 15478 OR ITEMNUM = 15479 OR ITEMNUM = 14301 OR ITEMNUM = 14302 OR ITEMNUM = 15476 OR ITEMNUM = 15477 OR ITEMNUM = 15018 OR ITEMNUM = 15019 OR ITEMNUM = 15020 OR ITEMNUM = 15021 OR ITEMNUM = 15022 OR ITEMNUM = 15023 OR ITEMNUM = 15024 OR ITEMNUM = 15025 OR ITEMNUM = 14437 OR ITEMNUM = 14438 OR ITEMNUM = 15656 OR ITEMNUM = 15657 OR ITEMNUM = 15658 OR ITEMNUM = 15659 OR ITEMNUM = 15660 OR ITEMNUM = 15661 OR ITEMNUM = 15662 OR ITEMNUM = 15663 ) AND (KIND = 133) AND (Convert(varchar, OCCUR_TIME,111) < '2013/03/22') ``` Sample result of logs query above(total actual results are in 600+): ``` CHAR_KEY NAME ITEMNUM -----------+----------------+----------- 28257 | clarkailey | 14438 894367 | Wolf | 15023 2869858 | HOPEINME | 14437 ``` Now I need to automatically insert each row into this query: ``` CHAR_KEY NAME ITEMNUM -----------+----------------+----------- 2869858 | HOPEINME | 14437 ``` (this query shows an example of the 3rd sample data above being inserted... instead of making this query for each entry is there a way for this to done faster?) ``` INSERT INTO Mail_Item_Table (ItemNumber, ItemInfo, ReceiveDate) VALUES (14437, --this is the ITEMNUM (SELECT CONVERT(BINARY(16), REVERSE(CONVERT(BINARY(16), 14437)))), NULL) INSERT INTO Mail_Message_Table (Message) VALUES ('Automated Message from the ADMIN.') INSERT INTO Mail_List_Table (ReceiverCharKey, MailListIndex, MailItemIndex, MailMessageIndex, Sender, Receiver, SendDate) VALUES (2869858, --this is the CHAR_KEY (SELECT TOP 1 MailListIndex+1 as last_entry FROM Mail_List_Table WHERE sender = 'SENDER' ORDER BY MailListIndex DESC), (SELECT TOP 1 MailItemIndex AS last_entry FROM Mail_Item_Table ORDER BY MailItemIndex DESC), (SELECT TOP 1 MailMessageIndex AS last_entry FROM Mail_Message_Table ORDER BY MailMessageIndex DESC), 'SENDER', 'HOPEINME', --this is the NAME getdate()) ``` My question: How to automate all this, that the query will read all the logs and insert the data row by row. Thank you very much. Can I use @variables for this?