Insert multiple Columns - 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

General SQL Server Forums

New to SQL Server Programming

Insert multiple Columns

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-01-11 : 13:29:56

Hi Group:

I need some help inserting multiple columns into a new Table. First I need to select a group of Distinct Application_Name from another Table and then all the information related with those distinct applications from all the other columns.
Enclosed is my query...but is not working and is giving me errors.
I need help


Insert into dbo.DR_Applications ([Application_Name]),([Application_Description]),([Email_Type]),([Submitted_by]), ([Submitted_Datetime])
SELECT ( DISTINCT Application_Name, Application_Description),Email_Type, Submitted_by, Submitted_Datetime 
From dbo.DR_Approver_Email_Accounts

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-11 : 13:32:11

You can't use DISTINCT like that. Do you mean you want to insert multiple rows and not multiple columns?

Please show us a data example to make your issue clear as we aren't familiar with your environment.

Do you know how to use a GROUP BY?

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

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 13:32:52

quote:
Originally posted by osirisa

Hi Group:

I need some help inserting multiple columns into a new Table. First I need to select a group of Distinct Application_Name from another Table and then all the information related with those distinct applications from all the other columns.
Enclosed is my query...but is not working and is giving me errors.
I need help

Insert into dbo.DR_Applications ([Application_Name]),([Application_Description]),([Email_Type]),([Submitted_by]), ([Submitted_Datetime])
SELECT DISTINCT Application_Name, Application_Description ,Email_Type, Submitted_by, Submitted_Datetime
From dbo.DR_Approver_Email_Accounts

What is the Error?

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-11 : 13:35:11

You could try this, but there is no guarantee the data derives from same record.

Insert into dbo.DR_Applications ([Application_Name]),([Application_Description]),([Email_Type]),([Submitted_by]), ([Submitted_Datetime])
SELECT Application_Name, Application_Description), MAX(Email_Type), MAX(Submitted_by), max(Submitted_Datetime )
From dbo.DR_Approver_Email_Accounts
GROUP BY Application_Name, Application_Description

If you are using SQL Server 2005, you can use ROW_NUMBER() function, which if you can't figurae out by using Books Online, Visakh16 will show you shortly.

E 12°55'05.63"
N 56°04'39.26"

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-01-11 : 13:39:21

Thank You All I will try the group by

Thanks again

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-01-11 : 13:50:40

New Table - dbo.DR_Applications
Reference Table = dbo.DR_Approver_Email_Accounts

Application_Name, Application_Description, Email_Type, Submitted_by, Submitted_Datetime
CONTROLS ------- VALVE CONTROLS ----------- 3-------- JOHN SMITH--- 01/01/2009
CONTROLS ------- VALVE CONTROLS ----------- 3---------JOHN SMITH --01/02/2009
CIS --------------INFORMATION SYS----------- 4---------LISA WILSON-----04/05/2008

ETC ..ETC....

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-01-11 : 13:53:43

ERROR MESSAGE: Incorrect syntax near ','. Incorrect syntax near ')'

INSERT INTO dbo.DR_Applications
([Application_Name]), ([Application_Description]), ([Email_Type]), ([Submitted_by]), ([Submitted_Datetime])
SELECT Application_Name, Application_Description), MAX(Email_Type), MAX(Submitted_by), max(Submitted_Datetime)
FROM dbo.DR_Approver_Email_Accounts
GROUP BY Application_Name, Application_Description

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 14:10:56

quote:
Originally posted by osirisa

ERROR MESSAGE: Incorrect syntax near ','. Incorrect syntax near ')'

INSERT INTO dbo.DR_Applications
([Application_Name]), ([Application_Description]), ([Email_Type]), ([Submitted_by]), ([Submitted_Datetime])
SELECT Application_Name, Application_Description ,MAX(Email_Type), MAX(Submitted_by), max(Submitted_Datetime)
FROM dbo.DR_Approver_Email_Accounts
GROUP BY Application_Name, Application_Description

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-01-11 : 14:42:31

Hi Sodeep, I don't understand your last post...
Is that a correction? or did you try to make a comment
?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 14:44:47

Correction.I removed ( from Application_Description

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-01-11 : 15:05:05

I still getting the same error...thanks sodeep..

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 15:14:17

quote:
Originally posted by sodeep

quote:
Originally posted by osirisa

ERROR MESSAGE: Incorrect syntax near ','. Incorrect syntax near ')'

INSERT INTO dbo.DR_Applications
([Application_Name]), ([Application_Description]), ([Email_Type]), ([Submitted_by]), ([Submitted_Datetime])
SELECT Application_Name, Application_Description ,MAX(Email_Type)as [Email_Type], MAX(Submitted_by)as [Submitted_by] , max(Submitted_Datetime)as [Submitted_Datetime]
FROM dbo.DR_Approver_Email_Accounts
GROUP BY Application_Name, Application_Description

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-11 : 16:48:54

That solution will give inaccurate results if the MAX values are in different rows. You need to use ROW_NUMBER() function or a derived table. See Peso's post, I'm duplicating it as it is very important to know that this is not going to be accurate unless the max values are in the same exact row, which is very unlikely.

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

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-11 : 23:57:27

Your INSERT part should look like this

INSERT INTO dbo.DR_Applications
([Application_Name], [Application_Description], [Email_Type], [Submitted_by], [Submitted_Datetime])
SELECT ...

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-12 : 03:36:55

Or, if you are using SQL Server 2005

INSERT	dbo.DR_Applications
	(
		Application_Name,
		Application_Description,
		Email_Type,
		Submitted_by,
		Submitted_Datetime
	)
SELECT	
FROM	(
		SELECT	Application_Name,
			Application_Description,
			Email_Type,
			Submitted_by,
			Submitted_Datetime,
			ROW_NUMBER() OVER (PARTITION BY Application_Name, Application_Description ORDER BY Submitted_Datetime DESC) AS recID
		FROM	dbo.DR_Approver_Email_Accounts
	) AS d
WHERE	recID = 1

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources