Script all data of a table

Question

Post reply

Script all data of a table

Florian Reischl

SSC-Dedicated

Points: 37301
More actions
February 25, 2009 at 7:27 am

#384014

Comments posted to this topic are about the item Script all data of a table

Viewing 15 posts - 1 through 15 (of 37 total)

You must be logged in to reply to this topic. Login to reply

Linson.Daniel Ten Centuries Points: 1153 More actions · Answer 1

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.....

dipetersen SSC Eights! Points: 888 More actions · Answer 2

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

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.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

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.

Chirag Prajapati SSC Eights! Points: 914 More actions · Answer 5

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.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

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

sbowell SSChasing Mays Points: 621 More actions · Answer 7

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

gtasneem Valued Member Points: 60 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 10

Thanks, Jeff!

I intend to update the script to add some missing data types like HIERARCHYID, UDTs, GEOMETRY and GEOGRAPHY.

Wish you best!

Flo

PS: Sorry for being out of the forums since a long time. I'm quiet busy with new projects. I'll be back as soon as possible!

Vivdix SSC-Addicted Points: 457 More actions · Answer 11

Hi,

I tried this on SQL Server 2000 but getting error on VARCHAR(MAX) line.

Will it not work on SQL Server 2000?

Thanks,

Vivek

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 12

Hi Vivek

The script was made for SQL Server 2005. However, if you change VARCHAR(MAX) to VARCHAR(8000) it might work, if your tables are not too wide.

In addition change:

SELECT @object_id = object_id, @schema_id = schema_id

FROM sys.tables

WHERE object_id = OBJECT_ID(@table_name)

... to something like this:

SELECT @object_id = id, @schema_id = schema_id

FROM systables

WHERE id = OBJECT_ID(@table_name)

Unfortunately I cannot test it on 2000 at the moment.

Hope this helps

Flo

Vivdix SSC-Addicted Points: 457 More actions · Answer 13

Hi Florian,

Still getting few errors -

WHERE TABLE_SCHEMA = SCHEMA_NAME(@schema_id)

'SCHEMA_NAME' is not a recognized function name.

DECLARE @select VARCHAR(MAX)

Line 82: Incorrect syntax near 'MAX'.

Please suggest on this.

Thanks,

Vivek

Vivdix SSC-Addicted Points: 457 More actions · Answer 14

Hi Florian,

Resolve the issue replacing -

DECLARE @select VARCHAR(MAX)

With

DECLARE @select VARCHAR(8000)

&

changing this query

SELECT @object_id = object_id, @schema_id = schema_id

FROM sys.tables

WHERE object_id = OBJECT_ID(@table_name)

to

SELECT @object_id = id, @schema_id = schema_id

FROM sysobjects

WHERE id = OBJECT_ID(@table_name) and type = 'U'

But still getting error at schema_id

Please help on this.

Thanks

Vivek