May 20, 2008 at 8:08 am
Quite often when scripting database changes, I'll use the SSMS designer to make a change, and then generate a change script. Often I then customise this script to my requirements.
My question relates to when one inserts a column into the middle of a table. The change script will build a new table (Tmp_) with the new columns and then insert from the original table.
The part of the script which inserts is generated thus [font="Courier New"]EXEC ('INSERT INTO dbo.Tmp_MyTable (Field1, ....) SELECT Field1,.... FROM dbo.MyTable WITH (HOLDLOCK TABLOCKX)')[/font]
The question is (finally!) why is the above generated with an EXEC statement rather than just INSERT INTO etc.. like the rest of the script.
I ask because I want to customize that part of the script, and I'd rather remove it from the EXEC, but I was wondering if there might be a reason why I shouldn't.
Any ideas?
Thanks,
David McKinney.
May 20, 2008 at 4:51 pm
Show us the whole script. It's probably because of something that happens the step before or the step after.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 20, 2008 at 4:55 pm
OK, I just checked it myself. It's because the step before just CREATE-d the table. DML statements in the same batch will not be able to see it so you need to switch batches, which you can do with the EXEC(string) command.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 21, 2008 at 12:30 am
Thanks Barry,
But I don't get the explanation. The statement that immediately precedes the insert is a GO i.e. a batch separator. So we are not in the same batch.
What am I missing?
Regards,
David.
May 21, 2008 at 12:36 am
I include an example script here. This is the change script generated when adding NewField between Field1 and Field2 of MyTable. I hope this makes it clearer.
My question is why
[font="Courier New"]
IF EXISTS(SELECT * FROM dbo.MyTable)
EXEC('INSERT INTO dbo.Tmp_MyTable (Field1, Field2)
SELECT Field1, Field2 FROM dbo.MyTable WITH (HOLDLOCK TABLOCKX)')
[/font] instead of
[font="Courier New"]
IF EXISTS(SELECT * FROM dbo.MyTable)
INSERT INTO dbo.Tmp_MyTable (Field1, Field2)
SELECT Field1, Field2 FROM dbo.MyTable WITH (HOLDLOCK TABLOCKX)
[/font]
Regards,
David McKinney.
[font="Courier New"]
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_MyTable
(
Field1 nchar(10) NULL,
NewField nchar(10) NULL,
Field2 nchar(10) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.MyTable)
EXEC('INSERT INTO dbo.Tmp_MyTable (Field1, Field2)
SELECT Field1, Field2 FROM dbo.MyTable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.MyTable
GO
EXECUTE sp_rename N'dbo.Tmp_MyTable', N'MyTable', 'OBJECT'
GO
COMMIT[/font]
May 22, 2008 at 11:23 am
Hmm, good point. I am not really sure why then. Maybe it just always does that after an IF EXISTS statement because it has to do that for so many others (like the DDL-type statements).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply