将数据从表插入

本文介绍了将数据从表插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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的

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))