SQL Server stored procedures for replicating table data to another table

Source: Internet
Author: User

if    exists(Select *  fromsysobjectswhereId=object_id('Generatedata') and  ObjectProperty(ID,'isprocedure')=1 )    Drop ProcedureGeneratedataGoCREATE PROCEDUREGeneratedata@tablenamesysname as begin    Declare @column varchar( -)     Declare @columndata varchar( -)     Declare @sql varchar(8000)     Declare @xtype tinyint     Declare @namesysnameDeclare @objectId int     Declare @objectnamesysnameDeclare @ident int     SetNocount on     Set @objectId=object_id(@tablename)     if @objectId  is NULL --determine if an object exists    begin         Print 'The object is not exists'         return     End     Set @objectname=RTrim(object_name(@objectId))     if @objectname  is NULL or charindex(@objectname,@tablename)=0 --This judgment is not strict    begin         Print 'object not in current database'         return     End     if ObjectProperty(@objectId,'istable')< > 1 --determine if the object is a table    begin         Print 'The object is not table'         return     End     Select @ident=Status&0x80  fromsyscolumnswhereId=@objectid  andStatus&0x80=0x80     if @ident  is  not NULL     Print 'SET Identity_insert'+@TableName+' on'     DeclareSyscolumns_cursorcursor     for SelectC.name,c.xtype fromsyscolumns CwhereC.id=@objectid Order  byC.colidOpenSyscolumns_cursorSet @column="'     Set @columndata="'     Fetch Next  fromSyscolumns_cursor into @name,@xtype      while @ @fetch_status < >-1     begin         if @ @fetch_status < >-2         begin             if @xtype  not inch(189, the, *, About,98)--timestamp do not need processing, image,text,ntext,sql_variant temporarily do not handle            begin                 Set @column=@column+ Case  when Len(@column)=0  Then"' Else ','End+@name                 Set @columndata=@columndata+ Case  when Len(@columndata)=0  Then "' Else ',"',"','End                 + Case  when @xtype inch(167,175) Then  "" "+'+@name+'+ "" " --Varchar,char                 when @xtype inch(231,239) Then " "N" " "+'+@name+'+ "" " --Nvarchar,nchar                 when @xtype= A  Then  "" "+convert (char),'+@name+', 121) + "" " --datetime                 when @xtype= -  Then  "" "+convert (char),'+@name+', +) + "" " --smalldatetime                 when @xtype= $  Then  "" "+convert (char),'+@name+')+ "" " --uniqueidentifier                Else @name End             End         End         Fetch Next  fromSyscolumns_cursor into @name,@xtype     End     CloseSyscolumns_cursordeallocateSyscolumns_cursorSet @sql='SET NOCOUNT ON select"'Insert into'+@tablename+'('+@column+') VALUES ("' as"'--"','+@columndata+',"')"' from'+@tablename     Print '--'+@sql     exec(@sql)     if @ident  is  not NULL     Print 'SET Identity_insert'+@TableName+'OFF' EndGO

EXEC generatedata ' table name under the same database '

SQL Server stored procedures for replicating table data to another table

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.