How copy records from 1 table to another

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 2005 Forums

Transact-SQL (2005)

How copy records from 1 table to another

Author

Topic

robertnzana
Starting Member

42 Posts

Posted - 2008-05-24 : 17:15:18

I have 2 tables.
TABLE 1: AttachmentTypeDefaults (fields: DefaultId (pk), AttachmentType)
TABLE 2: AttachmentType (fields: AttachmentTypeId (pk), CompanyId, AttachmentType)

The @CompanyId variable has to be the IDENTITY of a new record that was just INSERTed into another table just before (in this new SP.)

What I want to do is loop thru the AttachmentTypeDefaults table and INSERT every record into the AttachmentType table and use the @CompanyId for every new record. (AttachmentTypeId will create itself.)

How would I do this?

Sounds easy but I am new.

I would like it to be compatible with SQL Server 2000 also.

I tried a query like this, but I don't think I'm close...
declare @MyNewIdentity int
SET @MyNewIdentity = SCOPE_IDENTITY()

-- Now INSERT default attachments
INSERT INTO AttachmentType(CompanyId, AttachmentType)
VALUES(
SELECT @MyNewIdentity AS CompanyId, AttachmentType
FROM AttachmentTypeDefaults)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-24 : 17:55:10

I'm very confused. Is AttachmentTypeId an identity column? You say it will create itself, so it must be. I don't understand how you want to generate the CompanyId column. You can't use SCOPE_IDENTITY() or @@IDENTITY unless you run those right after INSERT from the other table.

Could you show us sample data to make your issue more clear?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

robertnzana
Starting Member

42 Posts

Posted - 2008-05-24 : 17:59:52

Yes. AttachmentTypeId is the primary key to AttachmentType.

I did not include the INSERT command into the Companies table. The full SP is like this...

ALTER PROCEDURE dbo.InsertCompany
(
@PackageId int,
@CompanyName nvarchar(MAX),
@Address1 nvarchar(MAX),
@Address2 nvarchar(MAX),
@City nvarchar(MAX),
@State nvarchar(MAX),
@Zip nvarchar(MAX),
@ContactPerson nvarchar(MAX),
@ContactPhone nvarchar(MAX),
@ContactPhone2 nvarchar(MAX),
@ContactEmail nvarchar(MAX),
@OpenSun bit, @WorkHrSunFrom nvarchar(MAX), @WorkHrSunTo nvarchar(MAX),
@OpenMon bit, @WorkHrMonFrom nvarchar(MAX), @WorkHrMonTo nvarchar(MAX),
@OpenTue bit, @WorkHrTueFrom nvarchar(MAX), @WorkHrTueTo nvarchar(MAX),
@OpenWed bit, @WorkHrWedFrom nvarchar(MAX), @WorkHrWedTo nvarchar(MAX),
@OpenThu bit, @WorkHrThuFrom nvarchar(MAX), @WorkHrThuTo nvarchar(MAX),
@OpenFri bit, @WorkHrFriFrom nvarchar(MAX), @WorkHrFriTo nvarchar(MAX),
@OpenSat bit, @WorkHrSatFrom nvarchar(MAX), @WorkHrSatTo nvarchar(MAX),
)

AS
BEGIN
DECLARE @MyNewIdentity int

INSERT INTO [Companies]
(
[PackageId],
[CompanyName],
[Address1],
[Address2],
[City],
[State],
[Zip],
[ContactPerson],
[ContactPhone],
[ContactPhone2],
[ContactEmail],
[DateAdded],
[WorkHours],
[OpenSun], [WorkHrSunFrom], [WorkHrSunTo],
[OpenMon], [WorkHrMonFrom], [WorkHrMonTo],
[OpenTue], [WorkHrTueFrom], [WorkHrTueTo],
[OpenWed], [WorkHrWedFrom], [WorkHrWedTo],
[OpenThu], [WorkHrThuFrom], [WorkHrThuTo],
[OpenFri], [WorkHrFriFrom], [WorkHrFriTo],
[OpenSat], [WorkHrSatFrom], [WorkHrSatTo]
)
VALUES
(
@PackageId,
@CompanyName,
@Address1,
@Address2,
@City,
@State,
@Zip,
@ContactPerson,
@ContactPhone,
@ContactPhone2,
@ContactEmail,
GETDATE(),
0,
@OpenSun, @WorkHrSunFrom, @WorkHrSunTo,
@OpenMon, @WorkHrMonFrom, @WorkHrMonTo,
@OpenTue, @WorkHrTueFrom, @WorkHrTueTo,
@OpenWed, @WorkHrWedFrom, @WorkHrWedTo,
@OpenThu, @WorkHrThuFrom, @WorkHrThuTo,
@OpenFri, @WorkHrFriFrom, @WorkHrFriTo,
@OpenSat, @WorkHrSatFrom, @WorkHrSatTo
)

SET @MyNewIdentity = SCOPE_IDENTITY()
-- SET @MyNewIdentity = @@IDENTITY

-- Now INSERT default attachments
INSERT INTO AttachmentType(CompanyId, AttachmentType)
SELECT @MyNewIdentity as CompanyId, AttachmentType FROM AttachmentTypeDefaults

RETURN
END

Currently Visual Studio gives me "Incorrect syntax near ')'", which is not very descriptive!!! :)

Though I am creating this in an SQL Server 2005 enviro, I will be moving the db to SQL Server 2000 so it's important that the SP works here too. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-24 : 18:11:51

You've got an extra comma here (before your AS):
@OpenSat bit, @WorkHrSatFrom nvarchar(MAX), @WorkHrSatTo nvarchar(MAX),
)

The rest looks good.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

robertnzana
Starting Member

42 Posts

Posted - 2008-05-24 : 18:16:31

Thanks - stupid comma!!!!! LOL

I don't know why Visual Studio/SQL Server can't just say 'extra comma you idiot'!!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-24 : 18:47:37

I'm not sure about VS, but Management Studio and Query Analyzer provide an error which you can double-click on and it'll jump to approximately where the error is.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

robertnzana
Starting Member

42 Posts

Posted - 2008-05-24 : 19:37:41

Thanks - and you are the SQL Goddess - ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources