how to create update query based on select query

Posted on 2014-08-03
Last Modified: 2014-08-09
Hi, lets say I have one table called mytabletest and this table have a lots of collumns and rows but i want to select just the row that have collum  id=10 and base on the result create one update query to update the same row on the same table on another database. So I will copy this update query inside one file and execute this on the other database to update just this id=10. But I don't want to give the others collumns names since is too much, so is there any way to get the collumns names when i am selecting.
Question by:rafaelrgl
    LVL 14

    Expert Comment

    by:Vikas Garg

    If you don't want to Fire update statement using column name then you can Delete the row in the Destination table and insert from source table but I don't think that it is right way to do this thing.

    You can also try Cursor or looping as shown in the below query.
    Hope this would help you
    LVL 1

    Author Comment

    I want to use column name, but what I say is the select statment should get those name and build the update query
    LVL 31

    Expert Comment

    What version of sql server are you using?
    LVL 1

    Author Comment

    LVL 1

    Accepted Solution

    I did the query i want it.

      This script will generate script to insert/update from a source table in one database to an 
      identical destination table in another database or server.  It can be run for inserts or updates,
      and can be run for a single row in insert and update mode, or all rows in a table for insert mode.
    declare @tab varchar(50)
           ,@pk1Val varChar(10)
    	   ,@pk1Name varChar(50)
    	   ,@qt char(1)
    	   ,@StatementType varChar(10)
    set nocount on
     1) open script and connect to the source database
     2) Change the  variable values to change the output options for the script below (@tab, @statementtype etc)
     3) execute the script (best to use text output)
     4) copy the script output into a script window, and run on the destination table.
    @Tab = the name of the source table
    @pk1Val = if selecting a single row or doing an update statement, the value of the primary key for that row
    @pk1Name = if inserting a single row or doing an update statement, the name of the column for the primary key
    @StatementType = either 'INSERT' to create an insert statement or 'UPDATE' for an Update statement
    select @tab = 'mytable', @pk1Val = '', @pk1Name = '', @StatementType = 'INSERT'
    declare @tabName varchar(50)
          , @colName varchar(50)
          , @colType varchar(50) 
          , @collength varChar(50)
    	  , @colOrder int
    	  , @IsIdent char(1)
    create table #output (Line varChar(4000), LineOrder int)
    create table #ColumnValues (ColName varChar(250), ColOrder int, RowNumber int, ColValue varchar(4000), colType varchar(50))
    declare @out varchar(4000)
           ,@lineCounter int
    	   ,@ColValue varchar(4000)
    	   ,@sortCol varchar(50)
    /* get the ordering column */
    select @sortCol = sc.Name
    from sysobjects so
    inner join syscolumns sc
    inner join systypes st
    on sc.xtype = st.xusertype 
    where so.Name = @tab
     and ((sc.status = 0x80) OR (ColOrder = 1 and not sc.status = 0x80 ))
    /* put in the repeating values based on the columns*/
    declare objCurs CURSOR FOR 
    select,,, sc.length, Case when sc.status = 0x80 then 'Y' else 'N' END as IsIdent, ColOrder
    from sysobjects so
    inner join syscolumns sc
    inner join systypes st
    on sc.xtype = st.xusertype
    where so.Name = @tab
    DECLARE @counter int, @numCols int, @RowNumber int, @LastRowNumber int, @maxRowNumber int, @maxColOrder int
    select @numCols = count( 
    from sysobjects so
    inner join syscolumns sc
    where so.Name = @tab
    --select @numCols  --debug code
    open objCurs
    Fetch from objCurs
    into @tabname, @colName, @colType, @colLength, @isIdent, @colOrder
    while @@fetch_status = 0
    	SET @counter = 0
    	/* get the value from the table */
    	if @IsIdent = 'N'
    	--select @TabName,@ColName, @ColType, @ColLEngth, @isIdent, @ColOrder  --debug code
    		/* increase better type handling by inserting more case statments, handling different data types */
    		if datalength(@pk1Name) = 0 or datalength(@pk1Val) = 0
    			/* getting all rows in the table */
    			exec ('insert into #ColumnValues (ColName, ColOrder, ColValue, ColType) 
    					select  ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName + ' order by ' + @SortCol + ' ' +
    				  ' declare @counter int set @counter = 0 ' +
    				  ' update #ColumnValues Set @Counter = RowNumber = @Counter + (' + @numCols + ' * 10) where ColName = ''' + @colName + '''' )
    			/* filtering by a pk val */
    			exec ('insert into #ColumnValues (RowNumber, ColName, ColORder, ColValue, ColType)
    					select 0, ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName + 
    				  ' where ' + @pk1Name + ' = ' + @pk1Val) 
    	end /* if @isIdent = 'n' */
    	Fetch Next from objCurs
    	into @tabname, @colName, @colType, @colLength, @IsIdent, @ColOrder
    --select * from #ColumnValues --debug code
    select @MaxRowNumber = Max(rowNumber) from #columnValues --keep highest row number so we know when we are finished
    select @MaxColOrder = max(ColOrder) from #ColumnValues where RowNumber = @MaxRowNumber
    /* next cursor for outputting the results from the retval table into the output table */
    declare ColVal_Curs  cursor for
    select ColName , ColOrder , RowNumber , ColValue , colType 
    from #ColumnValues
    order by RowNumber, ColOrder
    open ColVal_Curs
    --set the last row number to the first in the table, so post loop checking works
    select @lastRowNumber = min(rowNumber) from #ColumnValues 
    set @lineCounter = @LastRowNumber --initialise at the first row
    fetch from ColVal_Curs into
    @colName, @ColOrder, @RowNumber, @colValue, @ColType
    while @@Fetch_status = 0
    BEGIN /* cursor loop */
    		/* get the quote type to enclose the value from the column type */
    		select @qt = case @colType
    					 when 'nvarchar' then ''''
    					 when 'nchar' then ''''
    					 when 'DateTime' then ''''
    					 when 'ntext' then ''''
    					 else ''  
    --select @linecounter, @colName, @ColOrder, @RowNumber, @colValue, @ColType
    		if not @ColValue is null 
    		if @rowNumber = @lineCounter
    			select @out = case @statementType
    							when  'UPDATE' THEN 'Update ' + @tab + ' SET '
    							when  'INSERT' then 'INSERT INTO ' + @tab + ' ('
    			if @StatementType = 'UPDATE' 
    				select @Out = @out + @colName + ' = ' + @qt + COALESCE(@ColValue, 'NULL') + @qt + ',' -- + @ColType 
    				insert into #output (Line, LineOrder)
    				values (@out, @lineCounter)
    			if @statementType = 'INSERT' 
    				/* put comma in */
    				if @lineCounter > @RowNumber --not first line in set of values for row
    					select @out = @out + ','
    				/*put in the name of the column */
    				insert into #output (Line, LineOrder)
    				values (@out + @colName
    					  , @lineCounter)
    				if @lineCounter > @RowNumber --not first line in set of values for row 
    					select @out = ','
    					select @out = ''
    				/* put in the value of the column */
    				insert into #output (Line, LineOrder)
    				values (@out + @qt + COALESCE(@ColValue, 'NULL') + @qt 
    					  , @lineCounter + 10 + @numCols)
    		end  /*not @ColValue is null */
    	select @lineCounter = @lineCounter + 1
    	set @out = ''
    /* get the next record*/
    	fetch from ColVal_Curs into
    	@colName, @ColOrder, @RowNumber, @colValue, @ColType
    --select @ColOrder, @MaxColOrder, @@Fetch_Status  --debug code
    	if (@rowNumber > @lastRowNumber) or (@RowNumber = @MaxRowNumber and @MaxColOrder = @ColOrder and @@FEtch_Status = -1)
    	/* this bit of processing is done whenever the set of columsn in a row changes to the next row of the original table*/
    	/* ie we are building a record to insert, and the PK changes because we are at the next record */
    		/* remove the last comma from the last line */
    		declare @lastLine int
    		if @statementType = 'UPDATE'
    			/*remove last comma*/
    			update #output
    			set Line = left(Line,datalength(Line)-1)
    			where lineOrder = @LineCounter
    			/* insert a 'where' clause */
    			insert into #output (line, LineOrder)
    			select ' WHERE ' + @pk1Name + ' = ' + @pk1Val, Max(LineOrder) + 1 from #output
    		if @statementType = 'INSERT'
    			/* put in a 'values' statement between the column names and the column values */
    			insert into #output (Line, LineOrder)
    			values (') VALUES (', @LastRowNumber + @numCols + 5)
    			/* close off the lot */
    			insert into #output (line, lineorder)
    			select ')', Max(LineOrder) + 1 from #output
    		set @lastRowNumber = @RowNumber
    		set @lineCounter = @RowNumber  /* reset linecounter for next set */
    	End /* if rownumber > last row number */
    end /* cursor loop */
    close objCurs
    deallocate objCurs
    close ColVal_Curs
    deallocate ColVal_Curs
    /* get the statements out from the list*/
    select line as [Copy script from output window below] from #output order by lineorder
    /*  bug tracking code - uncomment to diagnose problems
    select distinct RowNumber from #ColumnValues order by 1
    select * from #ColumnValues
    order by RowNumber, ColOrder, ColName, ColValue
    drop table #output
    drop table #ColumnValues
    set nocount off

    Open in new window

    LVL 47

    Expert Comment

    Attribution for that source is:
     1  /* 
      2    SQL Server Row Script Creator
      3    Bruce Chapman Aug 2006

    Open in new window

    LVL 75

    Expert Comment

    by:Anthony Perkins
    Attribution for that source is:
    Good one!  Why do people do that?  It never ceases to amaze me.  Especially as EE has strict copyright rules that we all agree to when we sign on to this site.
    LVL 1

    Author Closing Comment

    It took too long to help me, so I found the solution myself

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now