Alter the variable @tableName to the table and also the @tableWhere for the where condition, I found that if you use the standard sql dumps that you was taking allot of other crap with you as well.<\/p>\n
The code<\/p>\n
\r\ndeclare @colName varchar(100), @colSql varchar(500), @colSQLInsert varchar(500), @type int, @auto int, @tableName varchar(50), @tableWhere varchar(500);\r\nset @tableName = 'tablename';\r\nset @tableWhere = 'the where condition';\r\n\r\n-- grab the table column names\r\ndeclare tablecol cursor for\r\nselect name, typestat, autoval from syscolumns where id = (select id from sysobjects where name = @tableName);\r\n\r\n--The @auto is the auto generated fields e.g. primary key. <\/b>\r\nset @colSql = '';\r\nset @colSQLInsert = '';\r\nopen tablecol;\r\nfetch tablecol into @colName, @type, @auto;\r\nwhile (@@fetch_status = 0)\r\nbegin\r\n if (@auto is null)\r\n begin\r\n if (charindex('.',@colName) > 0) set @colName = '['+@colName+']';\r\n set @colSQL = @colName + ',' +@colSQL;\r\n if (@type = 2)\r\n set @colSQLInsert = '''''''+isnull(' + @colName + ',0)+'''''',' + @colSQLInsert;\r\n else\r\n set @colSQLInsert = '''+str(isnull(' + @colName + ',''''))+'',' + @colSQLInsert;\r\n end\r\n fetch next from tablecol into @colName, @type, @auto;\r\nend\r\nclose tablecol;\r\ndeallocate tablecol;\r\n-- to build the sql statement, since it stops at 255 charactes split the outputs<\/b>\r\nselect 'select (''insert into '+@tableName+' (';\r\ndeclare @loopingVal int;\r\nset @loopingVal =0;\r\nwhile (len(@colSQL) > @loopingVal)\r\nbegin\r\n select substring(@colSQL, @loopingVal, 255);\r\n set @loopingVal = @loopingVal + 255;\r\nend\r\nselect ') values (';\r\n\r\nset @loopingVal =0;\r\nwhile (len(@colSQLInsert) > @loopingVal)\r\nbegin\r\n select substring(@colSQLInsert, @loopingVal, 255);\r\n set @loopingVal = @loopingVal + 255;\r\nend\r\nselect ')'') from '+@tableName + ' ' + @tableWhere;\r\n<\/pre>\nThe output will be x lines, and if you just copy them into a single line and this will display (once executed) the insert lines.<\/p>\n
If anyone has any better methods, please comment \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"
Alter the variable @tableName to the table and also the @tableWhere for the where condition, I found that if you use the standard sql dumps that you was taking allot of other crap with you as well. The code declare @colName varchar(100), @colSql varchar(500), @colSQLInsert varchar(500), @type int, @auto int, @tableName varchar(50), @tableWhere varchar(500); set … Continue reading Grab data from tables<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[398,23,22,24,25,405,400],"class_list":["post-279","post","type-post","status-publish","format-standard","hentry","category-sql","tag-linux","tag-ms-sql","tag-mysql","tag-oracle","tag-postgres","tag-sql","tag-windows"],"_links":{"self":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/279","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/comments?post=279"}],"version-history":[{"count":1,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/279\/revisions"}],"predecessor-version":[{"id":280,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/279\/revisions\/280"}],"wp:attachment":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/media?parent=279"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/categories?post=279"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/tags?post=279"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}