September 14, 2012 at 9:37 pm
Hi,
I need to iterate through a table and create a new row copying some of the values and ending when the end of the table is reached. Currently I'm changing the table layout that has only 3 image types to a layout that has unlimited types.
*** OLD TABLE LAYOUT ***
Create Table [dbo].[Images]
([county_pagekey] [char](14) NULL,
[image_sla] [varbinary](max) NULL,
[image_tif] [varbinary](max) NULL,
[image_pdf] [varbinary](max) NULL,
[sync_time] [datetime2](0) NULL,
[identity_id] [int] IDENTITY(1,1) NOT NULL,
*** NEW TABLE LAYOUT ***
Create Table [dbo].[Images]
([county_pagekey] [char](14) NULL,
[bin_type] [varchar](10) NULL,
[bin_data] [varbinary](max) NULL, (renamed image_sla to this bin_data)
[image_tif] [varbinary](max) NULL, (once copied, will be dropped)
[image_pdf] [varbinary](max) NULL, (once copied, will be dropped)
[sync_time] [datetime2](0) NULL,
[is_deleted] [bit] NULL,
[identity_id] [int] IDENTITY(1,1) NOT NULL
I renamed the field "image_sla" to the new name "bin_data" and by doing so, I avoid having to copy the sla images as the field "bin_data" only contains sla images. I also added 'SLA' into the new bin_type field. At this point the other 2 fields (image_tif and image_pdf) contains their respective images/file types and once copied as new records, these 2 fields will be dropped.
Now, I need help in crafting a insert routine that iterates this table from row 1 thru the end, while creating new rows and coping the "image_tif" value into the new field "bin_data" and inserting 'TIF' into the "bin_type" field. The field "county_pagekey" will be copied and sync_time will get a getdate timestamp.
Something simple would be good...
Thanks, Stanley
September 14, 2012 at 11:01 pm
In a test environment, give this a try:
with BaseData as (
select
county_pagekey,
image_tif as bin_data,
'TIF' as bin_type,
sysdatetime() as sync_time
from
dbo.Images
union all
select
county_pagekey,
image_pdf as bin_data,
'PDF' as bin_type,
sysdatetime() as sync_time
from
dbo.Images
)
insert into dbo.images(
county_pagekey,
bin_data,
bin_type,
sync_type
)
from
BaseData;
September 15, 2012 at 12:01 am
Hi Lynn,
I have dropped the image_pdf field as there was not any data in it. I modified your code and am getting this error:
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'from'.
The modified code is:
with BaseData as (
select
county_pagekey,
image_tif as bin_data,
'TIF' as bin_type,
sysdatetime() as sync_time
from dbo.Images
)
insert into dbo.images (
county_pagekey, bin_type, bin_data
)
from
BaseData;
Thanks for your interest,
Stanley
September 15, 2012 at 6:56 am
That's because of a bad cut and paste on my part:
with BaseData as (
select
county_pagekey,
image_tif as bin_data,
'TIF' as bin_type,
sysdatetime() as sync_time
from
dbo.Images
)
insert into dbo.images(
county_pagekey,
bin_data,
bin_type,
sync_type
)
select
county_pagekey,
bin_data,
bin_type,
sync_type
from
BaseData;
September 15, 2012 at 1:51 pm
Hi Lynn,
I'm running your script now and it now been 30 minutes since it started. I expect it to take 10-14 hours to complete. I've been refreshing the Images table to see if the row count is increasing. It is NOT increasing as expected, so I'm wondering what is actually happening.
Should the row count be increasing? I'm using sql 2012. I just tried select count(*) from images and its showing the same count as the ssms gui shows.
Any suggestions?
Stanley
September 15, 2012 at 3:50 pm
Hi Lynn,
Disregard my prev message. Looks like its sql server's inability to give accurate feedback in real time. Your process completed with the expected results and once it all got done, ssms reported it correctly. The only way I could see anything happening was watching the .ldf file grow, but couldn't tell exactly what was happening.
I wonder why the sql gods haven't thought of adding a new clause such as "with feedback" so one could watch progress... Just wondering...
Thanks Lynn for the code,
Stanley
September 15, 2012 at 8:50 pm
stanlyn (9/15/2012)
Hi Lynn,Disregard my prev message. Looks like its sql server's inability to give accurate feedback in real time. Your process completed with the expected results and once it all got done, ssms reported it correctly. The only way I could see anything happening was watching the .ldf file grow, but couldn't tell exactly what was happening.
I wonder why the sql gods haven't thought of adding a new clause such as "with feedback" so one could watch progress... Just wondering...
Thanks Lynn for the code,
Stanley
You weren't seeing anything because it was all done as a single batch, one large transaction. Not sure if you had indicated how many records (rows) you were copying or I might have provided the code such that it was done in smaller batches, perhaps 10,000 at a time or something like that.
Glad to hear that it all worked okay.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply