我现在又两个数据库,a和b,a中有一个“人口信息表”,b中有一个“常住人口信息表”,“常住人口信息表”是空表。我建立了如下链接
string sql1 = "select * from 人口信息表"; string constr1 = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + DBname1;//定义连接 SqlConnection conn1 = new SqlConnection(constr1);//建立连接
SqlDataAdapter da1 = new SqlDataAdapter(sql1, conn1);//连接数据适配器
SqlCommandBuilder cb1 = new SqlCommandBuilder(da1);//建立更新数据库语句
DataSet ds1 = new DataSet();
da1.Fill(ds1, "table");
string sql2 = "select * from 常住人口信息表"; string constr2 = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + DBname2;//定义连接 SqlConnection conn2 = new SqlConnection(constr2);//建立连接
SqlDataAdapter da2 = new SqlDataAdapter(sql2, conn2);//连接数据适配器
SqlCommandBuilder cb2 = new SqlCommandBuilder(da2);//建立更新数据库语句
DataSet ds2 = new DataSet();
da2.Fill(ds2, "table");
我现在想把“人口信息表”中的每一条记录都复制到“常住人口信息表”中,请问怎么实现,如果我的思路错了,请各位加以纠正,请给我详细的说明,谢谢
string sql1 = "select * from 人口信息表"; string constr1 = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + DBname1;//定义连接 SqlConnection conn1 = new SqlConnection(constr1);//建立连接
SqlDataAdapter da1 = new SqlDataAdapter(sql1, conn1);//连接数据适配器
SqlCommandBuilder cb1 = new SqlCommandBuilder(da1);//建立更新数据库语句
DataSet ds1 = new DataSet();
da1.Fill(ds1, "table");
string sql2 = "select * from 常住人口信息表"; string constr2 = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + DBname2;//定义连接 SqlConnection conn2 = new SqlConnection(constr2);//建立连接
SqlDataAdapter da2 = new SqlDataAdapter(sql2, conn2);//连接数据适配器
SqlCommandBuilder cb2 = new SqlCommandBuilder(da2);//建立更新数据库语句
DataSet ds2 = new DataSet();
da2.Fill(ds2, "table");
我现在想把“人口信息表”中的每一条记录都复制到“常住人口信息表”中,请问怎么实现,如果我的思路错了,请各位加以纠正,请给我详细的说明,谢谢
1。表结构相同的表,且在同一数据库(如,table1,table2) Sql :insert into table1 select * from table2 (完全复制) insert into table1 select distinct * from table2(不复制重复纪录) insert into table1 select top 5 * from table2 (前五条纪录) 2。 不在同一数据库中(如,db1 table1,db2 table2) sql: insert into db1..table1 select * from db2..table2 (完全复制) insert into db1..table1 select distinct * from db2table2(不复制重复纪录) insert into tdb1..able1 select top 5 * from db2table2 (前五条纪录) 3. 表结构不同的表或复制部分纪录(如,dn_user,dn_user2) a. 建一个新表[DN_UserTemp](在老表dn_user上增加一列) CREATE TABLE [DN_UserTemp] ( [Num] [numeric](18, 0) IDENTITY (1, 1) NOT NULL)
[Id] [idtype] NOT NULL ,
[Name] [fntype] NOT NULL ,
[Descript] [dstype] NULL ,
[LogonNm] [idtype] NOT NULL ,
[Password] [idtype] NULL ,
[Gender] [char] (1) NULL ,
[Quited] [booltype] NOT NULL,
[OffDuty] [booltype] NOT NULL ,
[Stopped] [booltype] NOT NULL,
[OSBind] [booltype] NOT NULL,
[Domain] [idtype] NULL ,
[EMail] [fntype] NULL ,
[UnitId] [idtype] NULL ,
[BranchId] [idtype] NULL ,
[DutyId] [idtype] NULL ,
[LevelId] [idtype] NULL ,
[ClassId] [idtype] NULL ,
[TypeId] [idtype] NULL ,
[IP] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[ExpireDT] [datetime] NULL ,
[Sort] [int] NOT NULL ,
[AllowDel] [booltype] NOT NULL,
[UnitChief] [booltype] NOT NULL,
[BranchChief] [booltype] NOT NULL ,
[UnitDeputy] [booltype] NOT NULL ,
[BranchDeputy] [booltype] NOT NULL , [Num] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ) ON [PRIMARY] b. 将dn_uer2的数据拷入dn_usertemp sql:insert into dn_usertemp select * from dn_user2 c.将dn_usertemp 拷入dn_user sql: declare @i int
declare @j int
declare @Name fntype
set @i=1
select @j=count(*) from dn_usertemp
while @i<@j 1
begin select @Name=Name from dn_usertemp where Num=@i
print @Name
insert into dn_user (Name) values (@Name) where Num=@i
select @i=@i 1
end --------------------------- creat到-- 然后把数据库名改成想复制到的那个库的名称
介绍:在软件开发中,把数据从一个地方复制到另一个地方是一个普遍的应用。 在很多不同的场合都会执行这个操作,包括旧系统到新系统的移植,从不同的数据库备份数据和收集数据。 ASP.NET 2.0有一个SqlBulkCopy类,它可以帮助你从不同的数据源复制数据到SQL SERVER数据库。 本文中我将示范SqlBulkCopy类的不同应用。
就用insert into a (,,,) select ,,, from b
{
string sql = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
SqlConnection Conn = new SqlConnection(sql);
Conn.Open();
SqlCommand Cmd = new SqlCommand("select * from student", Conn);
SqlDataReader reader = Cmd.ExecuteReader();
SqlConnection destinationConnection = new SqlConnection(sql);
// 打开连接
destinationConnection.Open(); SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString);
bulkCopy.BatchSize = 500;
bulkCopy.NotifyAfter = 1000;
// bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler();--这个地方不值得哪里错啦!
bulkCopy.DestinationTableName = "studentinfo";
bulkCopy.WriteToServer(reader);
reader.Close();
}
webconfig:
<appSettings>
<add key="ConnectionString" value="Data Source=PC-200903131045\HUGUO;Initial Catalog=Test;User ID=sa;Password=123456"></add>
</appSettings>
周公测试出现问题
有个地方有错误!
请指正