Table creation - 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

General SQL Server Forums

New to SQL Server Programming

Table creation

Author

Topic

Julie19
Starting Member

32 Posts

Posted - 2012-06-26 : 16:34:02

I have created a script and how I can I create table for that script to store data in that table in SQL server 2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 16:36:57

Show us the script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Julie19
Starting Member

32 Posts

Posted - 2012-06-26 : 16:38:20

declare @dt date, @dt1 date, @dt2 date, @dt3 date, @dt4 date, @dt5
date, @dt6 date,@dt7 date,@dt8 date,@dt9 date,@dt10 date,@dt11 date,@dt12 date,@dt13 date,@dt14 date

declare @sql varchar(MAX)
set @dt = getdate()
set @dt1 = dateadd(day, -1, getdate())
set @dt2 = dateadd(day, -2, getdate())
set @dt3 = dateadd(day, -3, getdate())
set @dt4 = dateadd(day, -4, getdate())
set @dt5 = dateadd(day, -5, getdate())
set @dt6 = dateadd(day, -6, getdate())
set @dt7 = dateadd(day, -7, getdate())
set @dt8 = dateadd(day, -8, getdate())
set @dt9 = dateadd(day, -9, getdate())
set @dt10 = dateadd(day, -10, getdate())
set @dt11 = dateadd(day, -11, getdate())
set @dt12 = dateadd(day, - 12, getdate())
set @dt13 = dateadd(day, - 13, getdate())
set @dt14 = dateadd(day, - 14, getdate())

set @SQL = 'SELECT a.RptFunction, a.Center, a.AMATTUID,a.AM,a.TMATTUID,a.TM,a.[ATTUID], a.Agent
,SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt12,120) + '''' + ' THEN a.daily1day ELSE 0 END) AS [ ' + convert(varchar(25), @dt14, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt12, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END) AS [ ' + convert(varchar(25), @dt14, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt11, 120) + '''' + ' THEN a.daily1day ELSE 0 END) AS [ ' + convert(varchar(25), @dt13, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt11, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END) AS [ ' + convert(varchar(25), @dt13, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt10, 120) + '''' + ' THEN a.daily1day ELSE 0 END) AS [ ' + convert(varchar(25), @dt12, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt10, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END) AS [ ' + convert(varchar(25), @dt12, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt9, 120) + '''' + ' THEN a.daily1day ELSE 0 END) AS [ ' + convert(varchar(25), @dt11, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt9, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END)AS [ ' + convert(varchar(25), @dt11, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt8, 120) + '''' + ' THEN a.daily1day ELSE 0 END) AS [ ' + convert(varchar(25), @dt10, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt8, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END) AS [ ' + convert(varchar(25), @dt10, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt7, 120) + '''' + ' THEN a.daily1day ELSE 0 END )AS [ ' + convert(varchar(25), @dt9, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt7, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END) AS [ ' + convert(varchar(25), @dt9, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt6, 120) + '''' + ' THEN a.daily1day ELSE 0 END) AS [ ' + convert(varchar(25), @dt8, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt6, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END) AS [ ' + convert(varchar(25), @dt8, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt5, 120) + '''' + ' THEN a.daily1day ELSE 0 END) AS [ ' + convert(varchar(25), @dt7, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt5, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END) AS [ ' + convert(varchar(25), @dt7, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt4, 120) + '''' + ' THEN a.daily1day ELSE 0 END) AS [ ' + convert(varchar(25), @dt6, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt4, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END) AS [ ' + convert(varchar(25), @dt6, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt3, 120) + '''' + ' THEN a.daily1day ELSE 0 END) AS [ ' + convert(varchar(25), @dt5, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt3, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END) AS [ ' + convert(varchar(25), @dt5, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt2, 120) + '''' + ' THEN a.daily1day ELSE 0 END) AS [ ' + convert(varchar(25), @dt4, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt2, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END) AS [ ' + convert(varchar(25), @dt4, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt1, 120) + '''' + ' THEN a.daily1day ELSE 0 END )AS [ ' + convert(varchar(25), @dt3, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt1, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END) AS [ ' + convert(varchar(25), @dt3, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt, 120) + '''' + ' THEN a.daily1day ELSE 0 END) AS [ ' + convert(varchar(25), @dt2, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 END )AS [ ' + convert(varchar(25), @dt2, 120) + ']'

+ ' FROM [dbo].[q_Agent] a where int_ext = ' + '''' + 'i' + ''''
+ ' GROUP BY a.RptFunction, a.Center, a.AMATTUID,a.AM,a.TMATTUID,a.TM,a.[ATTUID], a.Agent'
print @sql
exec (@sql)

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 16:43:26

Add this WHERE 1=0 and put this right before the FROM: INTO NewTable. That'll create the base table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Julie19
Starting Member

32 Posts

Posted - 2012-06-26 : 16:48:25

Is this waht you meant?
'SELECT WHERE 1=0 Into:NewTable a.RptFunction, a.Center, a.AMATTUID,a.AM,a.TMATTUID,a.TM,a.[ATTUID], a.Agent

I am getting syntax error near where clause

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 17:04:16

No, the where clause still has to go in the normal place. Like this:

select ...
into NewTable
from ...
where 1=0
group by ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-26 : 17:53:57

You won't be able to store this in a table the way it's written because of duplicate column names.

Is this for a report? If so you are better off using the report's pivoting function. Add CAST(ReportDate AS date) to the GROUP BY clause and pivot from that. You can then remove the CASE expressions and the CONVERT() functions and just do SUMs on the 2 columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources