Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Store Procedure to create Insert statement from exisiting records


Store Procedure to create Insert statement from exisiting records

Author
Message
Bharat Panthee
Bharat Panthee
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 162
Comments posted to this topic are about the item Store Procedure to create Insert statement from exisiting records
daveyhodge
daveyhodge
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 26
It doesn't appear to work with non dbo schema tables. My attempts to fix it on my own have so far been fruitless.

Can anybody else shed some light on this?

Thanks
Bharat Panthee
Bharat Panthee
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 162
You can add source and destination table's schema as parameter of SP and use there in code.
tommyh
tommyh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1560 Visits: 2000
Okay that is one horribly slow way of doing that. Now i dont have a script for doing this myself so creating one from scratch i have dropped some stuff that should be there. Like handling of different datatypes. Possibly you would want to exclude some columns (like identity). But this wouldnt effect performance (or would make it faster... less columns :-)).

But i came up with this.
declare @table varchar(128)
select @table = 'YourTableHere'

declare @Columns table (column_id integer primary key, name varchar(128), type varchar(128))
create table #result (RowNr integer primary key, str varchar(max))

insert into @Columns (column_id, name, type)
select ordinal_position, Column_name, data_type from information_schema.columns where table_name = @table

--In order to be able to sort the data exactly the same way every time we get the PK so we can sort on that
declare @pk varchar(max)
select @pk = (select ', ' + c.name
from sys.key_constraints as k
join sys.tables as t on t.object_id = k.parent_object_id
join sys.index_columns as ic on ic.object_id = t.object_id and ic.index_id = k.unique_index_id
join sys.columns as c on c.object_id = t.object_id and c.column_id = ic.column_id
where t.name = @table and k.type = 'PK'
order by ic.key_ordinal
for xml path(''), TYPE).value('.', 'varchar(max)')

select @pk = SubString(@pk, 3, Len(@pk))

--Build the start of the insert string
declare @insertstring varchar(max)
select @insertstring = (select name + ', ' from @Columns order by column_id for xml path (''), TYPE).value('.', 'varchar(max)')
select @insertstring = 'insert into ' + @table + ' (' + SubString(@insertstring, 1, Len(@insertstring) - 2) + ') values ('

--Okay time to build the result one column at a time
declare @i integer
declare @sql varchar(max)
set @i = 0
while exists (select * from @Columns where column_id > @i)
begin
set @i = @i + 1

--First time we have to insert instead of update
if @i = 1
begin
select @sql = ';with cte as (select row_number() over (order by ' + @pk + ') RowNr, Convert(varchar(max), ' + name + ') str from ' + @table + ') ' +
'insert into #Result (RowNr, str) select RowNr, str from cte'
from @Columns where column_id = @i
exec (@sql)
end

--Add the next column to the result
if @i > 1
begin
select @sql = ';with cte as (select row_number() over (order by ' + @pk + ') RowNr, Convert(varchar(max), ' + name + case when type = 'datetime' then ', 121' else '' end + ') str from ' + @table + ') ' +
'update r set str = r.str + '', '' + IsNull('''''''' + cte.str + '''''''', ''NULL'') from #result r join cte on cte.RowNr = r.RowNr'
from @Columns where column_id = @i
exec (@sql)
end
end

select RowNr, @insertstring + str + ')' from #result

drop table #result



Have to excuse the poor formating. Now i did do a WHILE instead of a cursor. Either one i think would be okay in this circumstance since the nr of columns are so few.

The resulting insert command between the posted procedure and the above code are almost identical (differs a bit in formating).

The HUGE difference is time. I ran both against a table with about 5000 rows (so a small one) with about 35 columns. And the procedure takes about 6min to finish and the above code about 7s (with result returned to the client).

I almost guarantee that there are even faster solutions (because mine are normally not among the fastest... but fast enough for me :-)). So i eagerly await that :-D

/T
pwnies
pwnies
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 15
I would love to use it, but I'm getting the following error messages:

Msg 16916, Level 16, State 1, Procedure GenerateInsertStatement, Line 70
A cursor with the name 'rec_col' does not exist.
Msg 16916, Level 16, State 1, Procedure GenerateInsertStatement, Line 72
A cursor with the name 'rec_col' does not exist.
Msg 16916, Level 16, State 1, Procedure GenerateInsertStatement, Line 126
A cursor with the name 'rec_col' does not exist.
Msg 16916, Level 16, State 1, Procedure GenerateInsertStatement, Line 127
A cursor with the name 'rec_col' does not exist.

I see where the cursor is being created, but not sure why it's not working.
rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 2299
I use SSMS Tools Pack for this.

http://www.ssmstoolspack.com/
Bharat Panthee
Bharat Panthee
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 162
Very good suggestion, thank you :-)
fahey.jonathan
fahey.jonathan
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 395
I like the script. I would like to see the datetime fields formatted using format 121 so that the second and millisecond precision is retained.
jay_nagda
jay_nagda
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 2
I keep getting below error for 4-5 tables I tried to generate the insert statements

Msg 245, Level 16, State 1, Procedure GenerateInsertStatement, Line 72
Conversion failed when converting the varchar value 'WXPAY0000001578' to data type int.
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8700 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search