SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 insert into tab from other tab if column not match
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 01/16/2013 :  23:57:30  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/17/2013 :  00:03:57  Show Profile  Reply with Quote
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 - 01/17/2013 :  00:53:17  Show Profile  Reply with Quote

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

India
52309 Posts

Posted - 01/17/2013 :  01:12:57  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000