这样的话有点麻烦要修改标识符如果这时间在有其它用户操作这样表可能就会出现标识符为空了set IDENTITY_INSERT 数据库A.dbo.Table1 off insert into 数据库A.dbo.Table1 select A列,B列,C列 from 数据库B.dbo.Table2 set IDENTITY_INSERT 数据库A.dbo.Table1 on
都不知道你是什么数据库,怎么帮你写啊 oeacle还是sql server啊
一下代码经过测试,有效 首先将test表中id 与test2 的数据删除 然后将test 和test2 的结果一起插入一个新表test3 删除test表,将test3表加规则, 搞定收工 delete from test where id in (select id from test2) goselect * into test3 from ( select [id],[content] from test union select [id],[content] from test2 ) as adrop table test/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_test3 ( id int NOT NULL IDENTITY (1, 1), [content] nvarchar(50) NULL ) ON [PRIMARY] GO SET IDENTITY_INSERT dbo.Tmp_test3 ON GO IF EXISTS(SELECT * FROM dbo.test3) EXEC('INSERT INTO dbo.Tmp_test3 (id, [content]) SELECT id, [content] FROM dbo.test3 WITH (HOLDLOCK TABLOCKX)') GO SET IDENTITY_INSERT dbo.Tmp_test3 OFF GO DROP TABLE dbo.test3 GO EXECUTE sp_rename N'dbo.Tmp_test3', N'test', 'OBJECT' GO ALTER TABLE dbo.test ADD CONSTRAINT PK_test3 PRIMARY KEY CLUSTERED ( id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO COMMIT
Use A Go insert Into A.TableA Select * From B.dbo.TableB
再遍历出入到A数据库中
号要和B表的ID号完全一致,不然就没意义了
是不是这样
A库的数据主键
1
2
3
B库的数据主键为10
11
12
这样不重复?也就是说你要把B库的10,11,12也拿到A里形成1,2,3,10,11,12
比如说A数据库本来是
1
2
3
4
5
6
然后我建了个B数据库
将
2
3
4
插到B中然后将A的
2
3
4
删掉
然后再回调
就是这个原理,是不会出现重复的
现在思路已经有了,关键是如何将表示列去掉
将标识列取消然后插入数据
再回复标识列就好了,关键是如何取消和恢复标识列
insert into 数据库A.dbo.Table1 select A列,B列,C列 from 数据库B.dbo.Table2
set IDENTITY_INSERT 数据库A.dbo.Table1 on
都不知道你是什么数据库,怎么帮你写啊
oeacle还是sql server啊
首先将test表中id 与test2 的数据删除
然后将test 和test2 的结果一起插入一个新表test3
删除test表,将test3表加规则,
搞定收工
delete from test where id in (select id from test2)
goselect * into test3 from
(
select [id],[content] from test
union
select [id],[content] from test2
) as adrop table test/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_test3
(
id int NOT NULL IDENTITY (1, 1),
[content] nvarchar(50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_test3 ON
GO
IF EXISTS(SELECT * FROM dbo.test3)
EXEC('INSERT INTO dbo.Tmp_test3 (id, [content])
SELECT id, [content] FROM dbo.test3 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_test3 OFF
GO
DROP TABLE dbo.test3
GO
EXECUTE sp_rename N'dbo.Tmp_test3', N'test', 'OBJECT'
GO
ALTER TABLE dbo.test ADD CONSTRAINT
PK_test3 PRIMARY KEY CLUSTERED
(
id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO
COMMIT
Go
insert Into A.TableA
Select * From B.dbo.TableB