如何在SQL表中动态插入列值 [英] How to dynamically insert column values into SQL Table

问题描述

我有一个名为"TAB1"的表.

我想将值插入TAB1，而不必根据条件提及列名.

例如，

TAB1字段:
------------
否
名称
主题
薪金
部门

有时我不想插入所有列.根据我的条件，我只是插入值.

例如，

第一次插入时，除薪水和部门外，我具有(No，Name，Subject)之类的值.

对于第二次插入，我仅具有(Salary，Dept)之类的值.

以上插入将基于某些条件而发生.

Hi,

I have the table called "TAB1".

I want to insert the values into TAB1 without mentioning column names based on the condition.

For Example,

TAB1 Fields:
------------
No
Name
Subject
Salary
Dept.

Sometimes i don''t want to insert all the columns. Based on my condition i''m just inserting the values.

For example,

First time insertion, i have the values like (No,Name,Subject) except salary and dept.

For the second time insertion, i have the values like (Salary,Dept) only.

The above insertions will be happening based on some condition.

How we form the insert statement in ASP.NET...?

推荐答案

^ ]如果您按创建顺序为所有列提供值，则只能在不指定列名的情况下使用该语句.

如果您不想指定列，但只想插入某些列，则必须对其他列使用默认值.

在第一种情况下，您使用

The SQL INSERT[^] statement can only be used without specifying the column names if you supply a value for all the column in order they were created.

If you do not want to specify the columns but also want to insert only certain columns you will have to use default value for the other columns.

In the first case you use

INSERT TAB1 (<No value>, <Name value>, <Subject value>, DEFAULT, DEFAULT)

在第二种情况下，您使用

In the second case you use

INSERT TAB1 (DEFAULT, DEFAULT, DEFAULT, <Salary value>, <Dept value>)

仅当您为所有列指定了默认值并且约束条件允许插入值时，此方法才有效.
在您的情况下，只有在没有为表定义主键的情况下，这才起作用.

This only works if you have specified default values for all the columns and the constrains allow the insertion of the values.
In your case this would only work if you have no primary key defined for the table.

您好，亲爱的.........
首次使用插入到.
和第二次用户更新查询.

插入TAb1
(NO，name)values(12345，''aa'')


更新tabl dept =，薪水=否= 12345

hello dear.........
Use Insert into for first time.
and second time user update query.

Inset into TAb1
(NO, name) values(12345,''aa'')


update tabl dept = , salary = where no = 12345

您必须在更新薪金和部门列的同时更新该行

在这里，我根据您的要求提供一些想法，请检查一次

Hi,

you''ve to update that row while updating salary and dept columns

Here I''m providing some idea on your requirement check this once

  create procedure AddData
(
 @cmdtype nvarchar(50),
 @Name nvarchar(50),
 @sub nvarchar(50),
 @no int=NULL,
 @sal money=NULL,
 @dept nvarchar(50)=NULL
)
as begin
 if(@cmdtype=="I")
{
   insert into tablename values (@Name,@sub,@sal,@dept);
}
else
{
  if(@no not null)
{
  update tablename set sal=@sal,dept=@dept where @no
}
}
end

id是自动生成的列.


执行过程是

Here id is auto generated column .


execution procedure is

exec AddData 'I','murali','SQL'

exec AddData 'U','murali','SQL',1154,30000,'Developement'

在上面的代码中，第一行用于插入第二行以更新sal和dept

最好的

in the above code first line for inserting second line for updating sal and dept

All the Best

这篇关于如何在SQL表中动态插入列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！