数据库中某个表a(不定是哪个表,也可能是b,也可能是c),然后备份这个表,在备份表的基础上,增加一个时间字段riqi。
问题:我想往备份表里插入被备份表的某条记录,怎么解决?表a的字段和结构都不知道,只知道表名。
谢谢
问题:我想往备份表里插入被备份表的某条记录,怎么解决?表a的字段和结构都不知道,只知道表名。
谢谢
解决方案 »
- 如何实现文字 竖向排列 的RichtextBox
- 机子是64位的cpu,24G内存,server 2003系统,但.net写的窗口程序仅占用不到2G,如何突破这个限制呢
- 在配置文件里连sql2005
- C#WinForm显示
- 如何实现点击listview中的某一item时,使其产生响应的动作??
- netscape 3.0中支持的html标志符
- 通过反射设置其它进程窗口的属性和调用方法无效。
- 还是进度条的问题
- 在windows 98 下的应用程序为什么不能访问web services?
- 如何把StringBuilder类型转成string类型
- 现在市面上流行的软件都是用什么方法进行注册的?
- 如何在表格中动态插入一行?
/// <summary>
/// 根据表名,取得对应字段 数据集 (Sql 2000可用)
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public DataTable GetTableFields(string tableName){ StringBuilder sql = new StringBuilder("SELECT ");
sql.Append("表名=case when a.colorder=1 then d.name else '' end, ");
sql.Append("表说明=case when a.colorder=1 then isnull(f.value,'') else '' end, ");
sql.Append("字段序号=a.colorder,");
sql.Append("字段名=a.name,");
sql.Append("标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, ");
sql.Append("主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( ");
sql.Append("SELECT name FROM sysindexes WHERE indid in( ");
sql.Append("SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ");
sql.Append("))) then 'yes' else 'no' end, ");
sql.Append("类型=b.name, ");
sql.Append("占用字节数=a.length,");
sql.Append("长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),");
sql.Append("小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),");
sql.Append("允许空=case when a.isnullable=1 then 'yes'else 'no' end,");
sql.Append("默认值=isnull(e.text,''),");
sql.Append("字段说明=isnull(g.[value],'')");
sql.Append("FROM syscolumns a ");
sql.Append("left join systypes b on a.xusertype=b.xusertype ");
sql.Append("inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' ");
sql.Append("left join syscomments e on a.cdefault=e.id ");
sql.Append("left join sysproperties g on a.id=g.id and a.colid=g.smallid ");
sql.Append("left join sysproperties f on d.id=f.id and f.smallid=0 ");
// 如果只查询指定表,加上此条件
sql.Append("where d.name='"+tableName+"' ");
sql.Append("order by a.id,a.colorder");
DataTable dt = new DataTable();
THS.Tools.DataAccess obj = new DataAccess();
dt = obj.GetDataSet(sql.ToString()).Tables[0];
if(dt != null)
return dt;
else
return null;
}
/// <summary>
/// 根据表名,取得对应字段 数据集(sql 2005可用)
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public DataTable GetTableFields(string tableName){ StringBuilder sql = new StringBuilder("SELECT ");
sql.Append("表名=case when a.colorder=1 then d.name else '' end, ");
sql.Append("表说明=case when a.colorder=1 then isnull(f.value,'') else '' end, ");
sql.Append("字段序号=a.colorder,");
sql.Append("字段名=a.name,");
sql.Append("标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, ");
sql.Append("主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( ");
sql.Append("SELECT name FROM sysindexes WHERE indid in( ");
sql.Append("SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ");
sql.Append("))) then 'yes' else 'no' end, ");
sql.Append("类型=b.name, ");
sql.Append("占用字节数=a.length,");
sql.Append("长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),");
sql.Append("小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),");
sql.Append("允许空=case when a.isnullable=1 then 'yes'else 'no' end,");
sql.Append("默认值=isnull(e.text,''),");
sql.Append("字段说明=isnull(g.[value],'')");
sql.Append("FROM syscolumns a ");
sql.Append("left join systypes b on a.xusertype=b.xusertype ");
sql.Append("inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' ");
sql.Append("left join syscomments e on a.cdefault=e.id ");
sql.Append("left join sys.extended_properties g on a.id=g.Major_id and a.colid=g.minor_id ");
sql.Append("left join sys.extended_properties f on d.id=f.Major_id and f.minor_id=0 ");
// 如果只查询指定表,加上此条件
sql.Append("where d.name='"+tableName+"' ");
sql.Append("order by a.id,a.colorder");
DataTable dt = new DataTable();
THS.Tools.DataAccess obj = new DataAccess();
dt = obj.GetDataSet(sql.ToString()).Tables[0];
if(dt != null)
return dt;
else
return null;
}
insert into bm select 1,'销售一部','小张','货品A',1000
insert into bm select 2,'销售二部','小王','货品B',3000
insert into bm select 3,'销售三部','小乐','货品C',2000
insert into bm select 4,'销售四部','小马','货品D',1000select * into bm_temp from bm where 1<>1
--select * from bm_temp
alter table bm_temp add riqi datetime default getdate()declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+name
from syscolumns where id=object_id('bm_temp')
and name <>'riqi'
exec('insert into bm_temp('+@sql+') select '+@sql+' from bm')select * from bm_tempbmid bmname username hpmc gz riqi
1 销售一部 小张 货品A 1000 2008-02-27 16:51:55.990
2 销售二部 小王 货品B 3000 2008-02-27 16:51:55.990
3 销售三部 小乐 货品C 2000 2008-02-27 16:51:55.990
4 销售四部 小马 货品D 1000 2008-02-27 16:51:55.990
select @sql=isnull(@sql+',','')+name
from syscolumns where id=object_id('bm_temp')
and name <>'riqi'
exec('insert into bm_temp('+@sql+') select '+@sql+' from bm')上面这段能不能换成c#,我看不懂。555555~~~谢谢
strSql +="select @sql=isnull(@sql+',','')+name ";
strSql +="from syscolumns where id=object_id('bm_temp') and name <> 'riqi'";
strSql += "exec('insert into bm_temp('+@sql+') select '+@sql+' from bm') ";
增加时间列
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.DateTime");
column.AllowDBNull = false;
column.Caption = "Time";
column.ColumnName = "Time";
column.DefaultValue = System.DateTime.Now;
DataTable.Columns.Add(column);难道是我理解有误?
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
string strSql = "declare @sql varchar(8000) ";
strSql += "select @sql=isnull(@sql+',','')+name ";
strSql += "from syscolumns where id=object_id('bm_temp') and name <> 'riqi'";
strSql += "exec('insert into bm_temp('+@sql+') select '+@sql+' from bm') ";
SqlConnection cn = new SqlConnection("server=.;uid=sa;pwd=sa;database=pubs");
cn.Open();
SqlCommand cmd = new SqlCommand(strSql, cn);
cmd.ExecuteNonQuery();
Response.Write(Convert.ToInt32(cmd.ExecuteNonQuery()));
}
string lieming = "";
DataTable autoTable = DBO.GetSqlData(mystr);
if (autoTable.Rows.Count > 0)
{
for (int i = 0; i < autoTable.Rows.Count; i++)
{
lieming += autoTable.Rows[i]["COLUMN_NAME"].ToString()+",";
}
}
lieming = lieming.Substring(0,lieming.Length-1);
string addtbsql = "insert into " + tbname + " (" + lieming + ") select " + lieming + " from " + Tablename + " where " + keyname + "='" + TbId + "'";
然后执行这个addtbsql,就欧了。也谢谢你哦,借我思想。呵呵。搞定,给分