logic for multi row insert - 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

SQL Server 2000 Forums

SQL Server Development (2000)

logic for multi row insert

Author

Topic

alexdcosta
Starting Member

12 Posts

Posted - 2006-11-08 : 17:35:08

Hi,

I have a form created for "Creating proposals" in VB 2005 with SQL db. I need to create new version of a proposal if a proposal is approved so that i can make the changes.
In VB, i put a button on form .......say "NEW VERSION"
On clicking that buttton, a copy of the records & its related child records must be made to corresponding tables automatically.
How can i accomplish this.......? What is the logic ?
I am using SPs to insert & update records in SQL server 2000

Alex

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-08 : 17:48:11

Use an INSERT ... SELECT, something like


INSERT INTO [pubs].[dbo].[authors]([au_id], [au_lname], [au_fname]
    , [phone], [address], [city], [state], [zip], [contract])
SELECT '111-11-1111', au_lname, au_fname, phone, address, city, state, zip, contract
FROM pubs..authors
WHERE au_id = '172-32-1176'
INSERT INTO [pubs].[dbo].[authors]([au_id], [au_lname], [au_fname]
    , [phone], [address], [city], [state], [zip], [contract])
SELECT '222-11-1111', au_lname, au_fname, phone, address, city, state, zip, contract
FROM pubs..authors
WHERE au_id = '172-32-1176'
INSERT INTO [pubs].[dbo].[authors]([au_id], [au_lname], [au_fname]
    , [phone], [address], [city], [state], [zip], [contract])
SELECT '333-11-1111', au_lname, au_fname, phone, address, city, state, zip, contract
FROM pubs..authors
WHERE au_id = '172-32-1176'

Or if you have multiple rows you can combine them in a single INSERT by using UNIONs between the SELECTs


INSERT INTO [pubs].[dbo].[authors]([au_id], [au_lname], [au_fname]
    , [phone], [address], [city], [state], [zip], [contract])
SELECT '111-11-1111', au_lname, au_fname, phone, address, city, state, zip, contract
FROM pubs..authors
WHERE au_id = '172-32-1176'
UNION ALL
SELECT '222-11-1111', au_lname, au_fname, phone, address, city, state, zip, contract
FROM pubs..authors
WHERE au_id = '172-32-1176'
UNION ALL
SELECT '333-11-1111', au_lname, au_fname, phone, address, city, state, zip, contract
FROM pubs..authors
WHERE au_id = '172-32-1176'

alexdcosta
Starting Member

12 Posts

Posted - 2006-11-08 : 18:18:09

If i do a multiple rows insert, & combine them in a single INSERT by using UNIONs between the SELECTs, will the IDENTITY columns in these tables auto increment correctly ?

Alex

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-08 : 23:10:13

Yes - you won't be able to get them using @@identity or scope_identity so you need to run them one at a time if you need them with those functions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources