如何将一个表中的所有值插入到到另一个表里
1表:EKC_ERP_PRODUCT_WAREHOUSE
2表:EKC_ERP_Y_QIMO_WAREHOUSE
现将1表的所有插入2表(1表与2表字段相同),请哪位大仙给个代码
1表:EKC_ERP_PRODUCT_WAREHOUSE
2表:EKC_ERP_Y_QIMO_WAREHOUSE
现将1表的所有插入2表(1表与2表字段相同),请哪位大仙给个代码
调试欢乐多
create database test
go
use test
go
create table a1
(
id int identity(1,1) primary key ,
aname nvarchar(20)
)
go
insert into a1 values ('aaa')
insert into a1 values ('bbb')
go
select * from a1
go--这里写一个判断,判断A2是否存在,存在需要删除
select * into a2 from a1
大概这样,找下语句语法了.偶经常不用忘了
(只复制结构,源表名:a 新表名:b) (Access可用) 法一:select * into b from a where 1<>1 法二:select top 0 * into b from a
拷贝表
(拷贝数据,源表名:a 目标表名:b) (Access可用) insert into b(a, b, c) select d,e,f from b;
insert into tb2
select * from tb1试试
{
//创建本地连接字符串
string strConn = @"Data Souce=(Local)\SQLExpress;Initial CataLog=" + DBName + ";Integrated Security=SSPI";
SqlConnection _conn = new SqlConnection(strConn); //创建数据库连接对象
_conn.Open(); //打开数据库 string sqlStr = "select * from EKC_ERP_PRODUCT_WAREHOUSE"; DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(sqlStr, _conn);
adp.Fill(ds, "Tables"); SqlCommand cmd = null; //遍历[EKC_ERP_PRODUCT_WAREHOUSE]表中的各个字段
//比如[字段1、字段2、字段3]
foreach (DataRow dr in ds.Tables[0].Rows)
{
string strCol1 = dr["字段1"].ToString();
string strCol2 = dr["字段2"].ToString();
string strCol3 = dr["字段3"].ToString(); string strInsert = "insert into EKC_ERP_Y_QIMO_WAREHOUSE (字段1,字段2,字段3) values('"
+ strCol1 + "','"
+ strCol2 + "','"
+ strCol3 + "')"; cmd = new SqlCommand(strInsert, _conn);
cmd.ExecuteNonQuery(); //执行SQL语句
} _conn.Close();
}
SELECT [Field1],[Field2]......[Fieldn]
FROM [Table1].....
法一:select * into b from a where 1<>1(仅用于SQlServer)
法二:select top 0 * into b from a
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..