从表中插入数据 [英] Insert data from to a table

问题描述

我有一个表格，其中列出了来自特定网站的评论数量，如下所示:

I have a table that lists number of comments from a particular site like the following:

Date                        Site         Comments         Total
---------------------------------------------------------------
2010-04-01 00:00:00.000      1              5               5
2010-04-01 00:00:00.000      2              8               13
2010-04-01 00:00:00.000      4              2               7
2010-04-01 00:00:00.000      7              13              13
2010-04-01 00:00:00.000      9              1               2

我有另一个表格列出了所有网站，例如从 1 到 10

I have another table that lists ALL sites for example from 1 to 10

Site
-----
1
2
...
9
10

使用以下代码，我可以找出哪些网站缺少上个月的条目:

Using the following code i can find out which sites are missing entries for the previous month:

SELECT s.site
from tbl_Sites s
EXCEPT
SELECT c.site
from tbl_Comments c
WHERE c.[Date] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)

制作:

site
-----
3
5
6
8
10

我希望能够将我的查询中列出的缺失站点插入到带有一些默认值的评论表中，即'0's

I would like to be able to insert the missing sites that is listed from my query into the comments table with some default values, i.e '0's

Date                        Site         Comments         Total
---------------------------------------------------------------
2010-04-01 00:00:00.000      3              0               0
2010-04-01 00:00:00.000      5              0               0
2010-04-01 00:00:00.000      6              0               0
2010-04-01 00:00:00.000      8              0               0
2010-04-01 00:00:00.000      10             0               0

问题是，我是如何更新/插入表/值的?

the question is, how did i update/insert the table/values?

干杯，

李

推荐答案

INSERT INTO CommentTable (Date, Site, Comments, Total)
SELECT '2010-04-01 00:00:00.000', Site, 0, 0
FROM SiteTable
WHERE Site NOT IN
    (SELECT DISTINCT Site FROM CommmentTable
     WHERE [Date] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0))

这篇关于从表中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！