Inserting multiple rows - 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

Transact-SQL (2000)

Inserting multiple rows

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-15 : 15:57:01

I have 2 tables:

TableABC with columns (EID, Year, EName, H1)
TableXYZ with columns(EID, Year, EName, H1,C1,C2,C3)

I need to insert multiple rows of records from tableABC into tableXYZ. C1,C2 and C3 should have values defined by me. C1, C2 and C3 are declared as 'NOT NULL'. Is there a way to insert records from tableABC into tableXYZ with values for C1,C2 and C3 defined by me?

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 16:17:59

Are C1, C2 and C3 the same all the time, can they changed based on the data being entered, why not have one table?

Not that I want to encourage this design, but you could use a TRIGGER for this.

Brett

8-)

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-15 : 16:34:50

TableXYZ is actaully a history table that keeps track of chnages in tableABC.
C1, C2, and C3 are 3 column for recording the person who changed it, timestamp and the type of change.

Any suggestions?

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 16:42:54

This will float your boat:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Company_R__Compa__1A9EF37A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Company_Return_List] DROP CONSTRAINT FK__Company_R__Compa__1A9EF37A
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__WH_Proof___Compa__3DE82FB7]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WH_Proof_Mapping] DROP CONSTRAINT FK__WH_Proof___Compa__3DE82FB7
GO

/****** Object:  Trigger dbo.Company_UpdTr    Script Date: 04/15/2003 4:41:57 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company_UpdTr]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[Company_UpdTr]
GO

/****** Object:  Table [dbo].[Company]    Script Date: 04/15/2003 4:41:57 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company]
GO

/****** Object:  Table [dbo].[Company_H]    Script Date: 04/15/2003 4:41:57 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company_H]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company_H]
GO

/****** Object:  Table [dbo].[Company]    Script Date: 04/15/2003 4:42:02 PM ******/
CREATE TABLE [dbo].[Company] (
	[Company_Name] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Active_Ind] [bit] NULL ,
	[Psft_Company_Id] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FEIN] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Has_NonQual_Taxes] [bit] NULL ,
	[Has_Qual_Taxes] [bit] NULL ,
	[Created_By] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Created_Ts] [datetime] NOT NULL ,
	[Updated_By] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Updated_Ts] [datetime] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Company_H]    Script Date: 04/15/2003 4:42:03 PM ******/
CREATE TABLE [dbo].[Company_H] (
	[HIST_ADD_TS] [datetime] NOT NULL ,
	[HIST_ADD_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[HIST_ADD_BY] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[HIST_ADD_SYSUSER_BY] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Company_Name] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Active_Ind] [bit] NULL ,
	[Psft_Company_Id] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FEIN] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Has_NonQual_Taxes] [bit] NULL ,
	[Has_Qual_Taxes] [bit] NULL ,
	[Created_By] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Created_Ts] [datetime] NOT NULL ,
	[Updated_By] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Updated_Ts] [datetime] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[Company_Name]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Company] WITH NOCHECK ADD 
	CONSTRAINT [DF__Company__Created__619B8048] DEFAULT (getdate()) FOR [Created_Ts]
GO

ALTER TABLE [dbo].[Company_H] WITH NOCHECK ADD 
	CONSTRAINT [DF__Company_H__HIST___59C55456] DEFAULT (getdate()) FOR [HIST_ADD_TS]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Trigger dbo.Company_UpdTr    Script Date: 04/15/2003 4:42:03 PM ******/

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE 
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
	BEGIN

		Insert Into Company_H (
		       HIST_ADD_TYPE
		      ,HIST_ADD_BY
		      ,HIST_ADD_SYSUSER_BY
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts
		)
		Select
		       'U'
		      ,(Select Inserted.Updated_By from Inserted 
			Where Deleted.Company_Name	= Inserted.Company_Name)
		      ,user
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts	  
		From  Deleted	   
	END


If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

	BEGIN

		Insert Into Company_H (
		       HIST_ADD_TYPE
		      ,HIST_ADD_BY
		      ,HIST_ADD_SYSUSER_BY
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts
		)
		Select
		       'D'
		      ,user
		      ,user
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts	  
		From  Deleted	
	END


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Brett

8-)

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-16 : 12:01:54

Thank you Brett for your code.

I inserted a record into company table using...

insert into company
(Company_Name,Active_Ind,Psft_Company_Id,FEIN,Has_NonQual_Taxes,Has_Qual_Taxes,Created_By,Updated_By) values ('Comp1',1,'23e','1232444',1,1,'psahu','vwest')

I have another problem when I am updating Company table. When I try to update ompany table using

update company set company_name = 'comp2'

I get the Error message:

Cannot insert the value NULL into column 'HIST_ADD_BY', table 'tempdb.dbo.Company_H'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I am aware that I am not giving the Hist_Add_by value, but I am not sure how to give it either.

Your help will be appreciated.

PKS

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-16 : 12:14:44

The problem is

,(Select Inserted.Updated_By from Inserted
Where Deleted.Company_Name = Inserted.Company_Name)

This will only work if you are not updating the company name and also if it is the single field primary key (and so should not be updated).
If it is not a simple primary key you will have to find some other method of recording the user as this would cause the trigger to fail.
you could

= coalesce((Select Inserted.Updated_By from Inserted
Where Deleted.Company_Name = Inserted.Company_Name), 'unknown')
to allow updates to it as the primary key (ugh!).

to stop it failing.
Afterthought - only one user so
= (Select top 1 Inserted.Updated_By from Inserted)

If you are doing this you may as well use a jopin rather than a subquery.

also I prefer to distinguish types by
if exists (select * from inserted) and exists (select * from deleted)
begin
update code
end
else if exists (select * from inserted)
begin
insert code
end
else if exists (select * from deleted)
begin
delete code
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 04/16/2003 12:16:18

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-16 : 14:41:41

We have it fail because we wanted to force the JAVA developers to make sure the pass the user who is performing the update. Here's a sample of the Company sproc. It allows, for Select, updates and inserts. Also, on the select, we pass a business rule for each column so the java developer knows what properties to set for each:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[adm_Company_sp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[adm_Company_sp]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE adm_Company_sp
		  @Request_Type		char(1)
		, @Company_Name		char(30)	= null -- Nullabe? no
		, @Active_Ind		char(1) 	= null --	   no
		, @Psft_Company_Id	char(3) 	= null --	   yes
		, @FEIN			char(10) 	= null --	   yes
		, @Has_NonQual_Taxes	Char(1)		= null --	   yes
		, @Has_Qual_Taxes	Char(1)		= null --	   yes
		, @User_Id		char(8)		= null --	   no
AS 
--
--                	Enterprise Solutions
--
--           File:      \\njros1d151\tax\procedures
--           Date:      10-21-2002
--         Author:      Brett Kaiser
--         Server:      
--       Database:      
--          Login:      
--    Description:      This Procedure Administers the Company Table
--
--
--                      The stream will do the following:
--
--				1. Determine the request of the call (Select, Update, Insert)
--				2. For a Slect request, return all rows from the table 
--				   with the attibutes of how to display each field.
--				3. For an update request, perform the update and indicate the success or failure
--				4.  For an Insert, perform the insert and indicate the success or failure

--
--    Tables Used:	Company
--
-- Tables Created:      None
--
--
--  Row Estimates:
--  		name                 		rows        reserved           data               index_size         unused
--              -------------------- 		----------- ------------------ ------------------ ------------------ ------------------
--		Company				42          24 KB	       8 KB		  16 KB		     0 KB


--
-- sp_spaceused Company
-- sp_help Company
-- Select Distinct Company_Type from  Company

--Change Log
--
-- UserId   	Date        	Description
-- -----------  --------------  ------------------------------------------------------------------------------------------
-- x002548  	10-21-2002 	1. Initial release
--
--
--

Declare @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int

Declare	  @WS_Active_Ind	bit
	, @WS_Has_NonQual_Taxes	bit
	, @WS_Has_Qual_Taxes	bit

BEGIN TRAN

	If @Request_Type Not IN ('S','I','U')
		BEGIN	
			Select @Error_Loc = 1
			Select @Error_Message =  'Request Type must be S, I or U.  Current Value is: "' + IsNull(@Request_Type,'') + '"'
			Select @Error_Type = 50002
			GOTO adm_Company_sp_Error
		END

	If	@Request_Type <> 'S'
	 And    @User_Id In (' ', '', 'null', null)
		BEGIN	
			Select @Error_Loc = 2
			Select @Error_Message =  'User If may not be null.  Current Value is: "' + IsNull(@User_Id,'') + '"'
			Select @Error_Type = 50002
			GOTO adm_Company_sp_Error
		END

	If	 @Request_Type <> 'S'
	 And    (@Company_Name   in (' ', '', 'null', null) 
	  Or 	 @Active_Ind 	 in (' ', '', 'null', null))
		BEGIN	
			Select @Error_Loc = 3
			Select @Error_Message =  'Company_Name or Active Ind must not be null.  Values are '
						+ ' Name: "' + IsNull(@Company_Name,'') + '"'
						+ ', Ind: "' + IsNull(@Active_Ind,'') + '"'
			Select @Error_Type = 50002
			GOTO adm_Company_sp_Error
		END

	Select	  @Psft_Company_ID   = Case When @Psft_Company_ID 	In ('', ' ', 'null', Null) 
					    Then Null Else @Psft_Company_ID 	End
		, @FEIN 	     = Case When @FEIN    		In ('', ' ', 'null', Null) 
					    Then Null Else @FEIN    		End
		, @Has_NonQual_Taxes = Case When @Has_NonQual_Taxes     In ('', ' ', 'null', Null) 
					    Then Null Else @Has_NonQual_Taxes	End
		, @Has_Qual_Taxes    = Case When @Has_Qual_Taxes	In ('', ' ', 'null', Null) 
					    Then Null Else @Has_Qual_Taxes	End	  
	 
	Select @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @error_out <> 0
		BEGIN
			Select @Error_Loc = 4
			Select @Error_Type = 50001
			GOTO adm_Company_sp_Error
		END

	If @Result_Count = 0
		BEGIN	
			Select @Error_Loc = 4
			Select @Error_Message =  'Required Values Not Set'
			Select @Error_Type = 50002
			GOTO adm_Company_sp_Error
		END

	If	@Request_Type <> 'S'
	 And   (@Active_Ind 		Not In ('Y','N')
	  Or	@Has_NonQual_Taxes 	Not In ('Y','N',Null)
	  Or 	@WS_Has_Qual_Taxes 	Not In ('Y','N',Null))
		BEGIN	
			Select @Error_Loc = 5
			Select @Error_Message =  'Indicators must Y, N or null where appropriate.  Values are '
						+ ' Active: "' + IsNull(@Active_Ind,'') + '"'
						+ ', Has Non Qual: "' + IsNull(@Has_NonQual_Taxes,'') + '"'
						+ ', Has Qual: "' + IsNull(@Has_Qual_Taxes,'') + '"'
			Select @Error_Type = 50002
			GOTO adm_Company_sp_Error
		END

	Select	  @WS_Active_Ind 	 = Case When @Active_Ind 	= 'Y' Then 1 Else 0	End
		, @WS_Has_NonQual_Taxes =  Case When @Has_NonQual_Taxes	= 'Y' Then 1
		       				When @Has_NonQual_Taxes	= 'N' Then 0 Else Null  End
		, @WS_Has_Qual_Taxes 	 = Case When @Has_Qual_Taxes	= 'Y' Then 1
		       				When @Has_Qual_Taxes	= 'N' Then 0 Else Null  End
	 
	Select @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @error_out <> 0
		BEGIN
			Select @Error_Loc = 6
			Select @Error_Type = 50001
			GOTO adm_Company_sp_Error
		END

	If @Result_Count = 0
		BEGIN	
			Select @Error_Loc = 6
			Select @Error_Message =  'Indicator Conversion Did not occur'
			Select @Error_Type = 50002
			GOTO adm_Company_sp_Error
		END

	If @Request_Type = 'S'
		BEGIN
--				  Column Name		Column Attribute
			SELECT	  Company_Name,		'K'
				, Case When Active_Ind = 1 	  Then 'Y' Else 'N' End
				,			'R'
				, Psft_Company_ID,	'N'
				, FEIN,			'N'
				, Case When Has_NonQual_Taxes = 1 Then 'Y'
				       When Has_NonQual_Taxes = 0 Then 'N' Else ' ' End
				,			'N'
				, Case When Has_Qual_Taxes = 1 	  Then 'Y'
				       When Has_Qual_Taxes = 0 	  Then 'N' Else ' ' End
				,			'N'
				, Created_By,		'P'
				, Created_Ts,		'P'
				, Updated_By,		'P'
				, Updated_Ts,		'P'
			  FROM	  Company
			 WHERE	  Company_Name <> 'U'
			Select @Result_Count = @@ROWCOUNT, @error_out = @@error
		
			If @error_out <> 0
				BEGIN
					Select @Error_Loc = 6
					Select @Error_Type = 50001
					GOTO adm_Company_sp_Error
				END
		END

	If @Request_Type = 'U'
		BEGIN
			UPDATE	  Company
			   SET	  Active_Ind		= @WS_Active_Ind 
				, Psft_Company_ID 	= @Psft_Company_ID
				, FEIN			= @FEIN
				, Has_NonQual_Taxes	= @WS_Has_NonQual_Taxes
				, Has_Qual_Taxes	= @WS_Has_Qual_Taxes 
				, Updated_By		= @User_Id
				, Updated_Ts		= GetDate()
			 WHERE	  Company_Name	 	= @Company_Name
			Select @Result_Count = @@ROWCOUNT, @error_out = @@error
		
			If @error_out <> 0
				BEGIN
					Select @Error_Loc = 6
					Select @Error_Type = 50001
					GOTO adm_Company_sp_Error
				END
		
			If @Result_Count = 0	-- Business Logic Error...This one for an expected row but no results
				BEGIN	
					Select @Error_Loc = 6
					Select @Error_Message =  'No Company Rows where updated for ID = "' 
								+ @Company_Name + '"'
					Select @Error_Type = 50002
					GOTO adm_Company_sp_Error
				END
		END

	If @Request_Type = 'I'
		BEGIN
			If Exists(Select 1 From Company Where Company_Name = @Company_Name)
				BEGIN	
					Select @Error_Loc = 7
					Select @Error_Message =  'Can not Insert Duplicate value for Company = "' 
								+ @Company_Name + '"'
					Select @Error_Type = 50003
					GOTO adm_Company_sp_Error
				END
			
			INSERT INTO
				  Company (
			  	  Company_Name
				, Active_Ind
				, Psft_Company_Id
				, FEIN
				, Has_NonQual_Taxes
				, Has_Qual_Taxes
				, Created_By)
			SELECT 

			  	  @Company_Name
				, @WS_Active_Ind
				, @Psft_Company_Id
				, @FEIN
				, @WS_Has_NonQual_Taxes
				, @WS_Has_Qual_Taxes
				, @User_Id

			Select @Result_Count = @@ROWCOUNT, @error_out = @@error
		
			If @error_out <> 0
				BEGIN
					Select @Error_Loc = 7
					Select @Error_Type = 50001
					GOTO adm_Company_sp_Error
				END
		
			If @Result_Count = 0	-- Business Logic Error...This one for an expected row but no results
				BEGIN	
					Select @Error_Loc = 7
					Select @Error_Message =  'No Company Rows where Inserted for Company = "' 
								+ @Company_Name + '"'
					Select @Error_Type = 50002
					GOTO adm_Company_sp_Error
				END
		END

COMMIT TRAN

adm_Company_sp_Exit:

-- Place any house keeping procedures here like...

Set ansi_warnings ON

return

adm_Company_sp_Error:

Rollback TRAN

If @Error_Type = 50001
	BEGIN
		Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))  
					     + ',"' + '  @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
					     + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
					     + ',"' + '  Message: ' + ',"' + RTrim(description)
				  	   From master..sysmessages
				 	  Where error = @error_out)
	END
If @Error_Type In ( 50002, 50003 )

	BEGIN
		Select @Error_Message = '"Location: ' + RTrim(Convert(char(3),@Error_Loc)) + '"' 
			                + ',"' + ' Severity:  UserLevel "' 
				        + ',"' + ' Message: ' + RTrim(@Error_Message) + '"'
	END

RAISERROR @Error_Type @Error_Message

GOTO adm_Company_sp_Exit


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources