有a,b两个表,还有一个a,b的字段对应表,从将a表的字段名存到一个数组中,读取对应字段表,然后利用循环语句,利用insert语句在b表中抓取数据,每次抓取一个字段,为什么会出现错行的问题,该怎么解决,用update,但是我update的where条件不会写啊private void button5_Click(object sender, EventArgs e)
{
string strSrc = "Server=LIUKAI-THINK;Integrated security=SSPI;database=CkMonitor";
using (SqlConnection thisConnection = new SqlConnection(strSrc))
{
thisConnection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT * FROM SelectedFields", thisConnection);//读取对应关系表
DataSet thisSet = new DataSet();
thisAdapter.Fill(thisSet, "SelectedFields");
DataTable tbl1 = thisSet.Tables[0];
int rowCount = tbl1.Rows.Count;
int columnCount = tbl1.Columns.Count;
string[]OriginalTableName=new string[rowCount];//原始数据库表名
string[]TargetTableName=new string[rowCount];//目标数据库表名
for (int i = 0; i < rowCount; i++)
{
OriginalTableName[i]=tbl1.Rows[i][0].ToString();
TargetTableName[i]=tbl1.Rows[i][1].ToString();
}
for (int i = 0; i < rowCount; i++)
{
int count=0;
for(int j=0;j<columnCount;j++)
{
if(DBNull.Value!=tbl1.Rows[i][j])
{
count++;
}
}
//读取目标数据库的字段名并存在一个数组Target里
ArrayList Target = new ArrayList();
string strField = "SELECT c.name FROM syscolumns AS c inner join sysobjects d on c.id=d.id and d.xtype='U' and d.name<>'dtproperties' where d.name='"+TargetTableName[i]+"'";
SqlCommand thisCommandField = thisConnection.CreateCommand();
thisCommandField.CommandText =strField;
SqlDataReader thisReaderTarget = thisCommandField.ExecuteReader();
while (thisReaderTarget.Read())
{
Target.Add(thisReaderTarget["name"]);
}
thisReaderTarget.Close();
for (int k = 2; k < count; k++)
{
string str1 = "INSERT INTO " + TargetTableName[i] + "(" + Target[k - 1] + ") SELECT " + tbl1.Rows[i][k + 1] + " FROM " + OriginalTableName[i];
SqlCommand command = new SqlCommand(str1, thisConnection);
command.ExecuteNonQuery();
}
}
MessageBox.Show("插入数据成功!");
Application.Exit();
}
}57 1 NULL NULL NULL NULL
58 2 NULL NULL NULL NULL
59 NULL 2011-01-01 00:00:00.000 NULL NULL NULL
60 NULL 2011-02-02 00:00:00.000 NULL NULL NULL
61 NULL NULL 122 NULL NULL
62 NULL NULL 13 NULL NULL
63 NULL NULL NULL 1221 NULL
64 NULL NULL NULL 13 NULL
65 NULL NULL NULL NULL 11
66 NULL NULL NULL NULL 13
{
string strSrc = "Server=LIUKAI-THINK;Integrated security=SSPI;database=CkMonitor";
using (SqlConnection thisConnection = new SqlConnection(strSrc))
{
thisConnection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT * FROM SelectedFields", thisConnection);//读取对应关系表
DataSet thisSet = new DataSet();
thisAdapter.Fill(thisSet, "SelectedFields");
DataTable tbl1 = thisSet.Tables[0];
int rowCount = tbl1.Rows.Count;
int columnCount = tbl1.Columns.Count;
string[]OriginalTableName=new string[rowCount];//原始数据库表名
string[]TargetTableName=new string[rowCount];//目标数据库表名
for (int i = 0; i < rowCount; i++)
{
OriginalTableName[i]=tbl1.Rows[i][0].ToString();
TargetTableName[i]=tbl1.Rows[i][1].ToString();
}
for (int i = 0; i < rowCount; i++)
{
int count=0;
for(int j=0;j<columnCount;j++)
{
if(DBNull.Value!=tbl1.Rows[i][j])
{
count++;
}
}
//读取目标数据库的字段名并存在一个数组Target里
ArrayList Target = new ArrayList();
string strField = "SELECT c.name FROM syscolumns AS c inner join sysobjects d on c.id=d.id and d.xtype='U' and d.name<>'dtproperties' where d.name='"+TargetTableName[i]+"'";
SqlCommand thisCommandField = thisConnection.CreateCommand();
thisCommandField.CommandText =strField;
SqlDataReader thisReaderTarget = thisCommandField.ExecuteReader();
while (thisReaderTarget.Read())
{
Target.Add(thisReaderTarget["name"]);
}
thisReaderTarget.Close();
for (int k = 2; k < count; k++)
{
string str1 = "INSERT INTO " + TargetTableName[i] + "(" + Target[k - 1] + ") SELECT " + tbl1.Rows[i][k + 1] + " FROM " + OriginalTableName[i];
SqlCommand command = new SqlCommand(str1, thisConnection);
command.ExecuteNonQuery();
}
}
MessageBox.Show("插入数据成功!");
Application.Exit();
}
}57 1 NULL NULL NULL NULL
58 2 NULL NULL NULL NULL
59 NULL 2011-01-01 00:00:00.000 NULL NULL NULL
60 NULL 2011-02-02 00:00:00.000 NULL NULL NULL
61 NULL NULL 122 NULL NULL
62 NULL NULL 13 NULL NULL
63 NULL NULL NULL 1221 NULL
64 NULL NULL NULL 13 NULL
65 NULL NULL NULL NULL 11
66 NULL NULL NULL NULL 13
解决方案 »
- 将查询结果拆成多个列
- (50分)求存储过程:问题比较长,请耐心看一下!!!
- 效率问题???
- SQL问题?(在线)
- 事件探查器跟踪到含有“password”的SQL语句时显示不出来,有什么方法解决。
- 还原数据库时已经还原了备份文件*.bak和日志*.trn,可怎么还是*.bak中的日期的数据?
- 一条SQL判断两时间区间是否交叉、两字符串是否有重复字符
- [求助]在存储过程中可否根据变量的值动态指定某一查询条件是否起作用?
- 关于Sql server中数据设备与数据库的建立,先谢了!!!分不够的话还加!
- 还是那个写SQL语句的问题,只是增加了表的个数。请大侠们赶快进来回答,要不然又被别人抢先了!:)
- 数据库死锁问题求助!
- 【高分的简单问题】关于sql数据库表的插入问题
为啥不用
SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
这样的方法呢