求教高手帮我写一下~顺便帮我解释一下~本人刚来CSDN没多久,所依一没多少分。我会把我所有的分都拿出来!希望大家帮帮忙
解决方案 »
- 有懂TSM的吗
- sql2000分页问题,如果是多表,表名参数那该怎么写才好?
- sql2008创建用户权限问题????在线等。。。。。。。。
- 怎么让 SQL Server 2000个人版升级到企业版?可以吗 ?
- 关于SQL SERVER的初级问题
- 如何将access数据库中的表py复制一份为py1,如何将表py更名为xt。这两个SQL语句怎么写?
- 最大值查询,求解!
- 高分急:本地DB与远程网站DB的同步问题??
- 如何按星期几统计??
- 如何将txt文件里的数据导入到sql数据表中
- 急,SQL查询问题!请求帮助!
- 求助,关于reinstall SQL Server + Reporting server
as
set nocount on
declare @s varchar(10)
set @s=convert(varchar(6),getdate(),112)
exec('if object_id(''b'+@s+''') is not null
drop table b'+@s+';'
+' select * into b'+@s+' from a;'
+'select * from b'+@s)
go
-->调用
exec sp_test1
if object_id('a') is not null
drop table a
go
create table a(test varchar(10))
insert a select 'test1'
union all select 'test2'
union all select 'test3'
go
-->创建存储过程
if object_id('sp_test1') is not null
drop proc sp_test1
go
create proc sp_test1
as
set nocount on
declare @s varchar(10)
set @s=convert(varchar(6),getdate(),112)
exec('if object_id(''b'+@s+''') is not null
drop table b'+@s+';'
+' select * into b'+@s+' from a;'
+'select * from b'+@s)
go
-->调用过程生成新表
exec sp_test1
--结果
select * from b200904
/*
test
----------
test1
test2
test3(3 行受影响)
*/
drop table b'+@s+';'
+' select * into b'+@s+' from a;'
+'select * from b'+@s)
不太明白
代码第一二句判断是否有存在B+当前年月的表,如果存在就删除
第三句就是复制创建
第四句是查询
这一句为什么是使用select 插入数据不是用insert吗?
而新建的表周中只是包含这两列 但还有其他列?该怎么写?
----将Tableb中的数据查找出来Insert到数据表A中
select * into tableA from tableB
select @select = 'select * into ' + @tableName + 'from userA'
为什么执行的时候报错
select @select = 'select * into ' + @tableName + 'from userA'
exec sp_executesql @select
为什么执行的时候报错,错误信息是:
消息 102,级别 15,状态 1,第 1 行
'200904' 附近有语法错误。
as
declare @sql nvarchar(1000) , @tableName char(6),
@select nvarchar(1000) --
set @tableName= convert(char(6),getdate(),112)
select @sql= ' create table ['+ @tableName +'](userName char(8) not null,userFee money not null,userID int not null)'
select @select = 'select * into ' + @tableName + 'from userA'
exec sp_executesql @sql
exec sp_executesql @select
go
exec dbo.create_table
这是我我写的存储过程,最后调用是时候提示错误信息:消息 102,级别 15,状态 1,第 1 行
'200904' 附近有语法错误。
给我看下我的代码那里有错
drop table a
go
create table a(test varchar(10))
insert a select 'test1'
union all select 'test2'
union all select 'test3'
go
--declare @sql varchar(8000)
--set @sql=''
--declare @a varchar(10)
--set @a='a'
--if object_id(@a) is not null
--Begin
-- set @sql='drop table '+@a
-- print @sql
-- exec(@sql)
--End
-->创建存储过程
if object_id('sp_test1') is not null
drop proc sp_test1
go
create proc sp_test1
as
set nocount on
declare @sql varchar(8000)
set @sql=''
declare @s varchar(10)
set @s=convert(varchar(6),getdate(),112)
if object_id('b'+@s) is not null
Begin
set @sql='insert into b'+@s+' select * from a'
exec(@sql)
End
else
Begin
set @sql='create table b'+@s+'(test varchar(10))'
exec(@sql)
set @sql='insert into b'+@s+' select * from a'
exec(@sql)
End
exec sp_test1
用这个吧
-->创建测试表
if object_id('a') is not null
drop table a
go
create table a(username varchar(10),usefree int)
insert a select 'test1',100
union all select 'test2',200
union all select 'test3',300
go-->创建存储过程
if object_id('sp_test1') is not null
drop proc sp_test1
go
create proc sp_test1
as
set nocount on
declare @sql varchar(8000)
set @sql=''
declare @s varchar(10)
set @s=convert(varchar(6),getdate(),112)
if object_id('b'+@s) is not null
Begin
set @sql='insert into b'+@s+' select * from a'
exec(@sql)
End
else
Begin
set @sql='create table b'+@s+'(username varchar(10),userfree int)'
exec(@sql)
set @sql='insert into b'+@s+' select * from a'
exec(@sql)
End
exec sp_test1
这样就和你的表结构一样了
这句话就是建表,直接在字符串上加就是了
create proc dbo.create_table
as
declare @sql nvarchar(1000) , @tableName char(6),
@select nvarchar(1000) --
set @tableName= convert(nvarchar(6),getdate(),112)
select @sql= N'if object_id(''['+@tableName+']'') is null
create table ['+ @tableName +N'](userName char(8) not null,userFee money not null,userID int not null)'
select @select = N'insert ' + @tableName + N' select * from userA'
exec sp_executesql @sql
exec sp_executesql @select
go
exec dbo.create_table
不如说
select '我们'会是乱码
而select N'我们' 就是中文
if object_id('a') is not null
drop table a
go
create table a(username varchar(10),usefree int)
insert a select '张三',100
union all select '李四',200
union all select '王五',300
go-->创建存储过程
if object_id('sp_test1') is not null
drop proc sp_test1
go
create proc sp_test1
as
set nocount on
declare @sql nvarchar(1000) , @tableName nvarchar(6),
@select nvarchar(1000) --
set @tableName= convert(nvarchar(6),getdate(),112)
select @sql= N'if object_id(''[b'+@tableName+']'') is null
create table [b'+ @tableName +N'](userName char(8) not null,usefree money not null,userID int not null)'
select @select = N'insert b' + @tableName + N' select *,0 from a where not exists(select 1 from b'+@tablename+N' b where a.username=b.username and a.usefree=b.usefree)'
exec sp_executesql @sql
exec sp_executesql @select
goexec sp_test1select * from b200904
/*
userName userFee userID
-------- --------------------- -----------
张三 100.00 0
李四 200.00 0
王五 300.00 0(3 行受影响)
*/drop table b200904
if object_id('a') is not null
drop table a
go
create table a(username varchar(10),usefree int)
insert a select '张三',100
union all select '李四',200
union all select '王五',300
go-->创建存储过程
if object_id('sp_test1') is not null
drop proc sp_test1
go
create proc sp_test1
as
set nocount on
declare @sql nvarchar(1000) , @tableName nvarchar(6),
@select nvarchar(1000) --
set @tableName= convert(nvarchar(6),getdate(),112)
select @sql= 'if object_id(''[b'+@tableName+']'') is null
create table [b'+ @tableName +'](userName char(8) not null,usefree money not null,userID int not null)'
select @select = 'insert b' + @tableName + ' select *,0 from a where not exists(select 1 from b'+@tablename+' b where a.username=b.username and a.usefree=b.usefree)'
exec(@sql)
exec(@select)
goexec sp_test1select * from b200904
/*
userName userFee userID
-------- --------------------- -----------
张三 100.00 0
李四 200.00 0
王五 300.00 0(3 行受影响)
*/drop table b200904
create proc sp_b表
set nocount on
declare @s varchar(10)
set @s=convert(varchar(6),getdate(),112)
exec('if object_id(''b'+@s+''') is not null
drop table b'+@s+';'
+' select * into b'+@s+' from a;'
+'select * from b'+@s)
go
-->调用
exec sp_b表