Microsoft SQL Server从选择查询中插入 [英] Microsoft SQL Server insert from select query
问题描述
我想做的事情:读取日志并将必要的数据插入3个不同的表中,以相互获取信息.
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
.
在游戏数据库上找到Mail_Item_Table
,Mail_List_Table
,Mail_Message_Table
.
LOG_ITEM201303
is found on gamelogs db.
Mail_Item_Table
, Mail_List_Table
, Mail_Message_Table
is found on game db.
邮件表通过索引连接.
CHAR_KEY
,NAME
,ITEMNUM
是我需要用于查询的值.
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')
上面的日志查询的示例结果(实际总结果在600以上):
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())
我的问题:
如何自动执行所有操作,查询将读取所有日志并逐行插入数据. 非常感谢.
How to automate all this, that the query will read all the logs and insert the data row by row. Thank you very much.
我可以为此使用@variables吗?
Can I use @variables for this?
推荐答案
您可以将以下语法用于插入
You can use the following syntax for inserts
INSERT INTO dbo.Destination (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM dbo.Source
如果您的表具有与目标相同的列或结果集,这些结果与目标具有相同的列,则不必在INSERT中指定列.
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
这两个都基于已经创建的Destination表.这些不与SELECT * INTO dbo.Destination 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
这篇关于Microsoft SQL Server从选择查询中插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!