根据配置的列验证表中的数据 [英] Validating data in a table based on columns configured
问题描述
以下是我的要求。
我有一个临时表tbl_Staging和一个主表tbl_Master与staging表具有相同的模式。
登台表中有1行,如下所示
tbl_Staging
ID姓名状态地址1地址2地址3电话
- ---- ------ --------- ------- -------- ---------
1 XYZ Active asdf xyz hju 1234
tbl_Master
ID姓名状态地址1地址2地址3电话
- ---- ------ --------- ------- -------- ---------
另一个表tbl_Validation只有1列FieldName 。此列的值是tbl_Staging的列名。
tbl_Validation
FieldName
---- -----
名称
状态
电话
现在我想要在推入tbl_Master表之前验证tbl_Staging中为tbl_Validation(名称,状态和电话字段)中配置的列的数据。
让我知道如何实现这一点在SQL Server中
提前致谢。
我为你准备了一个样品。
根据你的表我创建并插入样本数据。
- 创建样本表
创建 表 tbl_Staging(
ID int 不 null primary key ,
名称 varchar ( 100 ),
状态 varchar ( 10 ),
地址1 varchar ( 100 ),
地址2 varchar ( 100 ),
地址3 varchar ( 100 ),
电话< span class =code-keyword> varchar ( 20 )
);
创建 table tbl_Master(
ID int 不 null primary key ,
名称 varchar ( 100 ),
状态 varchar ( 10 ),
地址1 varchar ( 100 ),
地址2 varchar ( 100 ),
地址3 varchar ( 100 ),
电话 varchar ( 20 )
);
创建 表 tbl_Validation(
FieldName varchar ( 100 )
);
- 您的示例插入查询。
insert into tbl_Staging(ID,
Name,
Status,
地址1,
地址2,
地址3,
电话)值
( 1 ,' XYZ',' Active',' asdf',' xyz',' hju',' 1234')
insert into tbl_Validation(FieldName)值
(' 名称'),
(' 状态'),
(' 电话')
现在我有了在这里使用Cursor从验证表中获取每条记录。
并使用Cursor中的Dynamic Query并将每行Column Name传递给Staging Table并获取结果数据并检查验证。
- select * from tbl_Staging
- select * from tbl_Validation
DECLARE @ fieldName varchar ( 100 )
声明 @ Result varchar ( 100 )
声明 @ Field_Data varchar ( 100 )
DECLARE @SQLQuery nvarchar (max)
DECLARE Validation_Cursor CURSOR - 声明游标
LOCAL SCROLL STATIC
FOR
选择 FieldName FROM tbl_Validation
OPEN Validation_Cursor - 打开cursor
FETCH NEXT FROM Validation_Cursor
INTO @ fieldName
PRINT @ fieldName
WHILE @@ FETCH_STATUS = 0
BEGIN
SET @ SQLQuery = N ' select @Result =' + @ fieldName + ' 来自tbl_Staging';
- EXECUTE sp_executesql @SQLQuery;
EXECUTE sp_executesql @ SQLQuery ,N ' @ Result varchar(max)out', @ Field_Data out
- 选择@Field_Data作为Validation_result,LEN(@Field_Data)Length_Validation
IF (@ fieldName = ' Telephone')
BEGIN
IF (LEN( @Field_Data )< 6 )
BEGIN
选择 ' 你不能在这里插入'
END
ELSE
BEGIN
选择 ' 您的插入查询'
END
END
FETCH NEXT FROM Validation_Cursor
INTO @ fieldName
PRINT @ fieldName - < span class =code-comment>打印名称
END
CLOSE Validation_Cursor
DEALLO CATE Validation_Cursor
i已经完成了验证,只是为了检查你可以像上面那样做的电话休息。希望这会有所帮助你
Hi, Below is my requirement.
I have a staging table tbl_Staging and a master table tbl_Master with same schema as staging table.
The staging table has 1 row in it as below
tbl_Staging
ID Name Status Address1 Address2 Address3 Telephone
-- ---- ------ -------- -------- -------- ---------
1 XYZ Active asdf xyz hju 1234
tbl_Master
ID Name Status Address1 Address2 Address3 Telephone
-- ---- ------ -------- -------- -------- ---------
Another table tbl_Validation having only 1 column "FieldName". The values for this column are the column names of tbl_Staging.
tbl_Validation
FieldName
---------
Name
Status
Telephone
Now i want to validate the data in tbl_Staging for the columns configured in tbl_Validation (Name, Status and Telephone fields) before pushing into tbl_Master table.
Let me know how to implement this in SQL Server
Thanks in advance.
Hi,
I have a made a sample for you.
As per your Table i have created and Inserted the sample Data.
-- Create your sample table create table tbl_Staging ( ID int not null primary key, Name varchar(100) , Status varchar(10) , Address1 varchar(100) , Address2 varchar(100) , Address3 varchar(100) , Telephone varchar(20) ); create table tbl_Master ( ID int not null primary key, Name varchar(100) , Status varchar(10) , Address1 varchar(100) , Address2 varchar(100) , Address3 varchar(100) , Telephone varchar(20) ); create table tbl_Validation ( FieldName varchar(100) ); -- Your Sample Insert Query. insert into tbl_Staging ( ID, Name , Status, Address1, Address2, Address3, Telephone) values (1, 'XYZ', 'Active', 'asdf', 'xyz' ,'hju' ,'1234') insert into tbl_Validation (FieldName) values ('Name'), ('Status'), ('Telephone')
Now i have used Cursor here to fetch each record from your validation Table.
And used the Dynamic Query in Cursor and pass each row Column Name to Staging Table and get the result data and check for the validation.
--select * from tbl_Staging --select * from tbl_Validation DECLARE @fieldName varchar(100) Declare @Result varchar(100) Declare @Field_Data varchar(100) DECLARE @SQLQuery nvarchar(max) DECLARE Validation_Cursor CURSOR -- Declare cursor LOCAL SCROLL STATIC FOR Select FieldName FROM tbl_Validation OPEN Validation_Cursor -- open the cursor FETCH NEXT FROM Validation_Cursor INTO @fieldName PRINT @fieldName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLQuery=N'select @Result = ' + @fieldName + ' from tbl_Staging'; --EXECUTE sp_executesql @SQLQuery; EXECUTE sp_executesql @SQLQuery, N'@Result varchar(max) out', @Field_Data out --select @Field_Data as Validation_result,LEN(@Field_Data) Length_Validation IF(@fieldName='Telephone') BEGIN IF(LEN(@Field_Data) < 6) BEGIN Select 'You can not Insert Here' END ELSE BEGIN Select 'Your Insert Query' END END FETCH NEXT FROM Validation_Cursor INTO @fieldName PRINT @fieldName -- print the name END CLOSE Validation_Cursor DEALLOCATE Validation_Cursor
i have done the validation only for checking the telephone rest you can do same like above .hope this will help you.
这篇关于根据配置的列验证表中的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!