SQL Server Problem #2: data dump 4

[vongrunt]

Awright. Puz... problem #2. Nothing with dates

Every once in a while someone asks about SQL data dumping tool. Not DTS or bcp out/in (...damnit). Something like mysqldump -d, capable of generating buncha INSERT statements for each table we specify. Such tool would be quite handy in some situations so...

Write stored procedure that dumps single table as SQL code.

Prototype and output

Let's do it minimalistic. Only one input argument:

adm_dumpTable_<yournicknamehere>( @table_name sysname )

... and one returned result set with two columns:

1. lineNo int
2. SQLCode nvarchar(4000)


Things to take care about

Generated SQL code must be perfectly valid - string data properly quoted, NULLs written as "NULL" etc.
Anything above that is up to you. Imagine all possible scenarios during which generated code may fail or INSERT different values...


Things to ignore

BLOB/CLOB columns - text, ntext, image.
varchar size limit. We don't have tables with 250+ columns, right?
Timestamp columns.
Views and system tables - dump should work only on user tables, for obvious reasons.


Proposed testing

Create blank database. Choose one table, for example table Northwind.dbrders. Generate it's script (with primary key, but no foreign keys!) and dump data from that table with stored procedure from above. Run both script and code in blank DB. Etc etc.


Rules and scoring

Unlike previous problem, this one is hard to measure with stopwatch or something. Speed is irrelevant within reasonable margins - any code that doesn't suffer from excessive cursoritis is good to go. Let's do scoring this way:

- for each feature implemented/supported: +3 points. Example: identity inserts
- for each bug found: -1 point. Example: script crashes on varchar(N) column containing "O'Neill"

I know this scoring schema isn't perfect... but will make things competitive.
Regarding restrictions, you may not use anything outside database or call extended stored procedures.
Same as before - don't show any code until next Friday (Jan 27th).

If something isn't clear - just ask.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

I have one... relatively tricky but takes only few lines of code. Wanna hear it?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

yep

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[DotNetGnat]

two more stars for two stars from myside...

you guys are awesome...although i cannot contribute anything to match your level but i am learning a lot...

thanks

-DNG

 

[vongrunt]

Simple: calculate product per group. Something like SUM, but with * instead of +.

Specifics:
- aggregated values can be any numbers - positive, 0, negative - or NULL.
- this must be done with a single query
- no SQL2005 stuff is allowed (CREATE AGGREGATE/CLR calls etc).

If that's OK I can open new thread and post simple sample data + expected results.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Go ahead open new thread

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[DotNetGnat]

vongrunt...i gave a shot at your puzzle...not sure if i am correct...here is my code in simple format...

create table #temp(id int, value int)
insert into #temp(id, value) values (1, 10)
insert into #temp(id, value) values (2, 20)
insert into #temp(id, value) values (1, 30)
insert into #temp(id, value) values (3, 10)
insert into #temp(id, value) values (3, 10)
SELECT id, Power(10,SUM(LOG10(value))) from #temp group by id
drop table #temp

correct me if i am wrong...
-DNG

 

[SQLDenis]

DNG, isn't that result wrong?

Should't it be
1 40
2 20
3 20
instead of
1 300
2 20
3 100

Am I misunderstanding something here?
This is all we need don't we?

create table #temp(id int, value int)
insert into #temp(id, value) values (1, 10)
insert into #temp(id, value) values (2, 20)
insert into #temp(id, value) values (1, 30)
insert into #temp(id, value) values (3, 10)
insert into #temp(id, value) values (3, 10)
SELECT id, SUM(value) from #temp group by id
drop table #temp

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

See new thread...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DotNetGnat]

calculate product per group..isnt that what we wanted...

-DNG