I am trying to insert data from one table (or Storeproc) to temp table. Temp table column count doesn't match from source table column count. I want insert source data into temp table from beginning columns and remaining columns will empty strings columns.
That means if Temp Table has 10 columns and source table has only 3 columns then insert first 3 columns into temp table and remaining 7 columns will be empty or null.
Example
CREATE TABLE #VarTemp(Col1 nvarchar(256),Col2 nvarchar(256),Col3 nvarchar(256),
Col4 nvarchar(256),Col5 nvarchar(256),Col6 nvarchar(256),
Col7 nvarchar(256),Col8 nvarchar(256),Col9 nvarchar(256),
Col10 nvarchar(256))
INSERT into #VarTemp EXEC sp_FindStringInTable 'Nareshbhai%', 'dbo', 'UserstoClean';
SELECT * FROM #VarTemp
If I execute separate EXEC sp_FindStringInTable 'Nareshbhai%', 'dbo', 'UserstoClean';
I am getting
Constitid FirstName LastName email status pincode
3151502 Nareshbhai Desai desanar@iit.edu Inactive desai
Error is
There was an error. Check to make sure object exists.
(0 row(s) affected)
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.
The results, if any, should be discarded.
sp_FindStringInTable
is
ALTER PROCEDURE [dbo].[sp_FindStringInTable] @stringToFind VARCHAR(100), @schema sysname, @table sysname
AS
DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(8000)
BEGIN TRY
SET @sqlCommand = 'SELECT * FROM ' + @schema + '.' + @table + ' WHERE'
SET @where = ''
SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''' + @schema + '''
AND TABLE_NAME = ''' + @table + '''
AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'
EXEC (@cursor)
OPEN col_cursor
FETCH NEXT FROM col_cursor INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @where <> ''
SET @where = @where + ' OR'
SET @where = @where + ' ' + @columnName + ' LIKE ''' + @stringToFind + ''''
FETCH NEXT FROM col_cursor INTO @columnName
END
CLOSE col_cursor
DEALLOCATE col_cursor
SET @sqlCommand = @sqlCommand + @where
--PRINT @sqlCommand
EXEC (@sqlCommand)
END TRY
BEGIN CATCH
PRINT 'There was an error. Check to make sure object exists.'
IF CURSOR_STATUS('variable', 'col_cursor') <> -3
BEGIN
CLOSE col_cursor
DEALLOCATE col_cursor
END
END CATCH
GO
insert into tab from other tab if column not match
Posted - 01/16/2013 : 23:57:30
