Dynamic Query

Hi,

we  often changes table column and datatype and insert a records. These records need to copy same table or another table .Every changes  a updating stored procedure in Insert statement. some time we forgot the changes in stored procedure. To avoid following problem using dynamic Query  to get all columns and create a string Insert statement.

ALTER PROCEDURE [dbo].[spGetCopyTable]
@TABLE VARCHAR(50),
@QUERY VARCHAR(MAX) OUTPUT
/*———————————————————
DECLARE @QUERY nVARCHAR(MAX)
EXEC [dbo].[spGetCopyTable] @TABLE=’LiaRiskLocDtls’, @QUERY= @QUERY
SELECT @Query
——————————————————*/
AS
BEGIN
–DECLARE @TABLE VARCHAR(50)=’LiabilityMain’
–DECLARE @QUERY VARCHAR(MAX)
DECLARE @COLUMN NVARCHAR(MAX)
DECLARE @SELECT VARCHAR(MAX)

DECLARE @QRY NVARCHAR(MAX)

SET @QRY=N’SELECT @COLUMN=STUFF((
SELECT ”,[”+C.NAME+”]” FROM SYS.columns C INNER JOIN SYS.tables T ON T.object_id=C.object_id
WHERE T.name=”’+@TABLE+”’ FOR XML PATH(””)),1,1,””)’

exec sp_executesql @QRY, N’@COLUMN VARCHAR(MAX) out’, @COLUMN out
SET @SELECT=’SELECT ‘+REPLACE(@COLUMN,’QuotationNo’,’@NQuotationNo’)
SET @SELECT=REPLACE(@SELECT,'[‘,’PM.[‘)
SET @SELECT=REPLACE(@SELECT,’PM.[ID],’,”)
SET @SELECT=@SELECT+’ FROM ‘+@TABLE+’ PM WHERE QuotationNo=@SQuotationNo ‘
SELECT @QUERY=’INSERT INTO DBO.’+@TABLE+’ (‘+@COLUMN+’)’ + @SELECT
SET @QUERY=REPLACE(@QUERY,'[ID],’,”)
–PRINT @QUERY
–RETURN @QUERY
END

Advertisements

Author: tdmsql

I am a PL-Sql Developer. I am searching some sites sql studies but its bored. i like create studies note my own way. so that i created this site.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s