表Temp 字段StaffId,StaffName,day1,day2,day3.....day31
当为值为01 则将01插入Temp Day1字段
当为值为02 则将02 插入Temp Day02字段
当为值为03 则将03 插入Temp Day03字段
当为值为04 则将04 插入Temp Day04字段
当为值为05 则将05 插入Temp Day05字段
............................................当为值为31 则将31插入Temp Day31字段
当为值为01 则将01插入Temp Day1字段
当为值为02 则将02 插入Temp Day02字段
当为值为03 则将03 插入Temp Day03字段
当为值为04 则将04 插入Temp Day04字段
当为值为05 则将05 插入Temp Day05字段
............................................当为值为31 则将31插入Temp Day31字段
-- Author : htl258(Tony)
-- Date : 2010-04-19 19:16:57
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--------------------------------------------------------------------------
--> 生成测试数据表:tempIF NOT OBJECT_ID('[temp]') IS NULL
DROP TABLE [temp]
GO
CREATE TABLE [temp]([StaffId] NVARCHAR(10),[StaffName] NVARCHAR(10),[day1] INT,[day2] INT,[day3] INT,[day31] INT)
GO
--SELECT * FROM [temp]-->SQL查询如下:
IF NOT OBJECT_ID('[sp_tony]') IS NULL
DROP PROC [sp_tony]
GO
CREATE PROC sp_tony
@value INT
AS
DECLARE @s VARCHAR(1000)
SET @s='
INSERT temp([day'+ltrim(@value)+'])
Values('+LTRIM(@value)+')'
EXEC(@s)
GO
EXEC [sp_tony] 1
EXEC [sp_tony] 2
EXEC [sp_tony] 3
EXEC [sp_tony] 31SELECT * FROM temp
/*
StaffId StaffName day1 day2 day3 day31
NULL NULL 1 NULL NULL NULL
NULL NULL NULL 2 NULL NULL
NULL NULL NULL NULL 3 NULL
NULL NULL NULL NULL NULL 31
*/
--这是插入的,UPDATE同理,改下代码。
set @value='01'
set @sql='insert into Temp(day'+@value+') values('''+@value+''')'
--print @sql
--insert into Temp(day01) values('01')
exec @sql
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.tb') is not null drop table tb
if object_id('test.dbo.proc_test') is not null drop proc proc_test
-- 创建数据表
create table tb
(
StaffId int identity(1,1),
day01 char(3),
day02 char(3),
day03 char(3),
day04 char(3),
day05 char(3),
day06 char(3),
day07 char(3)
)
go
--插入数据
insert into tb select '01',null,null,null,null,null,null
go
--代码实现
create proc proc_test
@input char(3)
as
begin
declare @col char(6),@str_sql char(100)
select @col=name from(select name,right(name,2)_id from syscolumns where id=object_id('tb'))t where _id=@input
set @str_sql='insert into tb('+rtrim(@col)+') values ('''+rtrim(@input)+''')'
print @str_sql
exec(@str_sql)
end
go
--测试
select * from tb
/*原数据
StaffId day01 day02 day03 day04 day05 day06 day07
-----------------------------------------------------------
1 01 NULL NULL NULL NULL NULL NULL
*/
exec proc_test '04'
exec proc_test '06'
select * from tb
/*插入后数据
StaffId day01 day02 day03 day04 day05 day06 day07
-----------------------------------------------------------
1 01 NULL NULL NULL NULL NULL NULL
2 NULL NULL NULL 04 NULL NULL NULL
3 NULL NULL NULL NULL NULL 06 NULL当然也可以按照插入条件将数据插入到指定的行里!
*/