September 8, 2008 at 5:03 am
Hi,
I'm writing a script that moves data from one table to another.
TableSource has lots of columns say ColumnA to ColumnZ.
TableDest has one column say ColumnAtoZ.
Is there any alternative here to writing multiple insert statements that like...
INSERT into TableDest (ColumnAtoZ)
SELECT ColumnA from TableSource
INSERT into TableDest (ColumnAtoZ)
SELECT ColumnB from TableSource
etc
INSERT into TableDest (ColumnAtoZ)
SELECT ColumnZ from TableSource
Or...............
INSERT into TableDest (ColumnAtoZ)
SELECT ColumnA from TableSource
UNION
SELECT ColumnB from TableSource
UNION
etc
UNION
SELECT ColumnZ from TableSource
I understand I could write the UNION script but in reality the production table has about 100 columns.
(And yes, before you ask.....I know, terrible data model that I can't change, sorry!)
Any clever ideas out there?
Any help welcome 🙂
EamonSQL
September 8, 2008 at 6:09 am
You could use a cursor to select the column names and build a dynamic statement. By using this, you could also commit your statement after each row (which keeps your logfile small) It will be something like this: (SQL2005 version)
declare @colname sysname
declare @SQLCmd nvarchar(1000)
declare c_colname cursor for
select name
from sys.columns
where object_id = object_id('tablesource')
order by column_id
open c_colname
fetch next from c_colname into @colname
while @@fetch_status = 0
begin
set @sqlcmd = 'insert into tabledest(colz) select ' + @colname + ' from tablesource'
exec (@SQLCmd)
fetch next from c_colname into @colname
end
close c_colname
deallocate c_colname
(Check this code first, I haven't tried this)
As I mentioned , you could add BEGIN TRANSACTION/COMMIT and add a TRY .. CATCH block
Wilfred
The best things in life are the simple things
September 8, 2008 at 6:39 am
I don't really understand how the 2 statements are equivalent... or more importantly what you need to do. I never had to import a table one column at the time... unless I had to reorganize the data. That's why I don't really understand why you need to do this in that fashion.
September 8, 2008 at 6:43 am
Dear SSC Veteran,
On production that guy wants to avoid UNION and you are executiing it CURSOR
dont u think it will increase network traffic and degrade server performance:D
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 8, 2008 at 7:04 am
Thanks everyone,
the data has already been imported to TableSource which is a staging table and TableDest is the business reporting table. All I'm doing here is seeing if there is an alternative. Yes, a cursor seems obvious but I prefer to use an alternative approach and thought there may be another way out there.
I think I will use the Union method as the number of columns are fixed and won't change as seems to work well for me. Yes, that effectively constitues hard-coding and isn't as elegant as the cursor solution but there you go.
I think the answer could be a CTE solution which I'll have to investigate further myself.
Thanks for the all suggestions,
EamonSQL:)
September 8, 2008 at 7:05 am
On production that guy wants to avoid UNION and you are executiing it CURSOR
dont u think it will increase network traffic and degrade server performance
I'm confused :hehe: UNION is not the same as CURSOR :hehe:
My solution executes a statement (@SQLCmd) several times (depending on the number of columns in SourceTable)
Total networktraffic will be (almost) the same
Wilfred
The best things in life are the simple things
September 9, 2008 at 6:59 pm
How about this?
declare @sql nvarchar(max)
SET @sql = ''
SELECT @sql = @sql + ' SELECT ' + name + ' FROM dbo.Property UNION' + char(13) + char(10)
FROM sys.columns
WHERE object_id = object_id('Property')
SET @sql = left(@Sql, len(@Sql) - 8)
PRINT @sql
-- EXEC sp_ExecuteSQL @sql
Granted it is dynamic sql, but I think it will do what you want.
Gary Johnson
Sr Database Engineer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply