Need help on dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help on dynamic SQL

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-17 : 01:51:52

Let's i've table and data as follow,


RID                    |Posi      |BusN                  | DDate
-----------------------------------------------------------------------
_R100400000001  1       WKM1925 2010-06-16 00:00:00
_R100400000001  1       WKM1925 2010-06-17 00:00:00
_R100400000001  1       WKM1925 2010-06-18 00:00:00
_R100400000001  1       WKM1925 2010-06-19 00:00:00
_R100400000001  1       WKM1925 2010-06-20 00:00:00
_R100400000001  1       WKM1925 2010-06-21 00:00:00
_R100400000001  1       WKM1925 2010-06-23 00:00:00
_R100400000001  1       WKM1925 2010-06-24 00:00:00
_R100400000001  1       WKM1925 2010-06-25 00:00:00
_R100400000001  1       WKM1925 2010-06-26 00:00:00
_R100400000001  1       WKM1925 2010-06-27 00:00:00
_R100400000001  1       WKM1925 2010-06-28 00:00:00
_R100400000001  1       WKM1925 2010-06-30 00:00:00
_R100400000001  1       WKM1925 2010-07-01 00:00:00
_R100400000001  1       WKM1925 2010-07-02 00:00:00
_R100400000001  1       WKM1925 2010-07-03 00:00:00
_R100400000001  1       WKM1925 2010-07-04 00:00:00
_R100400000001  1       WKM1925 2010-07-05 00:00:00
_R100600000001  1       WKM1925 2010-06-15 00:00:00
_R100600000001  1       WKM1925 2010-06-16 00:00:00
_R100600000001  1       WKM1925 2010-06-17 00:00:00
_R100600000001  1       WKM1925 2010-06-18 00:00:00
_R100600000001  1       WKM1925 2010-06-19 00:00:00
....
....


create table t6
(idx int identity(1,1), RID varchar(30), Posi tinyint, BusN varchar(10), DDate datetime);

create table t7
(idx int identity(1,1), RID varchar(30), Posi tinyint, BusN varchar(10), DDate datetime);

I know how to insert into t6 and t7 as follow,


DECLARE @sql NVARCHAR(5000)
DECLARE @m INT

SET @m = 1

WHILE @m <= 12
BEGIN
     SET @sql = 'Insert into t' + cast(@m as varchar) + 
                     ' select ... from ... ' + 
                     ' where MONTH(datecolumn) = ' + cast(@m as varchar)
     EXEC sp_executesql @sql
     SET @m = @m + 1     
END

But if my data as follow,


RID                    |Posi      |BusN                  | DDate
-----------------------------------------------------------------------
_R100400000001  1       WKM1925 2010-06-16 00:00:00
_R100400000001  1       WKM1925 2011-06-17 00:00:00
_R100400000001  1       WKM1925 2010-07-18 00:00:00
_R100400000001  1       WKM1925 2010-06-19 00:00:00
_R100400000001  1       WKM1925 2011-07-20 00:00:00
....
....
....

How to insert into t62010, t62011, t72010, and t72011, and so on?

I'm stuck to built dynamic T-SQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-17 : 02:57:15

[code]
SET @sql = 'Insert into t' + cast(@m as varchar) + convert(varchar(10), @theyear)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-17 : 03:35:38

where can i get the @theyear value?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-17 : 03:39:52

quote:
Originally posted by Delinda

where can i get the @theyear value?

i don't know. What year do you want to concatenate to form the table name ?

KH
[spoiler]Time is always against us[/spoiler]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-17 : 03:44:17

i've no problem to insert into t6,t7,t8, and so on

now my table as follow
t62010, t72010, t82010, ....., t12011, t22011, and so on

i need to insert into t62010, t62011, t72010, and t72011, and so on?

how dynamic T-SQL looks like?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-17 : 04:04:52

i assume the 2010 behind the t6, t7 is a year ?

KH
[spoiler]Time is always against us[/spoiler]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-17 : 04:07:50

yes sir

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-17 : 04:13:48

so where is the year value coming from ?

just assign it to the @theyear variable or replace it directly in the query

KH
[spoiler]Time is always against us[/spoiler]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-17 : 04:18:24

ok sir

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-17 : 05:03:14

[code]

DECLARE @sql NVARCHAR(4000)
DECLARE @m INT

SET @m = 1

WHILE @m <= 12
BEGIN
SET @sql = 'Insert into t' + cast(@m as varchar(2)) +
' select ... from ... ' +
' where MONTH(datecolumn) = ' + cast(@m as varchar)
if @m>=6
SET @sql = '
Insert into t' + cast(@m as varchar(2)) +datename(year,getdate()) +
' select ... from ... ' +
' where MONTH(datecolumn) = ' + cast(@m as varchar)
+'
Insert into t' + cast(@m as varchar(2)) +datename(year,dateadd(year,1,getdate())) +
' select ... from ... ' +
' where MONTH(datecolumn) = ' + cast(@m as varchar)
print @sql
--EXEC sp_executesql @sql
SET @m = @m + 1
END
[/code]

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-17 : 05:27:05

Why are you even considering such design?

N 56°04'39.26"
E 12°55'05.63"

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-17 : 07:09:26

i know, my table design is stupid. sorry for that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources