Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Script all data of a table Expand / Collapse
Author
Message
Posted Wednesday, February 25, 2009 7:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Comments posted to this topic are about the item Script all data of a table


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #664232
Posted Wednesday, June 17, 2009 11:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 3:27 AM
Points: 313, Visits: 382
Nice script there, I too had written a similar script

http://www.sqlservercentral.com/scripts/insert+script+generator/65407/

but the bad thing about mine would be the fact that I had made use of cursors....and also since it was written for sql server 2000 it has its limitation when tried in 2005.....

Post #737159
Posted Friday, July 10, 2009 11:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 6, 2009 6:44 AM
Points: 18, Visits: 10
When I used it, there was an Error if the @column_names = 0. The word VALUES would concatenate at the end of the table name without a space(i.e. INSERT INTO dbo.FolderLevelLookupVALUES (0, 'None')
) . I just added a space before the word VALUES to fix it in the script.

...

PRINT ' '' VALUES ('' +'

...


Great script. Thanks



Kindest Regards,

David Petersen
dipetersen.com
Post #751291
Posted Saturday, July 11, 2009 5:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Linson.Daniel (6/17/2009)
Nice script there, I too had written a similar script

http://www.sqlservercentral.com/scripts/insert+script+generator/65407/

but the bad thing about mine would be the fact that I had made use of cursors....and also since it was written for sql server 2000 it has its limitation when tried in 2005.....

Hi Daniel

Sorry for the very late answer!! Seems I lost the response mail...

Just had a look at your script, maybe you should include QUOTENAME or REPLACE to avoid SQL injection by "'". For smaller tables cursors should be okay.



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #751556
Posted Saturday, July 11, 2009 5:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
dipetersen (7/10/2009)
When I used it, there was an Error if the @column_names = 0. The word VALUES would concatenate at the end of the table name without a space(i.e. INSERT INTO dbo.FolderLevelLookupVALUES (0, 'None')
) . I just added a space before the word VALUES to fix it in the script.

...

PRINT ' '' VALUES ('' +'

...


Great script. Thanks


Thanks for the feedback!

Thanks also for the bug report, I will fix this. Usually it takes some days until it becomes updated by admins.



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #751557
Posted Monday, August 10, 2009 5:30 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 6:48 AM
Points: 626, Visits: 77

Hi,

Excellent Script.

I m not sure whether you have updated it or not.
But it gives error in some cases where datatype is used as DbStamp of in case of Datetime.

Thanks,
Chirag.
Post #767756
Posted Wednesday, August 26, 2009 2:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi Craig

Chirag Prajapati (8/10/2009)
But it gives error in some cases where datatype is used as DbStamp of in case of Datetime.

Thanks for the feedback!

Could you explain the error and how it happens? What do you mean with DbStamp?

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #777347
Posted Thursday, August 27, 2009 3:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, January 15, 2011 2:34 PM
Points: 83, Visits: 118
Hi,

Love the script.

I had a small problem when I tried running it due to the collation being wrong. I was getting the error

Cannot resolve collation conflict for column 1 in SELECT statement.

To fix this I changed the following at the end of the script

-- Script the end of the statement
PRINT ' '') COLLATE Latin1_General_CI_AS'''
PRINT ' FROM ' + @table_name

This then worked a treat.

Thanks again
Post #778786
Posted Monday, October 12, 2009 2:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 18, 2013 8:43 AM
Points: 8, Visits: 38
Nice post.
Here's a script to just update data in a particular column in a table

select
'update @tablename set @columnname = ',
'''' + @columnname + '''',
'where @primarykeycolumn = ',
@primarykeycolumn
from @tablename

It generates a script which when run, updates that particular column in that table.
Post #801838
Posted Thursday, October 22, 2009 10:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 35,540, Visits: 32,123
I missed this before, Flo... it's written well enough that it probably should have been an article. Well done.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #807604
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse