将行数据插入表中 [英] Inserting rows data into tables

问题描述

如何将旧表[t_attendence_raw]中的部分/部分行数据插入新表[t_attendence]

这是我的编码.请任何人帮忙吗?！

这三行是:memcode，m_cardno和t_scantime.

how can i insert some/part rows data from a old table[t_attendence_raw] into a new table [t_attendence]

here''s my coding. please anyone help?!

those three rows are: memcode, m_cardno and t_scantime.

String SQLQuery = "INSERT INTO [t_attendence] (memcode, m_cardno, t_scantime, t_type, t_del, t_post, create_date, create_by, modify_date, modify_by) VALUES ([t_attendence_r].[m_memcode], [t_attendence_r].[m_cardno], [t_attendence_r].[t_scantime], 'Y', 'Y', 'Y', @create_date, @create_by, @modify_date, @modify_by)";
            SqlCommand cmd = new SqlCommand(SQLQuery, conn);
            {   
                cmd.Parameters.AddWithValue("@modify_by", HttpContext.Current.User.Identity.Name);
                cmd.Parameters.AddWithValue("@modify_date", DateTime.Now.ToString(""));
                cmd.Parameters.AddWithValue("@create_by", HttpContext.Current.User.Identity.Name);
                cmd.Parameters.AddWithValue("@create_date", DateTime.Now.ToString(""));

                cmd.ExecuteNonQuery();
                
                cmd.Parameters.RemoveAt("@modify_by");
                cmd.Parameters.RemoveAt("@modify_date");
                cmd.Parameters.RemoveAt("@create_by");
                cmd.Parameters.RemoveAt("@create_date");
            }

推荐答案

看看Select * Into查询.
此 [ http://www.w3schools.com/sql/sql_select_into.asp [ ^ ].

Have a look at the Select * Into query.
This[^] describes this in some detail.

Here is another example - http://www.w3schools.com/sql/sql_select_into.asp[^].

或者如果该表已经存在，您可以使用:

Or if the table already exists, you can use:

INSERT INTO t_attendence
([list of fields to insert])
SELECT [list of fields to select]
FROM t_attendence_raw
WHERE [conditions to restrict the rows to insert]

根据您的评论Mika我要提醒您，除了插入源表字段外，您还可以在插入语句中使用functions 和parameters :

According to your comment for Mika I should remind you that you can use also use functions and parameters in you insert into statement in addition to source table fields :

INSERT INTO [t_attendence] (memcode, m_cardno, t_scantime, t_type, t_del, t_post, create_date, create_by, modify_date, modify_by) 
select ([t_attendence_r].[m_memcode], [t_attendence_r].[m_cardno], [t_attendence_r].[t_scantime], 'Y', 'Y', 'Y', GETDATE(), @create_by, GETDATE(), @modify_by)
from [t_attendence_r]

希望对您有所帮助.

Hope it helps.

这篇关于将行数据插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！