--------下面是過程 ,把tableB拷貝給tableA ,如果結構不同,則用把字段列出來 ,
插入進去
Create proc kk
as
insert into tableA(col1,....)
select col1,... from tableB where id not in(select id from TableA)
插入進去
Create proc kk
as
insert into tableA(col1,....)
select col1,... from tableB where id not in(select id from TableA)
我这两个表每表有30多个字段,而且像您这个过程,怎么添加多余的字段,和返回重复的记录ID,谢谢关注!
asselect TableA.* from TableA inner join TableB on TableA.Id=TableB.Id ----返回重復的
insert into tableA(col1,....)
select col1,... from tableB where id not in(select id from TableA)
select A.name from
(select * from syscolumns where id=object_id('TableB') ) a
inner join
(select * from syscolumns where id=object_id('TableA') ) b
on a.name<>b.name
怎么攒到一起呢?您看这样对吗?呵呵.太笨了!不太清楚的地方在程序中用中文写的呵呵.您在给看看.以前写asp都是一句sql句搞定,现在要存储过程了.呵呵.谢谢.各可能也有不对的地方Create proc kk
as
select A.name from
(select * from syscolumns where id=object_id('TableB') ) a
inner join
(select * from syscolumns where id=object_id('TableA') ) b
on a.name<>b.name
if 不知些什么,应该是有不同字段 then begin if 这里还应有检测是不是有重复ID,如果没有 then
alter table tableA add 字段 類形(这里我也也不只是什么字段啊?) insert into tableA select * from tableB
else
insert into tableA select * from tableB where id not in(select id from TableA) 列出重复ID end
end
--谅解
Create proc kk
@tba nvarchar(4000),@tbb nvarchar(4000)
as
declare @s1 varchar(8000)
declare @s2 varchar(8000)
declare @s3 varchar(8000)
set @s1=''
set @s2=''
set @s3=''
select @s1=@s1+'alter table '+@tba+' add '+a.name+' '+b.name+char(13)
from syscolumns a left join systypes b on a.xtype=b.xusertype
where id=object_id(@tbb)
and a.name not in (select name from syscolumns
where id=object_id(@tba))exec(@s1)
select @s2=@s2+'alter table '+@tbb+' add '+a.name+' '+b.name+char(13)
from syscolumns a left join systypes b on a.xtype=b.xusertype
where id=object_id(@tba)
and a.name not in (select name from syscolumns
where id=object_id(@tbb))exec(@s2)
set @s3='insert into '+@tba+' select * from '+@tbb+' where id not in (select id from '+@tba+')'
exec(@s3)
go
--调用方式
exec kk 'orders','bak'
as
Declare @s varchar(30),@s1 varchar(10)
Declare phh cursor for
select A.name,type_name(A.xusertype)+'('+length+')' from
(select * from syscolumns where id=object_id('TableB') ) a
inner join
(select * from syscolumns where id=object_id('TableA') ) b
on a.name<>b.name
open phh
fetch next from phh into @s,@s1
while @@fetch_status=0
begin
exec('alter table TableA add ' +@s+' '+@s1 )
fetch next from phh into @s
end
close phh
deallocate phh
select TableA.* from TableA inner join TableB on TableA.Id=TableB.Id ---先列出重復的id
insert into tableA select * from tableB where id not in(select id from TableA) -----插入不重復的
select A.name,type_name(A.xusertype)+'('+length+')' from
(select * from syscolumns where id=object_id('TableB') ) a
inner join
(select * from syscolumns where id=object_id('TableA') ) b
on a.name<>b.name
as
Declare @s varchar(30),@s1 varchar(10)
Declare phh cursor for
select A.name,type_name(A.xusertype)+'('+A.length+')' from
(select * from syscolumns where id=object_id('TableB') ) a
inner join
(select * from syscolumns where id=object_id('TableA') ) b
on a.name<>b.name
open phh
fetch next from phh into @s,@s1
while @@fetch_status=0
begin
exec('alter table TableA add ' +@s+' '+@s1 )
fetch next from phh into @s
end
close phh
deallocate phh
select TableA.* from TableA inner join TableB on TableA.Id=TableB.Id ---先列出重復的id
insert into tableA select * from tableB where id not in(select id from TableA) -----插入不重復的
select name,type_name(xusertype)+'('+cast(length as varchar(15))+')' from syscolumns where id=object_id('tableA')
Create proc kk
as
Declare @s varchar(30),@s1 varchar(10)
Declare phh cursor for
select A.name,type_name(A.xusertype)+'('+cast(A.length as varchar(15))+')' from
(select * from syscolumns where id=object_id('TableB') ) a
inner join
(select * from syscolumns where id=object_id('TableA') ) b
on a.name<>b.name
open phh
fetch next from phh into @s,@s1
while @@fetch_status=0
begin
exec('alter table TableA add ' +@s+' '+@s1 )
fetch next from phh into @s
end
close phh
deallocate phh
select TableA.* from TableA inner join TableB on TableA.Id=TableB.Id ---先列出重復的id
insert into tableA select * from tableB where id not in(select id from TableA) -----插入不重復的
看不懂啊!呵呵。您太客气了,我怎么才能提前声明里面代表表的a,b.我a是数据库中的表,b是excel表。
您太强了!
再给说得详细些吧。谢谢
Create proc kk
@tba nvarchar(4000),@tbb nvarchar(4000)
as
declare @s1 varchar(8000)
declare @s2 varchar(8000)
declare @s3 varchar(8000)
set @s1=''
set @s2=''
set @s3=''
select @s1=@s1+'alter table '+@tba+' add '+a.name+' '+b.name
+case when b.name in ('numeric','decimal') then +' ('+ cast(columnproperty(a.id,a.name,'PRECISION') as nvarchar)
+','+cast(isnull(columnproperty(a.id,a.name,'Scale'),0) as nvarchar)+')'
else +'' end
+char(13)
from syscolumns a left join systypes b on a.xtype=b.xusertype
where id=object_id(@tbb)
and a.name not in (select name from syscolumns
where id=object_id(@tba))
exec(@s1)select @s1=@s1+'alter table '+@tbb+' add '+a.name+' '+b.name
+case when b.name in ('numeric','decimal') then +' ('+ cast(columnproperty(a.id,a.name,'PRECISION') as nvarchar)
+','+cast(isnull(columnproperty(a.id,a.name,'Scale'),0) as nvarchar)+')'
else +'' end
+char(13)
from syscolumns a left join systypes b on a.xtype=b.xusertype
where id=object_id(@tba)
and a.name not in (select name from syscolumns
where id=object_id(@tbb))exec(@s2)
set @s3='insert into '+@tba+' select * from '+@tbb+' where id not in (select id from '+@tba+')'
exec(@s3)
go
Create proc kk
@tba nvarchar(4000),@tbb nvarchar(4000)
as
declare @s1 varchar(8000)
declare @s2 varchar(8000)
declare @s3 varchar(8000)
set @s1=''
set @s2=''
set @s3=''
select @s1=@s1+'alter table '+@tba+' add '+a.name+' '+b.name
+case when b.name in ('numeric','decimal','nvarchar','char','nchar') and columnproperty(a.id,a.name,'PRECISION')>0 and isnull(columnproperty(a.id,a.name,'Scale'),0)>0
then +'('+ cast(columnproperty(a.id,a.name,'PRECISION') as nvarchar)+','+cast(isnull(columnproperty(a.id,a.name,'Scale'),0) as nvarchar)+')'
when b.name in ('numeric','decimal','nvarchar','char','nchar') and columnproperty(a.id,a.name,'PRECISION')>0 and isnull(columnproperty(a.id,a.name,'Scale'),0)=0
then +'('+ cast(columnproperty(a.id,a.name,'PRECISION') as nvarchar)+')'
else +'' end
+char(13)
from syscolumns a left join systypes b on a.xtype=b.xusertype
where id=object_id(@tbb)
and a.name not in (select name from syscolumns
where id=object_id(@tba))exec(@s1)select @s2=@s2+'alter table '+@tbb+' add '+a.name+' '+b.name
+case when b.name in ('numeric','decimal','nvarchar','char','nchar') and columnproperty(a.id,a.name,'PRECISION')>0 and isnull(columnproperty(a.id,a.name,'Scale'),0)>0
then +'('+ cast(columnproperty(a.id,a.name,'PRECISION') as nvarchar)+','+cast(isnull(columnproperty(a.id,a.name,'Scale'),0) as nvarchar)+')'
when b.name in ('numeric','decimal','nvarchar','char','nchar') and columnproperty(a.id,a.name,'PRECISION')>0 and isnull(columnproperty(a.id,a.name,'Scale'),0)=0
then +'('+ cast(columnproperty(a.id,a.name,'PRECISION') as nvarchar)+')'
else +'' end
+char(13)
from syscolumns a left join systypes b on a.xtype=b.xusertype
where id=object_id(@tba)
and a.name not in (select name from syscolumns
where id=object_id(@tbb))exec(@s2)
set @s3='insert into '+@tba+' select * from '+@tbb+' where id not in (select id from '+@tba+')'
exec(@s3)
go
让您耽误时间了
不好意思
还是那个问题
您写的程序我怎么用阿
我的表a是sql server中的数据库,tableb是excel,我原来是这样读的
OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=YES;IMEX=1;database=f:\student\manager\excel\2.xls;','select * from [Sheet1$]') as i where not exists(select * from main2 where 学号=i.学号)
我怎么才能在程序中声明a,b阿
谢谢您