Query help - 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 2008 Forums

Transact-SQL (2008)

Query help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-01-08 : 19:15:01

I want to insert the data into two tables which has primary and foreign relationship without using cursors.

StudentA
ID primary key

SudentAdd
ID Fkey

Ex:



Generaltable
------------

ID   name  add   City
--- ------ ----  ----
1   sam     Add1  NY 
2   chris   Add2  GA
3   Bob     Add3  WA


StudentA
----------
ID    name
---   ----
1     Sam
2     Chris
3     Bob


StudentAdd
----------

ID   ADD  City
---  ---- ----
1    Add1 NY
2    Add2 GA
3    Add3 WA

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-08 : 19:59:26

Since you have the data in the Generaltable, you can do two inserts as shown below. Insert into the StudentA table first and then to the StudentAdd table.

INSERT INTO StudentA (ID, [name])
SELECT Id,[name] FROM GeneralTable;

INSERT INTO StudentAdd (ID, [ADD], City)
SELECT Id,[ADD],City FROM GeneralTable;

There is the possibility that sometimes data may be inserted into the first table and something bad happens (because of data errors or system errors) and data does not get inserted into the second table. You can wrap the two insert statements in a transaction to make sure that either both succeed, or neither does. If you have that concern, take a look at this page: http://msdn.microsoft.com/en-us/library/ms175976.aspx There are couple of examples towards the bottom of the page.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-01-09 : 13:51:32

Thanks sunitabeck

StudentA table ID column is identity column..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-09 : 14:05:39

Set the IDENTITY_INSERT property before executing the insert commands:

SET IDENTITY_INSERT StudentA ON

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-01-09 : 16:50:42

I think i was not clear..Here is the requirement.



declare @TablePrimary table ( 	
	CustomerName varchar(35) NOT NULL,
	Add1 varchar(35)  NULL,
	Add2 varchar(35)  NULL
	 )

insert into @TablePrimary (CustomerName,Add1,Add2) values ('SAM','Troy','MI')
insert into @TablePrimary (CustomerName,Add1,Add2) values ('PAT','NY','NY')
insert into @TablePrimary (CustomerName,Add1,Add2) values ('MAP','MD','DC')

select * from @TablePrimary


declare @Tablecname table ( 
	NewPK int identity(1,1) PRIMARY KEY CLUSTERED, 
	CustomerName varchar(35) NOT NULL
	 )

Insert Into @Tablecname (CustomerName) 
select CustomerName from @TablePrimary


Select	*
from	@Tablecname


declare @Tablecadd table ( 
	NewPK int not null CONSTRAINT fk_pid FOREIGN KEY REFERENCES @Tablecname(NewPK),
	ADD1 varchar(35) NOT NULL,
	Add2 varchar(35)  NULL
	 )


Insert Into @Tablecadd (ADD1,Add2) 
select ADD1,Add2 from @TablePrimary


select * from @Tablecadd

Expected output:


NEWPK   Add1 Add2
-----------------
1	Troy  MI
2	NY    NY
3	MD    DC

Is there a way to do without cursors..I have nearly millions of records.

Thanks for your help in advance.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-09 : 17:09:59

I don't get it

The resultys look just like the original table but now with an identity column

ATLER TABLE TablePrimary ADD [ID] int IDENTITY(1,1)

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-09 : 18:03:33

Since you don't have any unique key in your staging table, I can't think of a simple way to do this other than inserting the data into a staging table and then moving from there to the actual tables. (What I am referring to is the possibility that there can be more than one row with the same CustomerName in your @TablePrimary).

Anyway, given that restriction, here is a way to do it using a staging table. My staging table is #tmp.

declare @TablePrimary table ( 	
	CustomerName varchar(35) NOT NULL,
	Add1 varchar(35)  NULL,
	Add2 varchar(35)  NULL
	 )

insert into @TablePrimary (CustomerName,Add1,Add2) values ('SAM','Troy','MI')
insert into @TablePrimary (CustomerName,Add1,Add2) values ('PAT','NY','NY')
insert into @TablePrimary (CustomerName,Add1,Add2) values ('MAP','MD','DC')

select * from @TablePrimary

SELECT
	*,
	IDENTITY(INT,1,1) AS ID
INTO #tmp
FROM
	@TablePrimary;
	
create table #Tablecname  ( 
	NewPK int identity(1,1) PRIMARY KEY CLUSTERED, 
	CustomerName varchar(35) NOT NULL
);

SET IDENTITY_INSERT #Tablecname ON;

Insert Into #Tablecname (NewPK,CustomerName) 
select ID,CustomerName from #tmp;

CREATE TABLE #Tablecadd  ( 
	NewPK int not null ,
	ADD1 varchar(35) NOT NULL,
	Add2 varchar(35)  NULL
	 )


Insert Into #Tablecadd (NewPK,ADD1,Add2) 
select ID,ADD1,Add2 from #tmp;

SELECT * FROM #Tablecname;
SELECT * FROM #Tablecadd

DROP TABLE #tmp;
DROP TABLE #Tablecname
DROP TABLE #Tablecadd

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-01-10 : 13:19:54

Thanks sunitabeck

Is there is other solution without using the temp table may be CTE.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-10 : 14:10:12

I can't think of a way. Even if you could, with a million rows, using a CTE would be more resource intensive. With the staging table, you could create primary keys and indexes to speed up the operation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources