InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 21:44:14 GMT20http://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspx[quote][b]Slawomir Swietoniowski (5/14/2010)[/b][hr]Hi,Well, in SSMS 2008 R2 this feature (scripting data) is no longer available, so this solutions is usefull again :-).Another option is to use this add-in to SSMS (it can scripting out table content): [url=http://www.ssmstoolspack.com/]http://www.ssmstoolspack.com/[/url].Anyway, this solution is brilliant.Regards[/quote]Slawomir,The scripting data feature is still available in SSMS 2008 R2, but it appears to be under a different heading from SSMS 2008. The property option is now available under the "General" header and called "Type of data to script". It has 3 options in the drop down: Data Only, Schema and data, Schema Only. For those not aware, you can get to the scripting: by right clicking the mouse on a database, Tasks > Generate Scripts... > Choose "Select specific database objects" > Select the tables you want to script > then select the "Advanced" button to get to the "Type of data to script" property.Thanks,John DTue, 18 Jan 2011 10:06:29 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxNice script !!! I suggest a small improving, special handling not only of text datatype but also of ntextwhen 'ntext' then 'cast([' + column_name + '] as nvarchar(max))'You can update your script with itMon, 17 May 2010 04:09:01 GMTFederico Iorihttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxNice Article. Very helpful.Thanks.Mon, 17 May 2010 03:53:15 GMTAtif-ullah Sheikhhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxOlegThe script is excellent no doubt about it.The problem with it that it is database specific + you have to connect to the database first.I had several situations when I was in the airport and all I had an excel file from the customer.Likely there is another solution which can help you in time of need[url=http://www.dbsoftlab.com/Advanced-ETL-Processor-News/Generate-Insert-Statements.html]http://www.dbsoftlab.com/Advanced-ETL-Processor-News/Generate-Insert-Statements.html[/url]Keep coding,MikeFri, 14 May 2010 12:06:48 GMTMike-1076638http://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxNice article. I'm glad to have finally seen it.Fri, 14 May 2010 11:54:02 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxHi,Well, in SSMS 2008 R2 this feature (scripting data) is no longer available, so this solutions is usefull again :-).Another option is to use this add-in to SSMS (it can scripting out table content): [url=http://www.ssmstoolspack.com/]http://www.ssmstoolspack.com/[/url].Anyway, this solution is brilliant.RegardsFri, 14 May 2010 10:54:08 GMTSlawomir Swietoniowskihttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspx[quote][b]Emito (5/14/2010)[/b][hr]You have a really good script for this in this url:http://vyaskn.tripod.com/code/generate_inserts.txtWritten by: Narayana Vyas KondreddiI used a lot, its really nice!!! :-D.[/quote]Yes, the old script you mention has been used by many in the past. It does not handle some data types, such as image and varbinary(max). Additionally, that script has a while loop in it used just to get column names and their data types. Using the loop is not a good idea when you can use a rather simple select statement to do the work. I specifically mentioned in the beginning of the article that I am offering no cursors, no while loops, no bunch of single quotes solution to the task of generating insert statements.My script also suffers from data type limitations, but this is only because of the small bugs in it. For example, I simply did not check floats and handling them requires small tweaks to the script. However, my script can potentially handle the data types which are not handled by the script to which you refer.OlegFri, 14 May 2010 08:47:32 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxYou have a really good script for this in this url:http://vyaskn.tripod.com/code/generate_inserts.txtWritten by: Narayana Vyas KondreddiI used a lot, its really nice!!! :-D.Fri, 14 May 2010 06:21:25 GMTEmitohttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxI've often used a useful stored procedure for this type of thing, also works like a charm! Feel free to check it out at http://vyaskn.tripod.com/code.htm direct links to the stored procedure source for 2000 is http://vyaskn.tripod.com/code/generate_inserts.txt , for 2005/8 its http://vyaskn.tripod.com/code/generate_inserts_2005.txtFri, 14 May 2010 04:44:31 GMTluke.baughanhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxthank you :-)... i have been using this script from the time it was first published... is there any change from the first query ?Fri, 14 May 2010 03:56:46 GMTziangijhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxalready done in ssms 2008, but nice try I might find it usefulFri, 14 May 2010 01:49:11 GMTmisus28http://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxThat is very interesting, bud i have a problem with "float" data type :unsure:Wed, 12 May 2010 06:50:42 GMTsergio.eduardo.linareshttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxExcellent Article man...will be really helpful!!Wed, 10 Feb 2010 08:18:59 GMTradityanhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxAnd I'll likely page the resulting statements so I can keep inserts to batches of 1,000 at a time -- it works faster this way.Fri, 08 Jan 2010 10:20:30 GMTDuncan A. McRaehttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxNot only did I appreciate this script, I *USE* it. I made a slight change to shrink the output a tad (shown below). The negative to my approach is that I must manually delete the very last "union all" piece, but I can live with that. Thanks Boss! set @script = 'select ''select '' + ' + substring(@values, 11, len(@values)) + ' + '') union all '' from ' + @table; if ( @is_identity = 1 ) begin print ('set identity_insert ' + @table + ' on'); end print 'insert into ' + @table + ' (' + substring(@columns, 3, len(@columns)) + ')' exec sp_executesql @script; if ( @is_identity = 1 ) begin print ('set identity_insert ' + @table + ' off'); endThu, 07 Jan 2010 12:06:16 GMTDuncan A. McRaehttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxi have written some code to help developers spend less time on scripting basic statements.the first link is a demonstration of this:[url=http://www.sqlservercentral.com/scripts/AutoGenerate/65042/][/url]the second one is the actual code that uses metadata to easily script select, insert and update statements:[url=http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/][/url]the next script is useful for deleting the statements produced by the above code, useful for when changes like a new table or column have been introduced and its time to redefine the stored procs to match:[url=http://www.sqlservercentral.com/scripts/Stored+Procedures/65869/][/url]Wed, 26 Aug 2009 00:44:43 GMTkodracon http://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxi have written some code to help developers spend less time on scripting basic statements.the first link is a demonstration of this:[url=http://www.sqlservercentral.com/scripts/AutoGenerate/65042/][/url]the second one is the actual code that uses metadata to easily script select, insert and update statements:[url=http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/][/url]the next script is useful for deleting the statements produced by the above code, useful for when changes like a new table or column have been introduced and its time to redefine the stored procs to match:[url=http://www.sqlservercentral.com/scripts/Stored+Procedures/65869/][/url]Wed, 26 Aug 2009 00:16:33 GMTkodracon http://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxi have written some code to help developers spend less time on scripting basic statements.the first link is a demonstration of this:[url=http://www.sqlservercentral.com/scripts/AutoGenerate/65042/][/url]the second one is the actual code that uses metadata to easily script select, insert and update statements:[url=http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/][/url]the next script is useful for deleting the statements produced by the above code, useful for when changes like a new table or column have been introduced and its time to redefine the stored procs to match:[url=http://www.sqlservercentral.com/scripts/Stored+Procedures/65869/][/url]Wed, 26 Aug 2009 00:06:07 GMTkodracon http://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxi have written some code to help developers spend less time on scripting basic statements.the first link is a demonstration of this:http://www.sqlservercentral.com/scripts/AutoGenerate/65042/the second one is the actual code that uses metadata to easily script select, insert and update statements:http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/the next script is useful for deleting the statements produced by the above code, useful for when changes like a new table or column have been introduced and its time to redefine the stored procs to match:http://www.sqlservercentral.com/scripts/Stored+Procedures/65869/Wed, 26 Aug 2009 00:02:36 GMTkodracon http://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspx[quote][b]sathesg (8/4/2009)[/b][hr]The script is very good, but how to convert the data again to english ?[/quote]You don't have to. It is already stored in correct format. Selecting inserted records from the table will display data correctly.OlegTue, 04 Aug 2009 09:53:24 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxThe script is very good, but how to convert the data again to english ?Tue, 04 Aug 2009 06:59:50 GMTsathesghttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxgood work. You gave me another tool for my arsenal and a fresh perspective on handling raw data versus "human" data.I would say as other mentioned that you should update usp_generate_inserts to support 2 parameters one for table one for schema. That way there is no confusion. Thanks again,TWed, 08 Jul 2009 08:15:30 GMTMudLuckhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxOleg,Well spotted, thanks for the advice. Running in "Results to grid" mode worked a treat. Fantastic.MattTue, 07 Jul 2009 07:13:24 GMTwilkimatthttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspx[quote][b]a_k93 (7/3/2009)[/b][hr]Hi there,Thnx for your reply. I tried your work around but it seems to be not working, may be I am making some mistake, but let me tell you what I have tried.[b]1.[/b] Created a table called Test1[b]2.[/b] Added 2 columns [b]a)[/b] col1 float & [b]b)[/b] col2 numeric(38,17)[b]3.[/b] Inserted 2 records.12345678998765432 123456789.98765432100000000123456789.98765433 123456789987.65432100000000000[b]4.[/b] Generated the insert statement by the SP you have providedinsert into dbo.test1 ([col1], [col2]) values (0x4345ee2a319dbfbc, 0x261100010091ba4e78fe7c994c360a00);insert into dbo.test1 ([col1], [col2]) values (0x419d6f3457f35ba8, 0x261100010068c688f3054290371be427);[b]5.[/b] Created a table called Test1 in another Db.[b]6.[/b] Added 2 columns [b]a)[/b]col1 numeric(38,10) [b]b)[/b]col2 numeric(38,17)[b]7.[/b] Tried to execute the insert statement, it raised an error Arithmetic overflow error converting varbinary to data type numeric.[b]8.[/b] At the same time, if I copied these 2 records by means of CTRL+C and pasted on newly created table Test1 it got pasted.Regards,a_k93[/quote]There are a couple of problems here. In my previous reply to you I quoted information from the BOL, but let me repeat it again here, because it is rather important. According to BOL float and real are[quote]Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.When float is declared without number of bits that are used to store the mantissa specified then that number defaults to 53. [/quote]While the range of values of float is huge (- 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308), [b][i]the data is approximate [/i][/b]part of the BOL quote is the key. This is because the precision of the float when n is omitted (defaults to 53) is 15 digits, that is all. This means that in your example, you cannot really store the number 12345678998765432 as float without losing precision. For example, if you try to select * from your table you will notice that the actual value of col1 of the first record comes out as 1.23456789987654E+16 loosing last 2 digits, namely 32.Internally, float is stored differently from numeric (decimal) data type. Here is example:[code]declare @f float, @n numeric(15, 6);set @f = 123456789.987654;set @n = 123456789.987654;-- float is stored differently, it occupies 8 bytes while numeric - 9select cast(@f as varbinary(max)) f, datalength(@f) f_length, cast(@n as varbinary(max)) n, datalength(@n) n_length;[/code]Results:f f_length n n_length---------------------------------------------------------------------0x419D6F3457F35B92 8 0x0F06000146C11C8648 9The second problem is the small bug in my code which handles the text but does not handle the float. Float, text and ntext are the data types which do not have explicit conversion from varbinary(max). This means that case part of my procedure should be modifed like this:The case statement currently reading[code]case data_type when 'text' then 'cast([' + column_name + '] as varchar(max))' else '[' + column_name + ']' end + ' as varbinary(max))), ''null'')'[/code]should become[code]case data_type when 'float' then 'cast([' + column_name + '] as decimal(38, 10))' when 'ntext' then 'cast([' + column_name + '] as nvarchar(max))' when 'text' then 'cast([' + column_name + '] as varchar(max))' else '[' + column_name + ']' end + ' as varbinary(max))), ''null'')'[/code]This will solve your problem (except those 2 lost digits but nobody can help with that, float data type is indeed approximate). Please replace the case part of the procedure as described before and inserts for your table will be generated correctly. This will teach me a good lesson to test the solution more thoroughly before attempting to publish article on this site.OlegFri, 03 Jul 2009 10:18:12 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxI really like this a lot, Oleg! And Florian, a pity your script went largely unnoticed - you should have submitted it as an article!Real shame about the 4000 char limitation. However I guess given we can sp_helptext both functions, we could simply script our own corrected functions to get round this limitation.Thanks again,David.Fri, 03 Jul 2009 07:21:19 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxHi there,Thnx for your reply. I tried your work around but it seems to be not working, may be I am making some mistake, but let me tell you what I have tried.[b]1.[/b] Created a table called Test1[b]2.[/b] Added 2 columns [b]a)[/b] col1 float & [b]b)[/b] col2 numeric(38,17)[b]3.[/b] Inserted 2 records.12345678998765432 123456789.98765432100000000123456789.98765433 123456789987.65432100000000000[b]4.[/b] Generated the insert statement by the SP you have providedinsert into dbo.test1 ([col1], [col2]) values (0x4345ee2a319dbfbc, 0x261100010091ba4e78fe7c994c360a00);insert into dbo.test1 ([col1], [col2]) values (0x419d6f3457f35ba8, 0x261100010068c688f3054290371be427);[b]5.[/b] Created a table called Test1 in another Db.[b]6.[/b] Added 2 columns [b]a)[/b]col1 numeric(38,10) [b]b)[/b]col2 numeric(38,17)[b]7.[/b] Tried to execute the insert statement, it raised an error Arithmetic overflow error converting varbinary to data type numeric.[b]8.[/b] At the same time, if I copied these 2 records by means of CTRL+C and pasted on newly created table Test1 it got pasted.Regards,a_k93Fri, 03 Jul 2009 05:51:18 GMTa_k93http://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspx[quote][b]noelpv (7/2/2009)[/b][hr]Or you can set column width:Tools \ Options \ Query results \ SQL Server \ Results to Text \ Maximumnumber of characters displayed in each column[/quote]Yes, this is exactly what I wrote in my yesterday's reply.Thu, 02 Jul 2009 11:42:16 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxOr you can set column width:Tools \ Options \ Query results \ SQL Server \ Results to Text \ Maximumnumber of characters displayed in each columnThu, 02 Jul 2009 11:14:46 GMTnoelpvhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspx[quote][b]a_k93 (7/2/2009)[/b][hr]Hi there,Nice article, I have one question. When I change your sample code from... and ran the statement it raised an error Operand type clash: varbinary is incompatible with floatAny help will be appreciated.Regards,a_k93[/quote]There are 4 data types which cannot be explicitly converted from varbinary. Those are float, real (ISO synonym for real is float(24)), text and ntext. There is a table of all possible conversion combinations on [url=http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx]MSDN site[/url] in Remarks section. According to BOL float and real are[quote]Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.When float is declared without number of bits that are used to store the mantissa specified then that number defaults to 53. [/quote]This means that in this case it has 15 digits precision and occupies 8 bytes.To make the long story short, yesterday I posted a reply to Terry Sycamore who was asking about the similar problem with text (post from me yesterday at 1:52:15 PM, on page 2 of this forum). I came up with one workaround, which is to alter the table's column from text to varchar(max), run the procedure and then alter the table's column back to text. This might not be the best way to do it but it works. Additionally, Microsoft strongly recommends to stop using text data type anyway as it will not be supported in future versions of SQL Server. In your case, you can alter the float column to its respective decimal (p, s) equivalent, run the script and then alter the column back to float.Hope this helps.OlegThu, 02 Jul 2009 10:24:00 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspx[quote][b]imran.raza (7/2/2009)[/b][hr]I have found more better way to do this as mention belowset quoted_identifier offdeclare @sql1 nvarchar(1000)set @sql1="select 'insert county (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from county"print @sql1exec sp_executesql @sql1; =====================================================Instead of using lots of ''''''''''''''' and replace function above can be used...Imran.http://razaimran.blogspot.com/[/quote]Imran,I am sorry but you did not find a better way. In the very beginning of the article I mentioned about the challenges associated with a straightforward implementation of the insert generating script, similar to the one of yours. These include conversion issues and single quotes/nulls handling. Let's consider the following script (your script but I will also create the table first):[code]use AdventureWorks;go-- create tablecreate table dbo.county (county_fips varchar(50) null, state_fips datetime null);-- insert some recordsinsert into dbo.county (county_fips, state_fips) values ('It aint''t not working though I think it should', '2009-05-30');insert into dbo.county (county_fips, state_fips) values (null, '2009-06-30');insert into dbo.county (county_fips, state_fips) values ('No single quotes here', null);-- your scriptset quoted_identifier offdeclare @sql1 nvarchar(1000)set @sql1="select 'insert county (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from county"print @sql1;exec sp_executesql @sql1; [/code]The above fails like this:select 'insert county (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from countyMsg 241, Level 16, State 1, Line 1Conversion failed when converting datetime from character string.OK, lets make another table as simple as it can be with both columns as varchars:[code]-- create tablecreate table dbo.county2 (county_fips varchar(50) null, state_fips varchar(50) null);-- insert some recordsinsert into dbo.county2 (county_fips, state_fips) values ('It aint''t working though I think it should', 'a');insert into dbo.county2 (county_fips, state_fips) values (null, 'b');insert into dbo.county2 (county_fips, state_fips) values ('No single quotes here', null);-- your scriptset quoted_identifier offdeclare @sql1 nvarchar(1000)set @sql1="select 'insert county2 (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from county2"print @sql1;exec sp_executesql @sql1; [/code]This does not fail, but does not generate inserts either. The first record will fail to execute because it will encounter unclosed string, the other two records are simply nulls, because concatenation of null to existing value yields null by default. Here is the dump from query results window:select 'insert county2 (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from county2-----------------------------------------------------------------------------insert county2 (county_fips, state_fips) values('It aint't working though I think it should','a')NULLNULL(3 row(s) affected)Please don't forget to drop those tables.OlegThu, 02 Jul 2009 09:30:04 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxHi there,Nice article, I have one question. When I change your sample code from use AdventureWorks;......declare @t table (col1 [b]int[/b] not null, col2 varchar(30));insert into @t (col1, col2)values(......[b]TO[/b]use AdventureWorks;......declare @t table (col1 [b]float[/b] not null, col2 varchar(30));insert into @t (col1, col2)values(......and ran the statement it raised an error Operand type clash: varbinary is incompatible with floatAny help will be appreciated.Regards,a_k93Thu, 02 Jul 2009 09:12:38 GMTa_k93http://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspx[quote][b]wilkimatt (7/2/2009)[/b][hr]Scripting in binary format is a great idea, however the script is failing for some reason with this table:.....................The result is this:insert into dbo.ecspuraccount ([id], [reference], [name], [address_1], [address_2], [address_3], [address_4], [address_5], [postcode], [telephone_1], [telephone_4], [email_1], [vatnumber], [days_pay_2], [co_pref], [locked_1], [locked_2]) values (0x00000001...............................Presumably some variable is not big enough. This was scripting to a file by the way. Had a quick look but can't see how this could happen. Any bright ideas!? Matt[/quote]Matt,The script does not fail, but the settings of your editor window are too small to display the records in their entirety. For example, out of the box Management Studio only displays 1024 characters per column if you execute the query with results to text option. Because every insert statement is a single record with single column, the record is truncated. In one of my replies yesterday (the one on page 2 of this forum) I mentioned the necessity to adjust the query results if needed. The script to file option borrows the results to text settings. This means that the easiest way is to Ctrl+D (results to grid), execute the proc, click the results header, Ctrl+C, notepad (or whatever text editor), Ctrl+V. Hope this helps.OlegThu, 02 Jul 2009 09:02:43 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxScripting in binary format is a great idea, however the script is failing for some reason with this table:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[EcsPurAccount]( [id] [int] NOT NULL, [reference] [varchar](12) NOT NULL, [name] [varchar](45) NULL, [address_1] [varchar](45) NULL, [address_2] [varchar](45) NULL, [address_3] [varchar](45) NULL, [address_4] [varchar](45) NULL, [address_5] [varchar](45) NULL, [postcode] [varchar](11) NULL, [telephone_1] [varchar](21) NULL, [telephone_4] [varchar](21) NULL, [email_1] [varchar](71) NULL, [vatnumber] [varchar](21) NULL, [days_pay_2] [int] NULL, [co_pref] [varchar](5) NULL, [locked_1] [int] NULL, [locked_2] [int] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFThe result is this:insert into dbo.ecspuraccount ([id], [reference], [name], [address_1], [address_2], [address_3], [address_4], [address_5], [postcode], [telephone_1], [telephone_4], [email_1], [vatnumber], [days_pay_2], [co_pref], [locked_1], [locked_2]) values (0x00000001insert into dbo.ecspuraccount ([id], [reference], [name], [address_1], [address_2], [address_3], [address_4], [address_5], [postcode], [telephone_1], [telephone_4], [email_1], [vatnumber], [days_pay_2], [co_pref], [locked_1], [locked_2]) values (0x00000002insert into dbo.ecspuraccount ([id], [reference], [name], [address_1], [address_2], [address_3], [address_4], [address_5], [postcode], [telephone_1], [telephone_4], [email_1], [vatnumber], [days_pay_2], [co_pref], [locked_1], [locked_2]) values (0x00000003insert into dbo.ecspuraccount ([id], [reference], [name], [address_1], [address_2], [address_3], [address_4], [address_5], [postcode], [telephone_1], [telephone_4], [email_1], [vatnumber], [days_pay_2], [co_pref], [locked_1], [locked_2]) values (0x00000004Presumably some variable is not big enough. This was scripting to a file by the way. Had a quick look but can't see how this could happen. Any bright ideas!? MattThu, 02 Jul 2009 04:00:09 GMTwilkimatthttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxI have found more better way to do this as mention belowset quoted_identifier offdeclare @sql1 nvarchar(1000)set @sql1="select 'insert county (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from county"print @sql1exec sp_executesql @sql1; =====================================================Instead of using lots of ''''''''''''''' and replace function above can be used...Imran.http://razaimran.blogspot.com/Thu, 02 Jul 2009 03:53:30 GMTimran.razahttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxThere is a minor bug in the procedure.Input parameter is 'schema.Table' but the schema part is not used when selecting from information_schema.columns. It doesn't matter if you don't have the same table name in multiple schemas (as we sometimes do).I modified that section as:... from information_schema.columns where table_schema = left(@table, charindex('.', @table) - 1 ) and table_name = substring(@table, charindex('.', @table) + 1, len(@table)) ...and then it worked as intended.Thu, 02 Jul 2009 03:40:11 GMTsven.purbehttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspx[quote][b]Tony Webster (7/1/2009)[/b][hr]Also, not that it probably matters too much in practice, but that function call has a bit of a performance hit - that's what makes it take about 30 to 50 times as long as the insert generation script I use. I tried getting the CONVERT approach to work in SQL Server 2005, but the few variations I tried seemed to indicate that casting varbinary to varchar didn't work - just seemed to return blanks.[/quote]Tony,Yes, that function call has a huge performance hit. This is easily understandable though because T-SQL is not the language one wants to use to buffer the strings. Removing the 1,999 characters restriction will make it even slower. But the bottom line is that if you have a table in the development database similar to the one of Production.Document of AdventureWorks and you do need to submit a script creating this table as well as the insert statements for its records because creating SSIS package for exporting data from development to say QA is not an option then the procedure I described can make it happen. There are faster scripts, but they do not necessarily include generation of insert statements for varbinary(max) columns.The situation with CONVERT is the following: extended version of it in SQL Server 2008 allows conversion of binary values to varchar(max). I replied earlier to Chris Howarth who wrote:[quote][b]Chris Howarth (7/1/2009)[/b][hr]Another new feature of SQL Server 2008 is an extension to the CONVERT function whereby a binary value can be converted to a string representation.e.g. SELECT CONVERT(VARCHAR(MAX), 0x484920544845524521, 1)...returns:0x484920544845524521This removes the need for a 'BinToHexStr' function.[/quote]This is great that extended function like this exists in 2008. It makes me wish I could switch to SQL Server 2008 today, but this is not my call. In 2005 version it does not return 0x484920544845524521 though. It returns [b]HI THERE[/b]!, which is what one would expect it to return. Calling CONVERT(varchar(max), YOUR_BINARY_COLUMN, 1) in 2005 version will return few unreadable characters, so it cannot be used directly.Yes, my script maybe slow for wide data, but it does not seem to be too bad for reasonably narrow tables even if they have a big number of records. It can be easily adjusted to include the predicates in order to generate inserts for a subset of existing data.OlegWed, 01 Jul 2009 20:01:21 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxNice article!When Possible, I will add IF EXISTS and UPDATE sentences and post it hereWed, 01 Jul 2009 18:45:59 GMTnoelpvhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspx[quote]The insert-generating method described above is clearly error prone.[/quote]Well, yes - but producing binary inserts clearly loses some of the flexibility I often use a scripting procedure for in the first place. This looks quite suited to moving bulk test data across if you want a deployment script (which I guess is pretty much what it is intended for) but more often than not I want a procedure you could add a condition to, and perhaps edit the output, just to transfer selected records. When I wrote about this previously, I felt that many didn't really get the point that you can use insert statement generators for different purposes than just scripting the contents of an entire table.Also, not that it probably matters too much in practice, but that function call has a bit of a performance hit - that's what makes it take about 30 to 50 times as long as the insert generation script I use. I tried getting the CONVERT approach to work in SQL Server 2005, but the few variations I tried seemed to indicate that casting varbinary to varchar didn't work - just seemed to return blanks.Interesting idea though. I lament somewhat the deprecation of DTS for SSIS, which seems like a sledgehammer to crack a nut for much of what I want to do day-to-day, and this could be useful for things like setting up a test environments for databases that aren't too big.Wed, 01 Jul 2009 18:38:26 GMTTony Websterhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspx[quote][b]TSycamore (7/1/2009)[/b][hr]Oleg,Interesting...What I am getting when I run your example of handling a text data type is:insert into dbo.test ([col1], [col2]) values (0x00000001, 0x73616d706c6520312c73616d706c652031);insert into dbo.test ([col1], [col2]) values (0x00000002, 0x73616d706c6520322c73616d706c652032);When I try to run these inserts into dbo.test, I get:Operand type clash: varbinary is incompatible with textTerry[/quote]Thank you very much Terry. I used the AdventureWorks database when I worked on the script, which does not have any tables with text columns. I tested the generation of the insert statements against other database which had tables with text columns but this was as far as I went. Here is one workaround I can suggest. It is admittedly silly, but it works. The script below assumes that we are where we were left off: the table dbo.test is created, but generated insert statements fail miserably.Here is the script, but please replace the occurences of the word alterz with alter. If I spell them out correctly in the code block then I cannot submit the post :([code]set nocount on;godelete dbo.test;go-- this does not work, there is a good reason why Microsoft recommends -- not to use the text data type, but switch to varchar(max) insteadinsert into dbo.test ([col1], [col2]) values (0x00000001, 0x73616d706c6520312c73616d706c652031);insert into dbo.test ([col1], [col2]) values (0x00000002, 0x73616d706c6520322c73616d706c652032);goalterz table dbo.test alter column col2 varchar(max);go-- this worksinsert into dbo.test ([col1], [col2]) values (0x00000001, 0x73616d706c6520312c73616d706c652031);insert into dbo.test ([col1], [col2]) values (0x00000002, 0x73616d706c6520322c73616d706c652032);go-- now revert the table back to what it wasalterz table dbo.test alter column col2 text;goselect * from dbo.test;goset nocount off;go[/code]Here is the dump from results window:Msg 206, Level 16, State 2, Line 4Operand type clash: varbinary is incompatible with textcol1 col2-----------------------------1 sample 1,sample 12 sample 2,sample 2The idea is to temporarily alter the suspect table changing the text columns to varchar(max) and then reverting the change back to what it was. Though I would seriously consider permanent altering of the tables with text columns to replace those with varchar(max) or nvarchar(max). Hope this helps.OlegWed, 01 Jul 2009 15:06:08 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic745044-1577-1.aspxOleg,Interesting...What I am getting when I run your example of handling a text data type is:insert into dbo.test ([col1], [col2]) values (0x00000001, 0x73616d706c6520312c73616d706c652031);insert into dbo.test ([col1], [col2]) values (0x00000002, 0x73616d706c6520322c73616d706c652032);When I try to run these inserts into dbo.test, I get:Operand type clash: varbinary is incompatible with textTerryWed, 01 Jul 2009 14:03:00 GMTTSycamore