February 25, 2013 at 5:28 am
[
...
Im guessing Im going to have to go for adding them into a permanent table rather than a temp if IDENTITY go this way?
That is exactly what I've been talking about. To make it a bit nicer, you can create this tables not in default "dbo" (or other) schema, but in the dedicated scheme.
Juts create a separate schema, call it, let say "work"
CREATE SCHEMA work
GO
Then you can create your "work" tables there (use the code sample for creating temp tables). Don't forget that your dynamic code will need to check if the table already exist and drop it if so:
...
SELECT @sql =
'IF OBJECT_ID(''work.Example_Table_' + Code + ''') IS NOT NULL DROP TABLE work.Example_Table_' + Code + ';
SELECT * INTO work.Example_Table_' + Code + ' from main_table Where Code Like ''' + Code + ''''
FROM
...
After, the execution of such dynamic sql work.Example_Table_ will be created and accessible.
February 25, 2013 at 5:30 am
Hiya again,
Right.... Im getting there but Id made the initial ask simpler than it should be.
Within my SQL there is a source table and Destination table. Both hold slightly different codes to link to so Im imagining something like this...
But again this is completely wrong because now there are two columns I'm not sure how to set this up
create table #SSC(ID INT IDENTITY (1, 1), Source_Code CHAR(1),Dest_Code varchar(20))
insert into #SSC
select 'A' AS Source_Code, 'Total_A' AS Dest_Code union ALL
select 'B', 'TOTAL_B' union all
select 'P', 'TOTAL_P' union all
select 'V', 'TOTAL_V' union all
select 'X', 'TOTAL_X' union all
select 'Y', 'TOTAL_Y' union all
select 'Z', 'TOTAL_Z' union all
Select '!', 'TOTAL_Exclamation_Mark' union all
Select '.', 'TOTAL_Full_Stop' union all
Select '(', 'TOTAL_Open_Bracket'
DECLARE @sql VARCHAR(MAX)
DECLARE @looper int = 1
WHILE @looper <= (SELECT MAX(ID) from #SSC)
BEGIN
SET @sql = ''
SELECT @sql = 'SELECT * INTO #Example_Table_' + Dest_Code + ' from main_table Where Source_Code Like ''' + Dest_Code + ''''
FROM #SSC WHERE ID = @looper
--Change to Exec when you want to execute rather than print
PRINT(@SQL)
SET @looper = @looper + 1
END
Im getting this error when i run it
An explicit value for the identity column in table '#SSC' can only be specified when a column list is used and IDENTITY_INSERT is ON.
February 25, 2013 at 5:33 am
I do like the idea of creating a schema for all the tables if I have to create them all as permanent tables.
February 25, 2013 at 5:34 am
Abu Dina (2/25/2013)
Okay fair enough then just make the tempt table into a global temp table and problem is sorted.
No, this will not be exactly enough to make it robust...
If you simply change temp table into global temp table, your code (for the same table ) will only be able to run once in a single session. Also, it will have trouble to be executed by more than one session simultaneously. You will need to make sure that global temp table does not already exist before creating it - exactly the same way as you would need if using permanent table.
February 25, 2013 at 5:45 am
There are few things here.
1. Your code example has a small visible bug:
You cannot insert 'TOTAL_Exclamation_Mark' value into your #SSC table as it's longer than maximum allowed by your column definition of Dest_Code varchar(20)
2. Executing of the code posted does not result with
"An explicit value for the identity column in table '#SSC' can only be specified when a column list is used and IDENTITY_INSERT is ON." error as there is nothing in your posted code inserts into identity column. I guess it's happen due to another code part or variation which you did not include in your post.
3. For some reason, I have a feeling that your design is not quite appropriate to the task you are trying to do. But, we will need to have more information and details on what you are really trying to achieve in order to advise you further.
February 25, 2013 at 5:48 am
I guess I just don't understand why you need separate tables for each code to begin with.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2013 at 5:49 am
Getting there!!!
create table #SSC(ID INT IDENTITY (1, 1), Source_Code CHAR(1),Dest_Code varchar(30))
insert into #SSC
select 'A' AS Source_Code, 'Total_A' AS Dest_Code union ALL
select 'B', 'TOTAL_B' union all
select 'P', 'TOTAL_P' union all
select 'V', 'TOTAL_V' union all
select 'X', 'TOTAL_X' union all
select 'Y', 'TOTAL_Y' union all
select 'Z', 'TOTAL_Z' union all
Select '!', 'TOTAL_Exclamation_Mark' union all
Select '.', 'TOTAL_Full_Stop' union all
Select '(', 'TOTAL_Open_Bracket'
DECLARE @sql VARCHAR(MAX)
DECLARE @looper int = 1
WHILE @looper <= (SELECT MAX(ID) from #SSC)
BEGIN
SET @sql = ''
SELECT @sql = 'SELECT * INTO #Example_Table_' + Dest_Code + ' from main_table Where Source_Code Like ''' + Dest_Code + ''''
+'And source_code like ''' + Source_Code + ''''
FROM #SSC WHERE ID = @looper
--Change to Exec when you want to execute rather than print
PRINT(@SQL)
SET @looper = @looper + 1
END
February 25, 2013 at 5:52 am
I do for things happening later in the process.
It would take ages to explain that bit and I don't need to because Im Ok with that part of it.
Also I just want to know how to do this for lots of other things
February 25, 2013 at 6:01 am
Jeff Moden (2/25/2013)
I guess I just don't understand why you need separate tables for each code to begin with.
That is exactly the thing which makes me suspicious that the OP design is a bit "strange" ...
:hehe:
February 25, 2013 at 6:01 am
Well its now printing the correct details for my SQL.
If I can get it to excecute I will be very happy indeed
create table #SSC(ID INT IDENTITY (1, 1), Source_Code CHAR(1),Dest_Code varchar(30))
insert into #SSC
select 'A' AS Source_Code, 'Total_A' AS Dest_Code union ALL
select 'B', 'TOTAL_B' union all
select 'P', 'TOTAL_P' union all
select 'V', 'TOTAL_V' union all
select 'X', 'TOTAL_X' union all
select 'Y', 'TOTAL_Y' union all
select 'Z', 'TOTAL_Z' union all
Select '!', 'TOTAL_Exclamation_Mark' union all
Select '.', 'TOTAL_Full_Stop' union all
Select '(', 'TOTAL_Open_Bracket'
DECLARE @sql VARCHAR(MAX)
DECLARE @looper int = 1
WHILE @looper <= (SELECT MAX(ID) from #SSC)
BEGIN
SET @sql = ''
SELECT @sql = 'SELECT fct.AC_YEAR, fct.AC_TERM, fct.MAX_Attend_Date, fct.SysTalk_ID,
fct.Sessional_Attendance_tblschool, fct.'+Dest_Code+' ,ISNULL(src.TOTAL,0)AS Total,
CASE WHEN fct.'+Dest_Code+' = ISNULL(src.TOTAL,0) THEN ''No change''
WHEN fct.'+Dest_Code+' < ISNULL(src.TOTAL,0) THEN ''dest less than source''
WHEN fct.'+Dest_Code+' > ISNULL(src.TOTAL,0) THEN ''Source less than dest''
ELSE ''NA'' END AS DESCR
INTO #'+Dest_Code+'_Differences
FROM
(SELECT fct.AC_YEAR, fct.AC_TERM, fct.MAX_Attend_Date, fct.SysTalk_ID,
fct.Sessional_Attendance_tblschool, fct.'+Dest_Code+'
FROM fact.Sessional_Attendance_By_Pupil fct)fct
LEFT OUTER JOIN
(SELECT src.AC_YEAR, src.AC_TERM, src.TBLPUPIL,src.tblSchool,SUM(src.TOTAL)AS TOTAL
FROM
(SELECT ''AM'' AS Sess, dt.AC_YEAR, dt.AC_TERM, a.TBLPUPIL, a.tblSchool, COUNT(*)AS TOTAL
FROM CAYA_Sessional.dbo.TBLPUPIL_ATTEND_SESSIONS_tables a
LEFT OUTER JOIN (SELECT date, AC_YEAR, AC_TERM FROM dim.Date_Time) dt ON a.AttendDate = dt.Date
WHERE a.CODE_AM = '+Source_Code+'
GROUP BY dt.AC_YEAR, dt.AC_TERM, a.TBLPUPIL, a.tblSchool
UNION
SELECT ''PM'' AS Sess, dt.AC_YEAR, dt.AC_TERM, a.TBLPUPIL, a.tblSchool, COUNT(*)AS TOTAL
FROM CAYA_Sessional.dbo.TBLPUPIL_ATTEND_SESSIONS_tables a
LEFT OUTER JOIN (SELECT date, AC_YEAR, AC_TERM FROM dim.Date_Time) dt ON a.AttendDate = dt.Date
WHERE a.CODE_PM = '+Source_Code+'
GROUP BY dt.AC_YEAR, dt.AC_TERM, a.TBLPUPIL, a.tblSchool)src
GROUP BY src.AC_YEAR, src.AC_TERM, src.TBLPUPIL,src.tblSchool)src
ON fct.AC_Year = src.AC_YEAR AND fct.AC_TERM = src.AC_TERM AND fct.SYSTALK_ID = src.tblPupil
AND fct.Sessional_Attendance_tblschool = src.tblSchool
WHERE CASE WHEN fct.'+Dest_Code+' = ISNULL(src.TOTAL,0) THEN ''No change''
WHEN fct.'+Dest_Code+' < ISNULL(src.TOTAL,0) THEN ''dest less than source''
WHEN fct.'+Dest_Code+' > ISNULL(src.TOTAL,0) THEN ''Source less than dest''
ELSE ''NA'' END <> ''No change'' '
FROM #SSC WHERE ID = @looper
--Change to Exec when you want to execute rather than print
PRINT(@SQL)
SET @looper = @looper + 1
END
February 25, 2013 at 6:03 am
Eugene Elutin (2/25/2013)
Jeff Moden (2/25/2013)
I guess I just don't understand why you need separate tables for each code to begin with.That is exactly the thing which makes me suspicious that the OP design is a bit "strange" ...
:hehe:
Sorry?
February 25, 2013 at 6:28 am
Debbie Edwards (2/25/2013)
Eugene Elutin (2/25/2013)
Jeff Moden (2/25/2013)
I guess I just don't understand why you need separate tables for each code to begin with.That is exactly the thing which makes me suspicious that the OP design is a bit "strange" ...
:hehe:
Sorry?
Creating separate tables (which will look exactly the same in terms of column structure) for each code does sound very strange in terms of relational database design.
From information you have posted so far, it's hard to see any reasons for doing so.
If you generate unknown X number of tables during the execution, than your code will be extremely complicated, very unreliable and quite limited in its use (as discussed earlier, you cannot use temp tables for what you're are trying to do, using global temp tables or permanent tables will place a limitation on your code - it should not be executed simultaneously by more than one single session) .
Why not just use one single table? Add a "code" column into it to differentiate between its data sources...
February 25, 2013 at 6:32 am
Eugene Elutin (2/25/2013)
Debbie Edwards (2/25/2013)
Eugene Elutin (2/25/2013)
Jeff Moden (2/25/2013)
I guess I just don't understand why you need separate tables for each code to begin with.That is exactly the thing which makes me suspicious that the OP design is a bit "strange" ...
:hehe:
Sorry?
Creating separate tables (which will look exactly the same in terms of column structure) for each code does sound very strange in terms of relational database design.
From information you have posted so far, it's hard to see any reasons for doing so.
If you generate unknown X number of tables during the execution, than your code will be extremely complicated, very unreliable and quite limited in its use (as discussed earlier, you cannot use temp tables for what you're are trying to do, using global temp tables or permanent tables will place a limitation on your code - it should not be executed simultaneously by more than one single session) .
Why not just use one single table? Add a "code" column into it to differentiate between its data sources...
I don't feel its its important to waste peoples time on everything else that is being done and the history behind it. And there is a lot of history behind this one.
This was simply a query regarding using a loop to create different tables. I wanted to add it to my library of things I can do.
February 25, 2013 at 5:00 pm
Debbie Edwards (2/25/2013)
I do for things happening later in the process.It would take ages to explain that bit and I don't need to because Im Ok with that part of it.
Also I just want to know how to do this for lots of other things
Understood. I know you don't need to explain it. I was just curious and thought you might have the time to explain.
The only time I ever did such things was to build whole databases based on dates and that's only because the folks I was working for wanted it that way. They figured 4 million rows per day was enough for 1 database. I disagreed but it wasn't my call.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2013 at 5:07 pm
Debbie Edwards (2/25/2013)
Eugene Elutin (2/25/2013)
Debbie Edwards (2/25/2013)
Eugene Elutin (2/25/2013)
Jeff Moden (2/25/2013)
I guess I just don't understand why you need separate tables for each code to begin with.That is exactly the thing which makes me suspicious that the OP design is a bit "strange" ...
:hehe:
Sorry?
Creating separate tables (which will look exactly the same in terms of column structure) for each code does sound very strange in terms of relational database design.
From information you have posted so far, it's hard to see any reasons for doing so.
If you generate unknown X number of tables during the execution, than your code will be extremely complicated, very unreliable and quite limited in its use (as discussed earlier, you cannot use temp tables for what you're are trying to do, using global temp tables or permanent tables will place a limitation on your code - it should not be executed simultaneously by more than one single session) .
Why not just use one single table? Add a "code" column into it to differentiate between its data sources...
I don't feel its its important to waste peoples time on everything else that is being done and the history behind it. And there is a lot of history behind this one.
This was simply a query regarding using a loop to create different tables. I wanted to add it to my library of things I can do.
Gosh. It actually is important. It's called "sharing information". 1) You might actually get a better solution if you explained the purpose. Even if you're not interested in a possible better solution, then consider that 2) a whole lot of people have never done such a thing never mind knowing a good reason why to "mess data up" like this. I put that in quotes because some folks like doing a poor man's version of partitioning instead of losing some of the capabilities that partitioning causes.
Remember this is a 2 way street. People are interested in these types of problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply