SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Generate INSERT statements...
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Andraax
Aged Yak Warrior

Sweden
790 Posts

Posted - 01/20/2003 :  07:03:03  Show Profile  Reply with Quote
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

Slovenia
307 Posts

Posted - 01/20/2003 :  07:22:15  Show Profile  Reply with Quote
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

Sweden
790 Posts

Posted - 01/20/2003 :  07:26:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/20/2003 :  08:12:36  Show Profile  Reply with Quote

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 - 04/29/2003 :  09:13:44  Show Profile  Reply with Quote
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

Sweden
790 Posts

Posted - 04/29/2003 :  09:48:32  Show Profile  Reply with Quote
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 - 04/29/2003 :  11:03:21  Show Profile  Reply with Quote
Ok,

But where are you getting the data from?



Brett

8-)
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/29/2003 :  11:59:00  Show Profile  Visit chadmat's Homepage  Reply with Quote
Or, you could just use my free tool SQLDataScripter. Available at www.clrsoft.com " target="_blank"> www.clrsoft.com

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 04/29/2003 :  12:15:03  Show Profile  Visit Merkin's Homepage  Reply with Quote
Cheeky

Damian
Go to Top of Page

Andraax
Aged Yak Warrior

Sweden
790 Posts

Posted - 04/30/2003 :  02:35:30  Show Profile  Reply with Quote
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

India
54 Posts

Posted - 05/03/2003 :  01:55:52  Show Profile  Visit gvphubli's Homepage  Reply with Quote
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!

Australia
4970 Posts

Posted - 05/03/2003 :  11:10:21  Show Profile  Visit Merkin's Homepage  Reply with Quote
Most of us have...

Damian
Go to Top of Page

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 08/07/2003 :  21:08:44  Show Profile  Reply with Quote
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

Australia
1591 Posts

Posted - 08/07/2003 :  21:31:13  Show Profile  Reply with Quote
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!

Australia
4970 Posts

Posted - 08/07/2003 :  21:32:13  Show Profile  Visit Merkin's Homepage  Reply with Quote
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

Edited by - Merkin on 08/07/2003 21:33:17
Go to Top of Page

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 08/07/2003 :  21:40:40  Show Profile  Reply with Quote
Thanks Damian.


Kevin
Go to Top of Page

samcneal
Starting Member

USA
3 Posts

Posted - 12/31/2004 :  09:01:37  Show Profile  Send samcneal an AOL message  Reply with Quote
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
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 12/31/2004 :  09:16:35  Show Profile  Reply with Quote
"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

Sweden
3279 Posts

Posted - 01/02/2005 :  18:50:42  Show Profile  Reply with Quote
insert t1(col1) select value from t2 where col1 is null -- ???

rockmoose
Go to Top of Page

tommyonline
Starting Member

Germany
3 Posts

Posted - 01/28/2006 :  10:20:18  Show Profile  Visit tommyonline's Homepage  Reply with Quote
Try www.sqlscripter.com to generate your insert, update or delete data scripts.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 01/30/2006 :  04:39:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also refer
http://sqlteam.com/forums/topic.asp?TOPIC_ID=53420

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000