Generate Update and Insert statements

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

Development Tools

ASP.NET

Generate Update and Insert statements

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-11 : 10:37:44

Anand Soni writes "Hi,

I want a stored procedure that generates Insert and update statements of a table passed as a parameter to it.

It should work something like if I passed Employee Information it should give me output for update scripts for all the records into it for Exampls
Update Employee SET
Field1 = <<Field1Value>>,
Field2 = <<Field2Value>>,
Field3 = <<Field3Value>>,
..............
..............
WHERE PrimaryKeyColumn = <<PrimaryKeyColumnValue>>

And insert statemen something like
INSERT INTO Employee(<<Field1>>, <<Field2>>,.....)
value(<<Field1Value, Field2Value....)

Thanks in Advance.
Anand"

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-11-11 : 12:10:13

Using a table called Broker_Address.....

UPDATE example:

CREATE PROCEDURE usp_BrokerAddress_upt
@BrokerAddress int,
@BrokerCode int,
@Name varchar(100),
@Addr1 varchar(100),
@Addr2 varchar(100),
@Addr3 varchar(100),
@PostCode varchar(30),
@City varchar(100),
@CountryCode int,
@Telephone varchar(30),
@Fax varchar(30),
@Email varchar(50),
@PostalAddr bit,
@CourierAddr bit,
@LoginName varchar(20)
AS
-- ** Used within frm_BrokerAddress **
UPDATE Broker_Address
SET
fs_brokerName = @Name,
fs_Add1 = @Addr1,
fs_Add2 = @Addr2,
fs_Add3 = @Addr3,
fs_postCode = @postCode,
fs_City = @City,
fa_countryCode = @CountryCode,
fs_telephone = @Telephone,
fs_Fax = @Fax,
fs_Email = @Email,
fi_postalAddress = @PostalAddr,
fi_courierAddress = @CourierAddr,
fs_loginname = @LoginName,
lastModifiedDate = current_timestamp

WHERE Broker_Address.fa_brokerAddress = @BrokerAddress

INSERT example:

CREATE PROCEDURE usp_brokerAddress_ins
@BrokerAddress int,
@BrokerCode int,
@Name varchar(100),
@Addr1 varchar(100),
@Addr2 varchar(100),
@Addr3 varchar(100),
@PostCode varchar(30),
@City varchar(100),
@CountryCode int,
@Telephone varchar(30),
@Fax varchar(30),
@Email varchar(50),
@PostalAddr bit,
@CourierAddr bit,
@LoginName varchar(20)
AS

INSERT into Broker_Address
(
fl_brokerCode,
fs_brokerName,
fs_Add1,
fs_Add2,
fs_Add3,
fs_postCode,
fs_City,
fa_countryCode,
fs_Telephone,
fs_Fax,
fs_Email,
fi_postalAddress,
fi_courierAddress,
lastModifiedDate,
fs_LoginName
)
VALUES (
@BrokerCode,
@Name,
@Addr1,
@Addr2,
@Addr3,
@PostCode,
@City,
@CountryCode,
@Telephone,
@Fax,
@Email,
@PostalAddr,
@CourierAddr,
current_timestamp,
@LoginName
)
select @@identity

===========
Paul

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-11 : 13:01:01

quote:

Using a table called Broker_Address.....

I could be wrong, but the think the poster is looking for a proc to automate the creation of other procs ...

Jay White
{0}

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-11 : 17:20:29

Take a look at Proc Blaser from www.lockwoodtech.com
It will do all sorts of code generation for you.

Damian

chadmat
The Chadinator

1974 Posts

Posted - 2003-04-23 : 00:39:50

Try the free utility at [url]http://www.clrsoft.com[/url]

It's called SQLDataScripter, and should do what you want (At least the insert portion).

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources