Is it possible to have a stored procedure or script that I can run which will generate a script in a new query window without truncating data?
I am aware of the options in SSMS to increase column width/characters returned, however my data exceeds this length. My aim is to streamline my process of scripting data in one database, modifying the script, and running it against another database on a different server on a physically separate network.
I have 2 tables to script data for (tlkpProduct, and tlkpModule). When I go to Tasks > Generate scripts... I can save the results to a new query window or file and save as .sql, but before I can run the script on the other server, I need to modify it in the following ways:
- remove lines that say
"SET IDENTITY_INSERT [dbo].[tlkpModule] ON
because the data i am scripting is coming from a db with primary keys, and is going into a db that cannot have primary keys defined.
- insert some code at the start of the generated script delete the existing data, which is:
"TRUNCATE TABLE [dbo].[tlkpProduct]
TRUNCATE TABLE [dbo].[tlkpModule]
I wrote a stored procedure which was a series of select statements, also using SET NOCOUNT ON so I didn't get the "rows affected" lines. However I noticed that the resulting data was getting truncated because of the limitation of about 8k characters per column. Each insert statement could exceed ~8k characters because I am scripting columns of type VARCHAR(MAX) .