insert PK Ids for each row in another table

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)

insert PK Ids for each row in another table

Author

Topic

keentolearn
Starting Member

21 Posts

Posted - 2009-07-17 : 09:58:24

Hi,
How can I insert/create Primary Key IDs in my Main table for each row in RecordTable?

Tables:
Main (mainID, CreatedBy)
Record (RecordID, ExpID)

I want to insert MainIDs to Main and get the MainIDs created to Record(ExpID)for each entry on the Record table (3800 records).

many thanks

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-07-17 : 10:08:33

I would of though if your saying that you want to have the information of whats inserted into one table also inserted into another table, i'd personally look at an after insert trigger look in books online

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 13:59:59

you can make use of OUTPUT operator available in sql 2005 for this
something like below


DECLARE @INSERTED_RECORDS table
(
mainID  int
)

INSERT INTO Main (CreatedBy)
OUTPUT INSERTED.mainID INTO @INSERTED_RECORDS
SELECT .... your values

INSERT INTO Record (ExpID)
SELECT mainID
FROM @INSERTED_RECORDS

keentolearn
Starting Member

21 Posts

Posted - 2009-07-17 : 14:35:30

Main table already has some MainIDs stored. When a record added to the database a main ID is created. I want to create MainIDs before uploading the data and get those IDs to my Record table that contains the data to be migrated. Hope this makes sense. This is because I need to work from bottom and make my way up as the database is MVC where I need to update the mainlookup tables first.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 14:37:38

quote:
Originally posted by keentolearn

Main table already has some MainIDs stored. When a record added to the database a main ID is created. I want to create MainIDs before uploading the data and get those IDs to my Record table that contains the data to be migrated. Hope this makes sense. This is because I need to work from bottom and make my way up as the database is MVC where I need to update the mainlookup tables first.

then where will these values be available before entry to main table?

keentolearn
Starting Member

21 Posts

Posted - 2009-07-22 : 06:00:12

When I ran the script above, it created only 1 MainID in Main table and inserted a new row in DRALL and copying the MainID in EXPID. But because it created a new row the ExpID is not related with any record in the table. I need a MainID created for each record in DRALL. e.g if there are 4000 records in DRALL, 4000 MainIDs need to be created in Main and MainIDs should be copied into EXPID column for each record.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-22 : 12:44:00

I'm not sure we are understanding you problem. Can you supply us with DDL, DML and expected output? Perhaps this link might help you construct your question:
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

keentolearn
Starting Member

21 Posts

Posted - 2009-07-22 : 14:14:54

Ok, so my tables have already been created:
CREATE TABLE [dbo].[DRAll](
[RecordID] [float] NULL,
[ClientMatterNo] [nvarchar](255) NULL,
[Client ] [nvarchar](255) NULL,
[DealDate] [nvarchar](255) NULL,
[HealthWarning] [nvarchar](255) NULL,
[Sectors ] [nvarchar](255) NULL,
[EnglishWording] [nvarchar](max) NULL,
[PGs] [nvarchar](255) NULL,
[WorkType ] [nvarchar](255) NULL,
[ProceduralType] [nvarchar](255) NULL,
[Offices] [nvarchar](255) NULL,
[Countries] [nvarchar](255) NULL,
[SystemsAdmin] [nvarchar](255) NULL,
[ExpID] [nvarchar](255) NULL

at the moment ExpID column is empty. I want to create MainIDs in Main for each recordID in DRALL and populate the ExpdID with the MainIDs created.

CREATE TABLE [dbo].[Main](
[MainID] [int] IDENTITY(1,1) NOT NULL,
[Created] [datetime] NULL CONSTRAINT [DF_Main_Created] DEFAULT (getdate()),
[CreatedBy] [nvarchar](max) NULL,
[Modifed] [datetime] NULL,
[ModifedBy] [nvarchar](max) NULL,
CONSTRAINT [PK_Main] PRIMARY KEY CLUSTERED
(
[MainID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

keentolearn
Starting Member

21 Posts

Posted - 2009-07-23 : 07:36:20

As a quick solution I have created ExpIDs in DRALL for each records and inserted the ExpIDs into Main table MainID. But I should really be adding new IDs to Main table and insert to DRALL.
Can I create MainIDs by using the identity seed on table design view, e.g. increment from the last MainID and create 4000 IDs? if the last MainID is 1000 for example, MainIDs shoul dbe created from 1001 to 5000?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources