tag:blogger.com,1999:blog-5299050221157624391.post6146711980831945387..comments2023-11-03T17:50:29.251+05:30Comments on Sudhir's SQL SERVER Blog: Script that generates data from an existing tableSudhir Chawlahttp://www.blogger.com/profile/14469801380794481741noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-5299050221157624391.post-53581186598290313392010-06-16T09:14:23.285+05:302010-06-16T09:14:23.285+05:30Begin
Set NOCOUNT ON
Declare @strSQLMain varchar(8...Begin<br />Set NOCOUNT ON<br />Declare @strSQLMain varchar(8000)<br />Declare @sSQLInsert varchar(5000)<br />Declare @sSQLFrom varchar(8000)<br />Declare @sComma char(1)<br />Declare @sOpenParenthesis char(1)<br />Declare @sCloseParenthesis char(1)<br />Declare @singleQuote varchar(10)<br />Declare @concat varchar(10)<br />Declare @p_tablename varchar(100)<br /><br />Set @sComma = ','<br />Set @sOpenParenthesis = '('<br />Set @sCloseParenthesis = ')'<br />Set @singleQuote = ''''<br />Set @concat = '''+'''<br />Set @sSQLFrom = ''<br /><br />Set @p_tablename = 'prf_invst_trans_subtyp_ref'<br /><br />-- drop if the temp table is not deleted from the previous RUN.<br />--If Exists ( Select 1 From Information_Schema.Tables where Table_Type = 'BASE TABLE' and Table_Name = 'tmpResultsuspGI')<br />--Begin<br />--Drop table tmpResultsuspGI<br />--End<br />Create table #tmpResultsuspGI<br />(DText varchar(8000))<br /><br /><br />-- Check , if table exists.<br />If Exists ( Select 1 From Information_Schema.Tables where Table_Type = 'BASE TABLE' and Table_Name = @p_tablename )<br />begin<br />-- Get the columns.<br />declare @name varchar(50),@xtype varchar(50)<br />declare curColumns cursor<br />for Select s.name,st.name<br />from sysColumns s<br />inner join sysTypes st On s.xtype = st.xusertype<br />where id = Object_ID(@p_tablename) and st.status=0<br /><br /><br />--based on their data type<br />select @sSQLInsert = 'INSERT INTO [dbo].[' + @p_tablename+']' +@sOpenParenthesis<br />open curColumns;<br />fetch next from curColumns into @name,@xtype<br />while @@fetch_status = 0<br />begin<br />/** Query Format<br />select cast(countryID as varchar(30) )+ ',''' + CountryCode + '''' + ',''' + countryname + ''''<br />from Country<br />**/<br />select @sSQLInsert = @sSQLInsert + @name + @sComma<br />if @xtype in ('char','varchar','datetime','smalldatetime','nvarchar','nchar','uniqueidentifier')<br />begin<br />select @sSQLFrom = @sSQLFrom + '''''''''' + '+ IsNull(cast(' + @name + ' as varchar(500)),''NULL'') +' + '''''''''' + '+' + ''',''' + '+'<br />end<br />else<br />begin<br />select @sSQLFrom = @sSQLFrom + 'cast(IsNull(cast(' + @name + ' as varchar(500)),''NULL'') as varchar(500)) ' + '+' + ''',''' + '+'<br />end<br />fetch next from curColumns into @name,@xtype<br />end<br />close curColumns;<br />deallocate curColumns;<br /><br />Select @sSQLInsert<br /><br />select @sSQLInsert = substring(@sSQLInsert,1,Len(@sSQLInsert) -1 )<br />select @sSQLInsert = @sSQLInsert + @sCloseParenthesis<br />select @sSQLFrom = substring(@sSQLFrom,1,Len(@sSQLFrom) -5 )<br />select @sSQLFrom = @sSQLFrom + ' as DText'<br />end<br /><br /><br />else<br />begin<br />Print 'Table does not exists.'<br />return<br />end<br /><br /><br />Set @strSQLMain = 'Insert into #tmpResultsuspGI Select ' + @sSQLFrom + ' From [' + @p_tablename + ']'<br />print @strSQLMain<br />exec (@strSQLMain)<br /><br /><br /><br />--Check if there is an identity column or not<br />If ObjectProperty(Object_ID(@p_tablename),'TableHasIdentity') = 1<br />Begin<br />Select 'Set IDENTITY_INSERT [' + @p_tablename + '] ON '<br />End<br /><br /><br />Select @sSQLInsert + ' VALUES' + @sOpenParenthesis + Replace(DText,'''NULL''','NULL') + @sCloseParenthesis As [--Statements]<br />From #tmpResultsuspGI<br />If ObjectProperty(Object_ID(@p_tablename),'TableHasIdentity') = 1<br />Begin<br />Select 'Set IDENTITY_INSERT ' + @p_tablename + ' OFF '<br />End<br /><br /><br />Drop table #tmpResultsuspGI<br />EndSudhir Chawlahttps://www.blogger.com/profile/14469801380794481741noreply@blogger.comtag:blogger.com,1999:blog-5299050221157624391.post-64855150240894436952009-09-17T05:12:08.883+05:302009-09-17T05:12:08.883+05:30Thanks for the correction.
This is linked to &qu...Thanks for the correction. <br /><br />This is linked to "Basic SQL Questions" post.Sudhir Chawlahttps://www.blogger.com/profile/14469801380794481741noreply@blogger.comtag:blogger.com,1999:blog-5299050221157624391.post-6119360415785699392009-09-16T23:18:46.189+05:302009-09-16T23:18:46.189+05:30"clustered index can be created only on table..."clustered index can be created only on table that has unique non null values"<br /><br />This statement is wrong..Clustered index can be created on non-unque null values as well.<br /><br />Try this code snippet.<br /><br /><br />------------------------------<br /><br />create table test<br />(<br /> id int null<br />)<br /><br />insert into test(id) values (null)<br />insert into test(id) values (1)<br />insert into test(id) values (null)<br />insert into test(id) values (2)<br />insert into test(id) values (2)<br />insert into test(id) values (1)<br />GO<br /><br />CREATE CLUSTERED INDEX TX_TEST ON TEST(ID)<br />GO<br />------------------------------Amulya Duttanoreply@blogger.com