declare @name varchar(50)
declare @sql varchar(1000)
set @name ='AlarmCountConfig_'+convert(varchar(23), getdate(),112);
set @sql = 'select top 0 * into '+@name+' from AlarmCountConfig';
print @sql
exec(@sql)
declare @Count int
declare @LastTableName varchar(100)
declare @Sql varchar(1000)
set @Count=100
set @LastTableName='2018-10-30'
while @Count>0
begin
set @Sql='drop table tb_AccessLayerTraffic_'+
CONVERT(varchar,DATEADD(day,-@Count,@LastTableName),112)
exec (@Sql)
set @Count=@Count-1
可配合存储过程和作业来定期执行，实现表的动态生成和过期数据的动态清理
建表declare @name varchar(50)declare @sql varchar(1000)set @name ='AlarmCountConfig_'+convert(varchar(120), getdate(),112);set @sql = 'select top 1 * into '+@name+' from AlarmCountConfig';print @sq...
有些时候需要根据变量批量创建表，下面是个ms sql根据表明变量动态创建表存储过程实例，在MSSQL2008下调试通过。
USE [UNICOM_MAIL_ACCOUNT]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- =============================================
-- Author:
CREATE TABLE [dbo].[time_dimension] (
[time_id] [int] IDENTITY (1, 1) NOT NULL ,
[the_date] [datet...
In the previous two articles of this series, we’ve discussed how to create a report and how to use built-in date and time functions. Today, we’ll join
these two together and analyze some in...
1 :普通SQL语句可以用exec执行
Select * from tableName exec('select * from tableName')
exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N
2:字段名，表名，数据库名之类作为变量时，必须用动态
SQL declare @fname v...
--创建测试表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))
DROP TABLE [dbo].[TestRows2Columns]
CREATE TABLE [dbo].[TestRows2Columns](
[Id] [int] IDENTITY(1,1) NOT NULL,
