Generate INSERT statements... - SQL Server Forums

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

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

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