0

I very rarely use SQL Server and in a professional context I keep well clear! I'm working on a pet project though and I'm have problems with a script creation.

I've got an online database that I need to extract everything out of. I use the Tasks > Generate Scripts option within SQL Server Management Studio. The following is an example of one insert statement the script creates (I have 1,000s of these inserts):

INSERT [dbo].[NewComics] ([NewComicId], [Title], [Subtitle], [ReleaseDate], [CollectionId]) VALUES (366, N'Hawk & Dove 1:                                                                                      ', N'First Strikes                                                                                       ', CAST(0x00009F6F00000000 AS DateTime), 248)

I have two issues with this:

(a) I want to strip all the whitespace from the two title elements (b) I don't want a HEX date - I want something readable like 2006-09-01 (yyyy-mm-dd)

INSERT [dbo].[NewComics] ([NewComicId], [Title], [Subtitle], [ReleaseDate], [CollectionId]) VALUES (366, N'Hawk & Dove 1:', N'First Strikes', '2006-09-01', 248)

What would be the quickest way to change about 3,000 insert statements to this revised format?

FYI - this is the design of the table:

[NewComicId] [int] NOT NULL,
[Title] [nchar](100) NOT NULL,
[Subtitle] [nchar](100) NULL,
[ReleaseDate] [datetime] NOT NULL,
[CollectionId] [int] NOT NULL,

Thanks in advance!

Improve this question
5
  • Where is the data going? What is the SQL Server edition? Version? You should be able to do Tasks > Export Data and dump it to a flat file, excel, or some other connection Commented May 30, 2012 at 23:27
  • I just want to make a drop and create SQL file for the whole database with the above requirements. I'm using SQL Management Studio 2012. Commented May 31, 2012 at 18:45
  • So you're saying you want to clean the data? Commented May 31, 2012 at 18:45
  • I suppose so... Imagine I've got 3,000 insert statements being generated in the style of the first code snippet above, but I want them in the style of the second snippet... Commented May 31, 2012 at 18:49
  • I filed a bug against this behavior. Please vote and comment! connect.microsoft.com/SQLServer/feedback/details/745796/… Commented Jun 1, 2012 at 17:39

2 Answers 2

Reset to default
5

Yes, generate scripts sadly scripts datetime columns as CONVERT(binary_value, Datetime). I'll try to get an answer as to why (or more importantly if there is a way to change the behavior). I suspect the reason is to avoid any issues with running the scripts on a different machine with different locale / regional settings etc. I don't know if there's a way to change that from happening in the meantime, but Management Studio isn't the only way to script your data... you could look into 3rd party products like Red-Gate's SQL Data Compare.

If it's really only 3,000 rows, and you intend to run the generated script on a different server, stop using the wizard and do this (on first glance this looks horrific, but it does several of the things you'll want - outputs a script ready to copy, paste and run, with nicely formatted and readable dates, inserts batched into multi-row VALUES by 1000 with GO commands in between, and even deals with potentially NULL values in title, subtitle and collectionid):

DECLARE @newtable SYSNAME = 'dbo.NewComics';

SET NOCOUNT ON;

;WITH x AS (SELECT TOP (4000) s = '(' 
    + CONVERT(VARCHAR(12), NewComicId) + ','
    + COALESCE('N''' + REPLACE(RTRIM(Title), '''', '''''') + '''', 'NULL') + ',' 
    + COALESCE('N''' + REPLACE(RTRIM(SubTitle), '''', '''''') + '''', 'NULL') 
    + ', ''' + CONVERT(CHAR(8), ReleaseDate, 112) + ' '
    + CONVERT(CHAR(8), ReleaseDate, 108) + ''','
    + CONVERT(VARCHAR(12), COALESCE(CollectionId, 'NULL')) + ')',
  rn = ROW_NUMBER() OVER (ORDER BY NewComicId)
  FROM dbo.OldComics ORDER BY NewComicId
),
y AS
(
SELECT [/*a*/] = 1, [/*b*/] = 'SET NOCOUNT ON;
GO
INSERT ' + @newtable + ' VALUES'
UNION ALL 
SELECT 2, s = CASE WHEN rn > 1 THEN ',' ELSE '' END + s
 FROM x WHERE rn BETWEEN 1 AND 1000
UNION ALL 
SELECT 3, 'GO' UNION ALL 
SELECT 4, s = CASE WHEN rn > 1001 THEN ',' ELSE '' END + s
 FROM x WHERE rn BETWEEN 1001 AND 2000
UNION ALL 
SELECT 5, 'GO' UNION ALL 
SELECT 6, s = CASE WHEN rn > 2001 THEN ',' ELSE '' END + s
 FROM x WHERE rn BETWEEN 2001 AND 3000
UNION ALL 
SELECT 7, 'GO' UNION ALL 
SELECT 8, s = CASE WHEN rn > 3001 THEN ',' ELSE '' END + s
 FROM x WHERE rn BETWEEN 3001 AND 4000
)
SELECT [/*b*/] FROM y ORDER BY [/*a*/];

(You might have to play with it if you have exactly 3000 or 3001 rows, or add another couple of unions if you have more than 4000, etc.)

If you are moving the data to a different table or different database on the same instance, use the script that @swasheck provided (and again, stop using the wizard).

You may have noticed a common trend here: stop using the generate scripts wizard if you don't like the binary format it outputs for dates.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Awesome! - This is exactly what I need and I can use it again and again. Many thanks Aaron!
2

So if this was me, what I'd do would be to build up the table structure in a separate database:

CREATE TABLE NewComics (
[NewComicId] [int] identity (0,1) NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[Subtitle] [nvarchar](100) NULL,
[ReleaseDate] [datetime] NOT NULL,
[CollectionId] [int] NOT NULL
);

ALTER TABLE [NewComics]
ADD CONSTRAINT PK_NewComicsID PRIMARY KEY;

And then use SQL to clean the data like so: 

INSERT INTO [NewDatabase].[dbo].[NewComics] (Title, Subtitle, ReleaseDate, CollectionID) 
SELECT 
    LTRIM(RTRIM(Title))
    , LTRIM(RTRIM(Subtitle))
    , CAST(ReleaseDate as Datetime)
    , CollectionID 
FROM [OldDatabase].[dbo].[NewComics];

Alternatively, you can use this same SELECT query:

SELECT 
    NewComicID
    , LTRIM(RTRIM(Title))
    , LTRIM(RTRIM(Subtitle))
    , CAST(ReleaseDate as Datetime)
    , CollectionID 
FROM [OldDatabase].[dbo].[NewComics];

as the source for an Import/Export Data Task (in the same menu that you've used to Generate Scripts). [OldDatabase] on this server would be the source and [NewDatabase] on this server would be the destination. Make sure you check the box to all identity inserts.

Improve this answer

6 Comments

OK. This didn't really work, but got me part way there! I had to change the [Title] and [Subtitle] to nvarchar and then it stripped the whitespace ok! The date though is still coming through as a hex date and not yyyy-mm-dd. Any ideas?
Whoops. Didn't even notice that. NVARCHAR would definitely be the right way to go. As for the dates, what is the output of SELECT ReleaseDate from NewComics? Are you sure that your table creation script is of time DATETIME?
Yep, definitely datetime - it's still coming out as CAST(0x00009D4B00000000 AS DateTime) though.
@Sniffer unfortunately this is the way Management Studio scripts it.
I'm not sure if SMO would do the same thing, but I suspect it would.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.