{"id":367,"date":"2011-08-05T19:38:12","date_gmt":"2011-08-05T19:38:12","guid":{"rendered":"http:\/\/jonescarvalho.com\/?p=367"},"modified":"2020-05-13T20:57:07","modified_gmt":"2020-05-13T23:57:07","slug":"fazendo-select-para-insert-em-tabelas-do-banco-de-dados-sql","status":"publish","type":"post","link":"https:\/\/jonescarvalho.com\/Blog\/?p=367","title":{"rendered":"Fazendo Select para Insert em Tabelas do Banco de Dados SQL"},"content":{"rendered":"<p>Em algumas ocasi\u00f5es quando precisamos gerar scripts de Insert no banco de dados, encontramos algumas dificuldades em fazer isto.<\/p>\n<p>o c\u00f3digo abaixo mostra como gerar insert de tabelas, apartir do nome da tabela.<\/p>\n<p>segue:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">DECLARE @tableName varchar(100) ='Pessoa' -- aqui voc\u00ea informa o nome da tabela\n\nDECLARE cursCol CURSOR FAST_FORWARD FOR\n\nSELECT column_name,data_type FROM information_schema.columns\n\nWHERE table_name = @tableName\n\nOPEN cursCol\n\nDECLARE @string nvarchar(3000)\n\nDECLARE @stringData nvarchar(3000)\n\nDECLARE @dataType nvarchar(1000)\n\nSET @string='INSERT '+@tableName+'('\n\nSET @stringData=''\n\nDECLARE @colName nvarchar(50)\n\nFETCH NEXT FROM cursCol INTO @colName,@dataType\n\nIF @@fetch_status&lt;&gt;0\n\nbegin\n\nprint 'Tabela '+@tableName+' n\u00e3o encontrada, processo parado.'\n\nclose curscol\n\ndeallocate curscol\n\nreturn\n\nEND\n\nWHILE @@FETCH_STATUS=0\n\nBEGIN\n\nIF @dataType in ('varchar','char','nchar','nvarchar')\n\nBEGIN\n\nSET @stringData=@stringData+'''''''''+\n\nisnull('+@colName+','''')+'''''',''+'\n\nEND\n\nELSE\n\nif @dataType in ('text','ntext')\n\nBEGIN\n\nSET @stringData=@stringData+'''''''''+\n\nisnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'\n\nEND\n\nELSE\n\nIF @dataType = 'money'\n\nBEGIN\n\nSET @stringData=@stringData+'''convert(money,''''''+\n\nisnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'\n\nEND\n\nELSE\n\nIF @dataType='datetime'\n\nBEGIN\n\nSET @stringData=@stringData+'''convert(datetime,''''''+\n\nisnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'\n\nEND\n\nELSE\n\nIF @dataType='image'\n\nBEGIN\n\nSET @stringData=@stringData+'''''''''+\n\nisnull(cast(convert(varbinary,'+@colName+')\n\nas varchar(6)),''0'')+'''''',''+'\n\nEND\n\nELSE\n\nBEGIN\n\nSET @stringData=@stringData+'''''''''+\n\nisnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'\n\nEND\n\nSET @string=@string+@colName+','\n\nFETCH NEXT FROM cursCol INTO @colName,@dataType\n\nEND\n\nDECLARE @Query nvarchar(4000)\n\nSET @query ='SELECT '''+substring(@string,0,len(@string)) + ')\n\nVALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''\n\nFROM '+@tableName\n\nexec sp_executesql @query\n\nCLOSE cursCol\n\nDEALLOCATE cursCol<\/pre>\n<p>Ap\u00f3s isso, execute a string SQL, e ver\u00e1 o resultado.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Em algumas ocasi\u00f5es quando precisamos gerar scripts de Insert no banco de dados, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[18,28,30],"class_list":["post-367","post","type-post","status-publish","format-standard","hentry","category-dicas","tag-insert","tag-programacao","tag-sql"],"_links":{"self":[{"href":"https:\/\/jonescarvalho.com\/Blog\/index.php?rest_route=\/wp\/v2\/posts\/367"}],"collection":[{"href":"https:\/\/jonescarvalho.com\/Blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jonescarvalho.com\/Blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jonescarvalho.com\/Blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jonescarvalho.com\/Blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=367"}],"version-history":[{"count":3,"href":"https:\/\/jonescarvalho.com\/Blog\/index.php?rest_route=\/wp\/v2\/posts\/367\/revisions"}],"predecessor-version":[{"id":862,"href":"https:\/\/jonescarvalho.com\/Blog\/index.php?rest_route=\/wp\/v2\/posts\/367\/revisions\/862"}],"wp:attachment":[{"href":"https:\/\/jonescarvalho.com\/Blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=367"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jonescarvalho.com\/Blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=367"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jonescarvalho.com\/Blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=367"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}