如何设置我的记录中不存在的变量? [英] how to set a variable that dont exists in my record?
问题描述
大家好...以及2012年新年快乐.
我有一个问题...让我解释一下
1.我读了一个csv文本..
2.使用sql bulk ..
存储到sql db ..
3.如果文件已更改..我再次读取..并使用merge和temp表使用im更新数据库..
在csv文件中有3个项..
Hi everyone...and happy new year 2012..
i got one question...let me explain
1. i read a csv text..
2. store into sql db..using sql bulk..
3. if the file changed..i read it again..and update db using merge and im using temp table..
in the csv file there are 3 items..which is
20111229,07:11:34,0011206071
第一个是日期，第二个是时间，第三个是id ...
但是在我的数据库中...我创建了另一列状态.此状态取决于时间..
csv文件中的数据仅针对那些来者退出..
例如:如果时间< 08:00:00，状态出席
如果时间> 08:00:00，状态延迟
存储在我的表中的状态logDetail
这是我真正的问题...
我如何设置不来的状态?
由于状态取决于csv文件中的时间...因此显然不存在未记录！
但是我该如何设置状态=不在我的logDetail表中?? ..
任何人都可以提供帮助吗?
我浪费了我两天的时间来寻找解决方案...
谢谢您的帮助和建议...
此处的代码:
the 1st is date, 2nd is time, 3rd is id...
but in my db...i created another column which status. This status depend on time..
the data in csv file exits only for those who were came..
e g : if the time< 08:00:00, status attend
if the time> 08:00:00, status late
the status stored in my table logDetail
this is my real question...
how can i set the status for not coming??
since the status is depend on time in csv file...so obviously the record for not coming does not exist!
but how may i set the status = not coming in my logDetail table??..
anybody can help??
i wasting my 2 days finding solution...
thanks you for any help and suggestion...
here the code:
//Make a temp table in sql server that matches our production table
string tmpTable = "create table #templogDetail (L_Date DateTime, L_Time time(0), L_CardID varchar(20), L_Status varchar(10))";
DataTable table = new DataTable();
//Create a datatable that matches the temp table exactly
table.Columns.Add(new DataColumn("L_Date", typeof(DateTime)));
table.Columns.Add(new DataColumn("L_Time", typeof(TimeSpan)));
table.Columns.Add(new DataColumn("L_CardID", typeof(string)));
table.Columns.Add(new DataColumn("L_Status", typeof(string)));
int i2 = 0;
string[] content = System.IO.File.ReadAllLines(e.FullPath);
foreach (string line in content)
{
string[] arr;
arr = line.Split('',''); //splitting the line which was read by the stream reader object
DataRow row = table.NewRow();
row["L_Date"] = DateTime.Parse(arr[0].Substring(0, 4) + "-" + arr[0].Substring(4, 2) + "-" + arr[0].Substring(6, 2));
row["L_Time"] = TimeSpan.Parse(arr[1]);
if (TimeSpan.Parse(arr[1]) < TimeSpan.Parse("07:41:00"))
status = "COMING";
else if (TimeSpan.Parse(arr[1]) < TimeSpan.Parse("09:00:00")&(TimeSpan.Parse(arr[1]) > TimeSpan.Parse("07:40:00")))
status = "LATE";
else
status = "BACK";
row["L_CardID"] = arr[2];
row["L_Status"] = status;
table.Rows.Add(row);
i2++;
}
//Connect to DB
string conString = "Data Source=****2;Initial Catalog=attandent;User ID=sa;Password=******;";
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
try
{
//Execute the command to make a temp table
SqlCommand cmd = new SqlCommand(tmpTable, con);
cmd.ExecuteNonQuery();
//BulkCopy the data in the DataTable to the temp table
using (SqlBulkCopy bulk = new SqlBulkCopy(con))
{
bulk.DestinationTableName = "#templogDetail";
bulk.WriteToServer(table);
}
//MessageBox.Show("before sql query");
//Now use the merge command to upsert from the temp table to the production table
string mergeSql = @"
merge into logDetail as Target
using #templogDetail as Source
on Target.L_Date=Source.L_Date
and Target.L_Time=Source.L_Time
and Target.L_CardID=Source.L_CardID
when matched then
update set Target.L_Status=Source.L_Status
when not matched then
insert (L_Date,L_Time,L_CardID,L_Status)
values(Source.L_Date,Source.L_Time,Source.L_CardID,Source.L_Status);";
cmd.CommandText = mergeSql;
cmd.ExecuteNonQuery();
推荐答案
假设这仅与更新有关(其他我无法想象):在导入下一个CSV之前，设置导入表的所有现有行到不来".
如果您需要以前的状态，可以扩展您的状态:
"late"，"was_late"，并将所有迟到的记录设置为"was_late"…，依此类推.但是为此，在导入下一个CSV之前添加一个额外的列来保存状态会容易得多(并且可能更有效率).
问候.
In assume this is related for updates only (other I can not imagine): Before importing the next CSV, set all existing rows of your import table to "not coming".
In case you need to have the former state you can extend your status :
"late", "was_late" and set all records which where late to "was_late"… and so on. But for this it would be much easier (and probably more efficient) adding an extra column where you save the status before importing the next CSV.
Regards.
假设logDetail表中存在"A121"，而csv中不存在，那么您可以将logDetail加入
#templogDetail并设置不存在的行的状态(即，其中#templogDetail.L_CardID为null ...)
更新ld
设置ld.L_Status =``不来''
来自logDetail ld
左联接#templogDetail tld
在tld.L_CardID = l.L_CardID上-更新多行，全部显示它们为``NOT COMING''
其中tld.L_CardID为null
或者，如果亲自存在"A121"，但没有出现在logDetail或#templogDetail中，那么您将不得不做一些事情
像这样...(即插入，但我不知道您将使用什么值来替换缺少的csv条目的值！)
插入logDetail(L_Date，L_Time，L_CardID，L_Status)
选择< somedate>，< sometime>，p.person_card_id，``不来''
来自人p
左联接#templogDetail tld
在tld.L_CardID = p.person_card_id
其中tld.L_CardID为null
Assuming that ''A121'' exists in the logDetail table and not in the csv then you can left join logDetail to
#templogDetail and set the status for those rows that do not exist (i.e. where #templogDetail.L_CardID is null...)
update ld
set ld.L_Status = ''NOT COMING''
from logDetail ld
left join #templogDetail tld
on tld.L_CardID = l.L_CardID -- updates multiple rows all showing that they are ''NOT COMING''
where tld.L_CardID is null
Alternatively if ''A121'' exists in person but not in either logDetail or #templogDetail then you would have to do something
like this...(i.e. an insert but I have no idea what values you would use to replace those for the missing csv entry!)
insert logDetail (L_Date,L_Time,L_CardID,L_Status)
select <somedate>,<sometime>,p.person_card_id,''NOT COMING''
from person p
left join #templogDetail tld
on tld.L_CardID = p.person_card_id
where tld.L_CardID is null
这篇关于如何设置我的记录中不存在的变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
