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
 insert into tab from other tab if column not match

Author  Topic 

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2013-01-16 : 23:57:30
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-17 : 00:03:57
for INSERT...EXEC to work the table should be exactly same in structure as SP resultset. if you need to selectively populate columns and fill rest using NULL or default value you need method 2 here

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2013-01-17 : 00:53:17

quote:
Originally posted by visakh16

for INSERT...EXEC to work the table should be exactly same in structure as SP resultset. if you need to selectively populate columns and fill rest using NULL or default value you need method 2 here

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Its not working 2nd method. Is there anyway I can insert default values while inserting from source table?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-17 : 01:12:57
why its not working? whats the error you're getting?
Is Adhoc Distributed queries option set in your database? you need to set it if you want to use OPENROWSET

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -