34,590
社区成员
发帖
与我相关
我的任务
分享
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- 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
当然也可以按照插入条件将数据插入到指定的行里!
*/
declare @value varchar(10),@sql varchar(200)
set @value='01'
set @sql='insert into Temp(day'+@value+') values('''+@value+''')'
--print @sql
--insert into Temp(day01) values('01')
exec @sql
--------------------------------------------------------------------------
-- 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)
--------------------------------------------------------------------------
--> 生成测试数据表:temp
IF 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] 31
SELECT * 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同理,改下代码。