Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Generate INSERT statements...

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...

Go to Top of Page

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 :)

Go to Top of Page

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}
Go to Top of Page

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-)
Go to Top of Page

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)



Go to Top of Page

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-)
Go to Top of Page

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.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-04-29 : 12:15:03
Cheeky

Damian
Go to Top of Page

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.

Go to Top of Page

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/
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-05-03 : 11:10:21
Most of us have...

Damian
Go to Top of Page

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
Go to Top of Page

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.."
Go to Top of Page

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
Go to Top of Page

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-07 : 21:40:40
Thanks Damian.


Kevin
Go to Top of Page

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,...
Go to Top of Page

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...?
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-30 : 04:39:10
Also refer
http://sqlteam.com/forums/topic.asp?TOPIC_ID=53420

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
    Next Page

- Advertisement -