{"id":2255,"date":"2012-08-14T08:29:59","date_gmt":"2012-08-14T08:29:59","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2255"},"modified":"2012-12-20T13:03:12","modified_gmt":"2012-12-20T13:03:12","slug":"%e4%ba%a7%e7%94%9finsert%e8%84%9a%e6%9c%ac%e8%bd%ac","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/08\/14\/%e4%ba%a7%e7%94%9finsert%e8%84%9a%e6%9c%ac%e8%bd%ac\/","title":{"rendered":"\u4ea7\u751finsert\u811a\u672c[\u8f6c]"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\ncreate procedure sp_generate_insert_script\r\n                 @tablename_mask varchar(30) = NULL\r\nas\r\nbegin\r\n  declare @tablename       varchar (128)\r\n  declare @tablename_max   varchar (128)\r\n  declare @tableid         int\r\n  declare @columncount     numeric (7,0)\r\n  declare @columncount_max numeric (7,0)\r\n  declare @columnname      varchar (30)\r\n  declare @columntype      int\r\n  declare @string          varchar (30)\r\n  declare @leftpart        varchar (max)   \r\n  declare @rightpart       varchar (max)   \r\n  declare @hasident        int\r\n\r\n  set nocount on\r\n\r\n  -- take ALL tables when no mask is given (!)\r\n  if (@tablename_mask is NULL)\r\n  begin\r\n    select @tablename_mask = &#39;%&#39;\r\n  end\r\n\r\n  -- create table columninfo now, because it will be used several times\r\n\r\n  create table #columninfo\r\n  (num      numeric (7,0) identity,\r\n   name     varchar(30),\r\n   usertype smallint)\r\n\r\n\r\n  select name,\r\n         id\r\n    into #tablenames\r\n    from sysobjects\r\n   where type in (&#39;U&#39; ,&#39;S&#39;)\r\n     and name like @tablename_mask\r\n\r\n  -- loop through the table #tablenames\r\n\r\n  select @tablename_max  = MAX (name),\r\n         @tablename      = MIN (name)\r\n    from #tablenames\r\n\r\n  while @tablename &lt;= @tablename_max\r\n  begin\r\n    select @tableid   = id\r\n      from #tablenames\r\n     where name = @tablename\r\n\r\n    if (@@rowcount &lt;&gt; 0)\r\n    begin\r\n      -- Find out whether the table contains an identity column\r\n      select @hasident = max( status &amp; 0x80 )\r\n        from syscolumns\r\n       where id = @tableid\r\n\r\n      truncate table #columninfo\r\n\r\n      insert into #columninfo (name,usertype)\r\n      select name, type\r\n        from syscolumns C\r\n       where id = @tableid\r\n         and type &lt;&gt; 37            -- do not include timestamps\r\n\r\n      -- Fill @leftpart with the first part of the desired insert-statement, with the fieldnames\r\n\r\n      select @leftpart = &#39;select &#39;&#39;insert into &#39;+@tablename\r\n      select @leftpart = @leftpart + &#39;(&#39;\r\n\r\n      select @columncount     = MIN (num),\r\n             @columncount_max = MAX (num)\r\n        from #columninfo\r\n      while @columncount &lt;= @columncount_max\r\n      begin\r\n        select @columnname = name,\r\n               @columntype = usertype\r\n          from #columninfo\r\n         where num = @columncount\r\n        if (@@rowcount &lt;&gt; 0)\r\n        begin\r\n          if (@columncount &lt; @columncount_max)\r\n          begin\r\n            select @leftpart = @leftpart + @columnname + &#39;,&#39;\r\n          end\r\n          else\r\n          begin\r\n            select @leftpart = @leftpart + @columnname + &#39;)&#39;\r\n          end\r\n        end\r\n\r\n        select @columncount = @columncount + 1\r\n      end\r\n\r\n      select @leftpart = @leftpart + &#39; values(&#39;&#39;&#39;\r\n\r\n      -- Now fill @rightpart with the statement to retrieve the values of the fields, correctly formatted\r\n\r\n      select @columncount     = MIN (num),\r\n             @columncount_max = MAX (num)\r\n        from #columninfo\r\n\r\n      select @rightpart = &#39;&#39;\r\n\r\n      while @columncount &lt;= @columncount_max\r\n      begin\r\n        select @columnname = name,\r\n               @columntype = usertype\r\n          from #columninfo\r\n         where num = @columncount\r\n\r\n        if (@@rowcount &lt;&gt; 0)\r\n        begin\r\n\r\n          if @columntype in (39,47)\r\n          begin\r\n            select @rightpart = @rightpart + &#39;+&#39;\r\n            select @rightpart = @rightpart + &#39;ISNULL(&#39; + replicate( char(39), 4 ) + &#39;+replace(&#39; + @columnname + &#39;,&#39; +\r\n\r\nreplicate( char(39), 4 ) + &#39;,&#39; + replicate( char(39), 6) + &#39;)+&#39; + replicate( char(39), 4 ) + &#39;,&#39;&#39;NULL&#39;&#39;)&#39;\r\n          end\r\n\r\n          else if @columntype = 35\r\n                                  \r\n          begin\r\n            select @rightpart = @rightpart + &#39;+&#39;\r\n            select @rightpart = @rightpart + &#39;ISNULL(&#39; + replicate( char(39), 4 ) + &#39;+replace(convert(varchar(1000),&#39; +\r\n\r\n@columnname + &#39;)&#39; + &#39;,&#39; + replicate( char(39), 4 ) + &#39;,&#39; + replicate( char(39), 6 ) + &#39;)+&#39; + replicate( char(39), 4 ) +\r\n\r\n&#39;,&#39;&#39;NULL&#39;&#39;)&#39;\r\n          end\r\n\r\n          else if @columntype in (58,61,111)\r\n          begin\r\n            select @rightpart = @rightpart + &#39;+&#39;\r\n            select @rightpart = @rightpart + &#39;ISNULL(&#39; + replicate( char(39), 4 ) + &#39;+convert(varchar(20),&#39; + @columnname +\r\n\r\n&#39;)+&#39;+ replicate( char(39), 4 ) + &#39;,&#39;&#39;NULL&#39;&#39;)&#39;\r\n          end\r\n\r\n          else  \r\n          begin\r\n            select @rightpart = @rightpart + &#39;+&#39;\r\n            select @rightpart = @rightpart + &#39;ISNULL(convert(varchar(99),&#39; + @columnname + &#39;),&#39;&#39;NULL&#39;&#39;)&#39;\r\n          end\r\n\r\n\r\n          if ( @columncount &lt; @columncount_max)\r\n          begin\r\n            select @rightpart = @rightpart + &#39;+&#39;&#39;,&#39;&#39;&#39;\r\n          end\r\n\r\n        end\r\n        select @columncount = @columncount + 1\r\n      end\r\n\r\n    end\r\n\r\n    select @rightpart = @rightpart + &#39;+&#39;&#39;)&#39;&#39;&#39; + &#39; from &#39; + @tablename\r\n\r\n    -- Order the select-statements by the first column so you have the same order for\r\n    -- different database (easy for comparisons between databases with different creation orders)\r\n    select @rightpart = @rightpart + &#39; order by 1&#39;\r\n\r\n    -- For tables which contain an identity column we turn identity_insert on\r\n    -- so we get exactly the same content\r\n\r\n    if @hasident &gt; 0\r\n       select &#39;SET IDENTITY_INSERT &#39; + @tablename + &#39; ON&#39;\r\n\r\n    exec ( @leftpart + @rightpart )\r\n\r\n    if @hasident &gt; 0\r\n       select &#39;SET IDENTITY_INSERT &#39; + @tablename + &#39; OFF&#39;\r\n\r\n    select @tablename      = MIN (name)\r\n      from #tablenames\r\n     where name            &gt; @tablename\r\n  end\r\n\r\nend\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>create procedure sp_generate_insert_script @tablename_m [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2255","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2255","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2255"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2255\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}