Database

Re: sql group by and minimum

13-Jan-09 17:26

select * from
(
select ROW_NUMBER() over (partition by col1 order by col2 asc) as rowNumber,*
from your_table
)base
where base.rowNumber=1

Human knowledge belongs to the world

RyanEK13-Jan-09 17:29

RyanEK

13-Jan-09 17:29

If you're using sql2k...

<br />
declare @temp table (col1 int, col2 int, col3 int)<br />
insert into @temp values(1,3,20)<br />
insert into @temp values(2,6,512)<br />
insert into @temp values(1,2,23)<br />
insert into @temp values(4,9,429)<br />
insert into @temp values(3,8,92)<br />
insert into @temp values(4,7,35)<br />
insert into @temp values(2,9,1)<br />
<br />
select t.*<br />
from @temp t<br />
inner join<br />
( select col1, min(col2) as col2<br />
  from @temp <br />
  group by col1<br />
) d on d.col1 = t.col1 and d.col2 = t.col2<br />

Ryan

Stored Procedure for Dynamic Table Creation

Sunset Towers13-Jan-09 10:49

Sunset Towers

13-Jan-09 10:49

I need to create a table dynamically, preferably via stored procedure. I've gotten a bit through what I'm sure I need to do, but now I'm getting lost.

CREATE PROCEDURE CreateNewPersonTable
@tableName NVARCHAR(100),
@ColumnList NVARCHAR(MAX)
AS
Begin
Declare @CreateTableCommand NVARCHAR(MAX)
SET @CreateTableCommand = '
CREATE TABLE [dbo].[' + @tableName + 'SampleTable](
	[RecordID] [bigint] IDENTITY(1,1) NOT NULL,
	[CheckedOutBy] [nvarchar](50) NULL,
	[Completed] [nvarchar](5) NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Suffix] [nvarchar](5) NULL,
	[Address1] [nvarchar](100) NULL,
	[Address2] [nvarchar](100) NULL,
	[City] [nvarchar](50) NULL,
	[State] [nvarchar](50) NULL,
	[ZipCode] [nvarchar](50) NULL,
	[Disposition] [nvarchar](50) NULL' +
	@ColumnList
	+
') ON [PRIMARY] '

exec(@CreateTableCommand)
END
GO

Where the @ColumnList is will be a list that is generated from column names in a CSV file. The column names vary from file to file, then the data is looked up, and updated to the columns that are non-dynamic.

I had planned on making the @ColumnList a comma seperated list of the columns( IE col1,col2,col3, etc.. ) and then make all the columns NVARCHAR(MAX) NULL. The final column would look something like

[O_Col1] [nvarchar](MAX) NULL

Is there a simple way of doing this or would I be better off just doing this from within my code and pass the full value?

Wendelius13-Jan-09 11:06

13-Jan-09 11:06

If the problem is in reformatting the columns, you could use REPLACE function. Something like:

DECLARE @collist varchar(1000)
DECLARE @formatted varchar(1000)

SET @collist = 'col1,col2,col3';
SET @formatted = '[O_' 
                 + REPLACE(@collist, ',', '] [nvarchar](MAX) NULL,[O_') 
                 + '] [nvarchar](MAX) NULL';
PRINT @formatted;

This results into:
[O_col1] [nvarchar](MAX) NULL,[O_col2] [nvarchar](MAX) NULL,[O_col3] [nvarchar](MAX) NULL

However, I'm wondering if instead of creating the table could you use SELECT INTO statement to create and populate the table. It could make the task easier.

The need to optimize rises from a bad design.My articles[^]

Sunset Towers13-Jan-09 11:55

Sunset Towers

13-Jan-09 11:55

I could rename the columns before hand, but the information that is being inserted is being retrieved from a CSV file into a DataTable. I'm not sure how I could pass that table directly into the SQL Server to do the SELECT INTO. Also there is a need to do some pre-processing on each record before it's inserted.

I figured just creating the table upfront would be the simpliest route, even if it's not the quickest route. But I'm open to suggestions. The pre-processing could always be done post insert.

Wendelius13-Jan-09 21:50

13-Jan-09 21:50

I think your solution works fine even if you do the table first and then insert the rows. But to answer your questions:

Sunset Towers wrote:
the information that is being inserted is being retrieved from a CSV file

If the CSV file can be opened by SQL Server (it resides on a disk that SQL Server can see), you could use SELECT INTO ... OPENROWSET combination. In that case you would give reasonable names to the selected columns in the select statement and the table will be created at the same time as it's populated. For more info, see: OPENROWSET[^].

Sunset Towers wrote:
I'm not sure how I could pass that table directly into the SQL Server to do the SELECT INTO

If you mean the table where the data goes, you don't have to, since it's created on-the-fly. If you mean the datatable, there's an article I wrote about this: How to pass multiple records to a Stored Procedure[^].

Sunset Towers wrote:
Also there is a need to do some pre-processing on each record before it's inserted

Perhaps another option could be that you first insert the data to the table and instead of pre-processing, you load the data 'as-is' and modify it in the table after it's populated (especially if the table you create is used just for loading). Don't know if this is possible or reasonable in your case.

Anyhow, you can create the table using stored procedure and then fill it from client side and use procedure to reduce the number of roundtrips if you want.

By the way, did the first reply resolve your table creation problem?

The need to optimize rises from a bad design.My articles[^]

SQL Server 2008 Management Studio Installation

Mycroft Holmes14-Jan-09 0:08

Mycroft Holmes

14-Jan-09 0:08

I have this nasty situation and have to use SSIS - not control, no user to mediate the import, columns that change, additional datefrom/to columns added so the shape was the same just additional sets of from/to. I did the following:

Read the CSV into a table in the UI
Create a CSV string of the column headers making sure they are unique (dupe datefrom fields) by adding # to the field names
Pass the CSV string to a stored proc that drops and recreates the table if the columns are different. All columns are created as varchar(200)
Bulkcopy the table into the SQL table
Use a proc to move the data from the staging table to where it will be used.

Never underestimate the power of human stupidity
RAH

Grapes-R-Fun13-Jan-09 5:57

Grapes-R-Fun

13-Jan-09 5:57

Hi Guys, I have this weird problem... I installed SQL Server 2008 Enterprise on a Windows 2008 (x64) virtual server the other day. The installation wizard did his thing and everything went well, except that when all was done, I realized Management Studio was not there!! I looked in all related directories, checked the installed components, but that darn Management Studio was not installed!

Does anyone know how I can get that installed seperately? Has anyone encountered that problem?

Thanks Smile | :)

Green Grape

Wendelius13-Jan-09 6:22

13-Jan-09 6:22

Haven't installed 2008 enterprise yet, but if it acts like previous versions, you must include the Management Studio during installation. By default it's not installed. Re-run the setup and check if there's an option you can include. Something like Client tools etc.

The need to optimize rises from a bad design.My articles[^]

Grapes-R-Fun13-Jan-09 6:41

Grapes-R-Fun

13-Jan-09 6:41

Thanks Mika- I will give it a try Smile | :)

Hopefully it will work!

Green Grape

Wendelius13-Jan-09 7:26

13-Jan-09 7:26

No problem Smile | :)

The need to optimize rises from a bad design.My articles[^]

Reza Raad13-Jan-09 17:40

13-Jan-09 17:40

I think it may be here on your installation DVD:
x64\Setup\sql_ssms.msi
this is setup of SqlServer Management Studio

Human knowledge belongs to the world

try catch in T-SQL

George_George12-Jan-09 21:59

12-Jan-09 21:59

Hello everyone,

I an new to begin try/end try and begin catch/end catch. Could anyone recommend me some tutorials for a beginner?

My background is, I wrote T-SQL before, but not too much, I have never used begin try/end try and begin catch/end catch.

thanks in advance,
George

Wendelius12-Jan-09 22:16

12-Jan-09 22:16

Have you looked at these:
- http://www.4guysfromrolla.com/webtech/041906-1.shtml[^]
- http://www.codeproject.com/KB/database/ExceptionHandlingSql2005.aspx[^]

The need to optimize rises from a bad design.My articles[^]

George_George13-Jan-09 0:58

13-Jan-09 0:58

Good stuff, thanks Mika!

regards,
George

Hamid_RT15-Jan-09 2:28

Hamid_RT

15-Jan-09 2:28

See Try/Catch Block in SQL Server 2005[^] and T-SQL error handling with Try…Catch blocks[^].

Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )

George_George15-Jan-09 18:29

15-Jan-09 18:29

Good stuff, thanks Hamid!

regards,
George

GETUTCDATE and GETDATE

George_George12-Jan-09 21:14

12-Jan-09 21:14

Hello everyone,

Any ideas what is the differences between GETUTCDATE and GETDATE? And when to apply GETUTCDATE and when to apply GETDATE?

thanks in advance,
George

Reza Raad12-Jan-09 21:44

12-Jan-09 21:44

getutcdate means : Coordinated Universal Time
and return universal time regardless your time zone
but getdate return time based on your time zone:

<br />
SELECT 'SYSDATETIME()      ', SYSDATETIME();<br />
SELECT 'SYSDATETIMEOFFSET()', SYSDATETIMEOFFSET();<br />
SELECT 'SYSUTCDATETIME()   ', SYSUTCDATETIME();<br />
SELECT 'CURRENT_TIMESTAMP  ', CURRENT_TIMESTAMP;<br />
SELECT 'GETDATE()          ', GETDATE();<br />
SELECT 'GETUTCDATE()       ', GETUTCDATE();<br />
/* Returned:<br />
SYSDATETIME()            2007-05-03 18:34:11.9351421<br />
SYSDATETIMEOFFSET()      2007-05-03 18:34:11.9351421 -07:00<br />
SYSUTCDATETIME()         2007-05-04 01:34:11.9351421<br />
CURRENT_TIMESTAMP        2007-05-03 18:34:11.933<br />
GETDATE()                2007-05-03 18:34:11.933<br />
GETUTCDATE()             2007-05-04 01:34:11.933<br />
*/<br />

Human knowledge belongs to the world

George_George13-Jan-09 0:31

13-Jan-09 0:31

Thanks Reza,

Could I understand GETDATE returns machine local time?

regards,
George

Reza Raad13-Jan-09 2:53

13-Jan-09 2:53

George_George wrote:
Could I understand GETDATE returns machine local time?

GetDate() returns current date of your sqlserver machine(means the server where your sqlserver installed)

Human knowledge belongs to the world

George_George14-Jan-09 2:10

14-Jan-09 2:10

Thanks Reza!

regards,
George

Wendelius12-Jan-09 21:45

12-Jan-09 21:45

GETUTCDATE returns current date of the system in UTC time (Greenwich Mean Time) while GETDATE return current time in the timezone where the system is working.

The need to optimize rises from a bad design.My articles[^]

George_George13-Jan-09 0:31