表的数据插入相关操作(identity,临时表,获取表列)
来源:互联网 发布:数据库开发与dba 编辑:程序博客网 时间:2024/06/11 17:50
alter procedure [dbo].[xxxx](
@newDatabaseName varchar(500), -- cn new database
@oldDatabaseName varchar(500)--us database
)
as
begin
/*TEST DATA
set @newDatabaseName='[eData_wqlNew]';
set @oldDatabaseName='[eData_wql]';
*/
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#eTables') and type='U')
begin
print 'EXSITES'
drop table #eTables;
end
create table #eTables
(
id [int] IDENTITY(1,1) NOT NULL,
name varchar(250) null
)
insert into #eTables select name from sysobjects where XTYPE='u' and name like 'e%'
select * from #eTables
declare @delSql varchar(max);
declare @insertSql varchar(max);
declare @newTableName varchar(500);
declare @oldTableName varchar(500);
declare @minId int,@maxId int;
set @minId=(select min(id) from #eTables)
set @maxId=(select max(id) from #eTables)
while (@minId<=@maxId)
begin
declare @tableName varchar(500);
select @tableName=name from #eTables where id=@minId;
print 'no.'+convert(varchar(500),@minId)+' '+convert(varchar(500),@tableName)
set @minId=@minId+1;
print @minId;
set @newTableName=@newDatabaseName+'.dbo.'+@tableName;
print '@newTableName:'+@newTableName;
set @oldTableName=@oldDatabaseName+'.dbo.'+@tableName;
print '@oldTableName:'+@oldTableName;
begin tran A --begin tran
set @delSql='delete from '+@newTableName;
print '@delSql:'+@delSql;
execute(@delSql);
--1\lock table(share)
--2\whether has identity
--3\open identity column insert(if has)
--4\table insert
--5\close identity column insert(if has)
--?6\think of the case that the table struture changed
declare @collist nvarchar(max);
declare @colSqL nvarchar(max);
--get the table col list(not think of 6)
/*set @colSqL='set @collist=(select name+'','' from sys.columns where object_id=OBJECT_ID('''+@oldTableName+''') for xml path(''''))';
print '@colSql:'+@colSql;
exec sp_executesql @colSqL, N'@collist varchar(max) output', @collist output
select @collist=substring(@collist,1,len(@collist)-1);
print '@collist:'+isnull(@collist,'null');*/
--get the table col list(think of 6)
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##newColInfo') and type='U')
begin
drop table ##newColInfo ;
end
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##oldColInfo') and type='U')
begin
drop table ##oldColInfo ;
end
execute('select * into ##newColInfo from '+@newTableName+' where 1=2');
execute('select * into ##oldColInfo from '+@oldTableName+' where 1=2');
--select newCol.name from (select name from tempdb..syscolumns where id=OBJECT_ID('tempdb..#newColInfo')) newCol join (select name from tempdb..syscolumns where id=OBJECT_ID('tempdb..#oldColInfo')) oldCol on newCol.name=oldCol.name
--set @colSqL='set @collist=(select name+'','' from sys.columns where object_id=OBJECT_ID('''+@oldTableName+''') for xml path(''''))';
--get the same col info
set @colSqL='set @collist=(select newCol.name+'','' from (select name from tempdb..syscolumns where id=OBJECT_ID(''tempdb..##newColInfo'')) newCol join (select name from tempdb..syscolumns where id=OBJECT_ID(''tempdb..##oldColInfo'')) oldCol on newCol.name=oldCol.name for xml path(''''))';
print '@colSql:'+@colSql;
exec sp_executesql @colSqL, N'@collist varchar(max) output', @collist output
select @collist=substring(@collist,1,len(@collist)-1);
print '@collist:'+isnull(@collist,'null');
--get the table has identity
declare @hasIdentity varchar(50);
Select @hasIdentity=OBJECTPROPERTY(OBJECT_ID( ''+@newTableName+''), 'TableHasIdentity ');
print '@hasIdentity:'+isnull(@hasIdentity,'null');
set @insertSql='select * from '+@newTableName+' with (TABLOCK);';--deny other trans to /insert/updata data;
if(@hasIdentity='1')
begin
set @insertSql+='set identity_insert '+ @newTableName +' on;';
end
set @insertSql+='insert into '+@newTableName+'('+@collist+')'+' select '+@collist+' from'+@oldTableName+';';
if(@hasIdentity='1')
begin
set @insertSql+='set identity_insert '+ @newTableName +' off;';
end
print '@insertSql:'+isnull(@insertSql,'null');
execute(@insertSql);
if @@error<>0 --has error
begin
rollback tran A--rollback
return;
--print convert(varchar(500),@minId)+'trans';
end
commit tran A
end
end
- 表的数据插入相关操作(identity,临时表,获取表列)
- 数据的分多次插入临时表操作
- 如何把数据批量插入具有Identity列的表
- 如何把数据批量插入具有Identity列的表
- oracle 修改表名、列名、字段类型、添加表列、删除表列 等相关操作
- 一个利用创建临时表后插入数据的查询
- 将存储过程里的数据 插入临时表
- 删除表数据并插入临时表
- SQL Server 临时表的使用以及临时表数据插入问题
- SQL SERVER 插入数据获取自增ID,SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY的比较
- MySQL数据表列属性操作
- mysql 数据库整理——数据表列的操作
- 获取表数据的插入SQL
- thinkphp3.2.3 临时表数据操作
- 视图+视图DML操作+获取用户的所有表名或视图名+视图插入数据
- 将查到的数据插入一个自增编号的临时表
- 把查询得到的数据插入到新的临时表中
- sqlserver 循环临时表插入数据到另一张表
- Tika
- 类似于选择城市相同的首字母放在一起的效果
- 人脸识别网上资源列表(装载)
- java实现简单的单点登录
- 不同风格的游戏技术人 - Game Developer stereotypes
- 表的数据插入相关操作(identity,临时表,获取表列)
- scrollTo vs scrollBy
- 数据库时间类型
- 北京多数受访者称长假高速路拥堵并非因免费-长假-拥堵-高速免费
- iOS Nav+TabBar下的self.title,self.navigationItem.title,self.tabBarItem.title区别和联系
- 常用sql语句
- JVM 垃圾回收 总结
- Oracle中sequence简介
- struts2 中的 addActionError 、addFieldError、addActionMessage的方法【转】