如何在表中添加数据也将添加到其他列中 [英] How to add data in a table whch will be added to other columns as well
本文介绍了如何在表中添加数据也将添加到其他列中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在我的表中添加一些leves ...但是那个表也有不同的公司..我想只添加一次,这将被添加到所有公司..我怎么能在sql中做到这一点
解决方案
尝试此解决方案。将@t替换为您的表名,还包括您需要添加到记录中的其他字段。
insert into @ t (CompanyId,AbsenceTypeName)
选择 distinct CompanyId,' New Leave' 来自 @ t
看看例子:
DECLARE @ tmp TABLE (CompanyId INT ,AbsenceTypeName VARCHAR ( 30 ),AbsenceTypeId INT , colorcode VARCHAR ( 30 ) NULL ,TotalLeaves INT NULL ,DataForYear INT NULL )
INSERT INTO @ tmp (CompanyId,AbsenceTypeName,AbsenceTypeId,colorcode,TotalLeaves,DataForYear)
SELECT 41 ,' 休闲离开', 124 , ' #800000', NULL ,< span class =code-keyword> NULL
UNION ALL SELECT 41 ,' 付费离开, 125 ,' #A52A2A', NULL , NULL
UNION ALL SELECT 41 ,' 年假', 126 ,' #800080', 200 , 2013
SELECT *
FROM @ tmp
INSERT INTO @ tmp (CompanyId,AbsenceTypeName,Abse nceTypeId,colorcode,TotalLeaves,DataForYear)
SELECT CompanyID,' New Leave',( SELECT MAX(AbsenceTypeID)+1 FROM < span class =code-sdkkeyword> @ tmp ), NULL , NULL ,< span class =code-keyword> NULL
FROM @ tmp
SELECT *
FROM @tmp
I want to add some leves in my table ... but that table has got different company as well .. i want to add only once and that will be get added to all companies .. how can i do that in sql
解决方案
Try this solution. Replace @t with your table name and also inclue other fields which you need to add to the records.
insert into @t (CompanyId,AbsenceTypeName) select distinct CompanyId,'New Leave' from @t
Have a look at example:
DECLARE @tmp TABLE (CompanyId INT, AbsenceTypeName VARCHAR(30), AbsenceTypeId INT, colorcode VARCHAR(30) NULL, TotalLeaves INT NULL, DataForYear INT NULL) INSERT INTO @tmp (CompanyId, AbsenceTypeName, AbsenceTypeId, colorcode, TotalLeaves, DataForYear) SELECT 41, 'Casual Leave', 124, '#800000', NULL, NULL UNION ALL SELECT 41, 'Paid Leaves', 125, '#A52A2A', NULL, NULL UNION ALL SELECT 41, 'Annual Leave', 126, '#800080', 200, 2013 SELECT * FROM @tmp INSERT INTO @tmp (CompanyId, AbsenceTypeName, AbsenceTypeId, colorcode, TotalLeaves, DataForYear) SELECT CompanyID, 'New Leave', (SELECT MAX(AbsenceTypeID) +1 FROM @tmp), NULL, NULL, NULL FROM @tmp SELECT * FROM @tmp
这篇关于如何在表中添加数据也将添加到其他列中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文