| Author |
Topic |
|
Andraax
Aged Yak Warrior
790 Posts |
 Posted - 2003-01-20 : 07:03:03
|
Hey guys...
I just made a little proc to generate inserts for a given table. Sadly, it contains two cursors... :(
Can any of you guys come up with a way without cursors?
Here's the proc:
create proc generate_inserts @table varchar(20)
--Generate inserts for table @table
AS
declare @cols varchar(1000)
declare @col varchar(50)
set @cols=''
declare colcur
cursor for
select column_name
from information_schema.columns
where table_name=@table
open colcur
fetch next from colcur into @col
while @@fetch_status=0
begin
select @cols = @cols + ', ' + @col
fetch next from colcur into @col
end
close colcur
deallocate colcur
select @cols = substring(@cols, 3, datalength(@cols))
--select @cols
declare @sql varchar(4000)
declare @colname varchar(100),
@coltype varchar(30)
select @sql = 'select replace(''insert ' + @table + ' (' + @cols + ') '
select @sql = @sql + 'values ('''
declare ccur
cursor for
select column_name, data_type
from information_schema.columns
where table_name=@table
open ccur
fetch from ccur into @colname, @coltype
while @@fetch_status=0
begin
if @coltype in ('varchar', 'char', 'datetime')
select @sql=@sql + ''''''
select @sql=@sql + ' + coalesce(convert(varchar, ' + @colname + '), ''null'') + '
if @coltype in ('varchar', 'char', 'datetime')
select @sql=@sql + ''''''
select @sql = @sql + ''', '''
fetch from ccur into @colname, @coltype
end
close ccur
deallocate ccur
select @sql=substring(@sql, 1, datalength(@sql)-3)
select @sql=@sql + ')'', ''''''null'''''', ''null'') from ' + @table
exec (@sql)
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2003-01-20 : 07:22:15
|
Maybe you should consider using a temp table instead of a cursor to hold your columns from information_schema.columns.
Just modify the while loop a little bit...
|
 |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-01-20 : 07:26:58
|
Yeah... Could do that. Well. Performance is not much of an issue here so it doesn't really matter as long as it does it's job :)
|
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-20 : 08:12:36
|
select
@cols = coalesce(@collist + ',','') + column_name
from
information_schema.columns
where
table_name = @table
...
Jay White
{0} |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-29 : 09:13:44
|
How about:
USE NorthWind
DECLARE @TBName sysname, @TBName2 sysname
SELECT @TBName = 'Orders', @TBName2 = 'Orders2'
SELECT SQL FROM (
SELECT 'INSERT INTO ' + @TBName2 + ' ( ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + COLUMN_NAME As SQL, TABLE_NAME, 1 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT ') ' As SQL, TABLE_NAME, 2 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TBName
UNION ALL
SELECT 'SELECT ' + '''' + ' ' + '''' + ' AS ' + COLUMN_NAME As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION = 1
UNION ALL
SELECT ' , ' + '''' + ' ' + '''' + ' AS ' + COLUMN_NAME As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBName
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT ' FROM ' + TABLE_NAME As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TBName
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order
Brett
8-) |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-04-29 : 09:48:32
|
Hey Brett... 3 months later... :)
That doesn't really give the same result... Mine gives the actual values in the table:
insert into bla (bla, bla, bla) values (1, 2, 3)
insert into bla (bla, bla, bla) values (5, 6, 7)
|
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-29 : 11:03:21
|
Ok,
But where are you getting the data from?
Brett
8-) |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-04-29 : 11:59:00
|
Or, you could just use my free tool SQLDataScripter. Available at [url] www.clrsoft.com [/url]
-Chad
http://www.clrsoft.com
Software built for the Common Language Runtime. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-04-29 : 12:15:03
|
Cheeky 
Damian |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-04-30 : 02:35:30
|
Brett, it's made for easy moving of static data, scripting insert statements for use in installation scripts etc.
|
|
|
|
gvphubli
Yak Posting Veteran
54 Posts |
Posted - 2003-05-03 : 01:55:52
|
As a DBA/Developer, I have developed my own applications like Data Scripter, SQL-Compare Pro. If anyone need these let me know. I can give it to u guys @ now price and obligation... 
- = Cracky DBA = -
http://www.geocities.com/gvphubli/ |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-05-03 : 11:10:21
|
Most of us have...
Damian |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-07 : 21:08:44
|
Hello,
I'm confused, probably because I'm new.
Are you trying to create a routine that will insert rows into ANY table, using the same script ?
Kevin
|
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-08-07 : 21:31:13
|
Kevin,
Yes they are.
As Damian has pointed, most of us have written something very similar..
At first I thought this will come in handy, then it suddenly dawned on me that BCP does this faster and better.
DavidM
"SQL-3 is an abomination.." |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-08-07 : 21:32:13
|
quote:
Are you trying to create a routine that will insert rows into ANY table, using the same script ?
Not quite.
All these tools will read the data from a table, and create INSERT statements for each row to insert into a similar table.
It's really useful when moving a database from development to live. Generally what happens is you have a whole bunch of rubbish data that you don't want to copy, plus a handful of configuration type tables that you do want in your live system. So, you can generate insert statements for all the good data, then create a blank database on your live server, then run the insert statement scripts.
Now you have a clean database, but with all your good configuration data in place.
Damian
EDIT : Sniped... quoting for context |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-07 : 21:40:40
|
Thanks Damian.
Kevin |
|
|
|
samcneal
Starting Member
3 Posts |
Posted - 2004-12-31 : 09:01:37
|
I was reading you guys responses regarding the insert script.
I'm trying to write an insert script to insert in an existing table. For instance, I want to insert a row for recordid if column1 does not exists. If column1 does not exists for recordid then insert row with all the same data with one exception - add value for column1.
Do you guys understand what I am trying to do?
Sonya A. McNeal,
MCDBA, MCSE, MCT,... |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-12-31 : 09:16:35
|
"If column1 does not exists for recordid then insert row with all the same data with one exception"...what same data?
can you give examples (made-up) data in table1 and table2....and include DDL info for both tables...? |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-01-02 : 18:50:42
|
insert t1(col1) select value from t2 where col1 is null -- ???
rockmoose |
|
|
|
tommyonline
Starting Member
3 Posts |
Posted - 2006-01-28 : 10:20:18
|
| Try www.sqlscripter.com to generate your insert, update or delete data scripts. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Next Page
|
Generate INSERT statements...