根据查询返回的结果将字符串插入SQL表 [英] Inserting strings into an SQL table depending on what results a query returns

问题描述

我有几个查询，它们用于测试我运行的测试的数据库结果是否没有明显的损坏和错误.

I have a handful of queries that I'm using to test whether or not database results from tests that I run don't have anything obviously broken and buggy in them.

其中一个查询具有以下基本形式:

One of the queries has this basic form:

SELECT *
FROM Table
WHERE Column = ''

进行检查以确保一个特定字段不为空.我想跟踪这些测试的运行情况以及它们何时通过或失败.有没有一种方法可以编写查询，以便如果该查询返回任何结果，然后将字符串写到另一个表中，该表显示"Test Passed"之类的内容?

Its checking to make sure that one particular field is not blank. I want to keep track of these tests being run and when they pass or fail. Is there a way that I can write a query so that if that query brings back any results then it writes a string to another table that says something like "Test Passed"?

因此，伪版本可能看起来像这样:

So, a pseudo version might look something like this:

IF (
    SELECT *
    FROM Table1
    WHERE Table1.Column1 = ''
) = 0
INSERT INTO Table2 (FileName, Date, Result)
VALUES ('File1', 'GetDate()', 'Test Passed')
ELSE
INSERT INTO Table2 (FileName, Date, Result)
VALUES ('File1', 'GetDate()', 'Test Failed')

该表可能如下所示:

| FileName |   Date   |    Result   |
|:--------:|:--------:|:-----------:|
| File1    | 12-25-16 | Test Passed |
| File2    | 12-25-16 | Test Failed |
| File3    | 12-25-16 | Test Passed |
| File4    | 12-25-16 | Test Passed |

推荐答案

您的代码中几乎包含了它.只需将SELECT *更改为SELECT COUNT(*)即可.我将设置状态，然后执行插入操作.

You almost have it in your code. Just change the SELECT * to SELECT COUNT(*) and you've got it . I would set the status and then do the insert.

DECLARE @testStatus NVARCHAR(MAX);

IF (
    SELECT COUNT(*)
    FROM Table1
    WHERE Table1.Column1 = ''
) = 0
    SET @testStatus = 'Test Passed'
ELSE
    SET @testStatus = 'Test Failed'

INSERT INTO Table2 (FileName, Date, Result)
VALUES ('File1', GetDate(), @testStatus)

这篇关于根据查询返回的结果将字符串插入SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！