Creating temporary table

Hi,

How can I create a temporary table say "Tblabc" with column fields

ShmCoy char(2)
ShmAcno char(10)
ShmName1 varchar(60)
ShmName2 varchar(60)

and fill the table from the data extracted from the statement...

"select logdetail from shractivitylog"

The above query returns single value field the data seperated with a ''

Ex:
BRLight Blue Duck

in this case I should get
ShmCoy = 'BR'
ShmAcno = ''
ShmName1 = 'Light Blue Duck'
ShmName2 = ''

I want to do this job with single SQL query. Is it possible. Pls help.

Herewith I am providing the sample data

BRLight Blue Duck
0234578
BRAqua Duck
0234586
UBAqua Duck

Regards,
OmavHi.

I think that is better using a stored procedure, but you can try with
this:

create table shractivitylog (logdetail varchar(50))
go

insert into shractivitylog values ('BRLight Blue Duck');
insert into shractivitylog values ('0234578');
insert into shractivitylog values ('BRAqua Duck');
insert into shractivitylog values ('0234586');
insert into shractivitylog values ('UBAqua Duck');

select cast(substring(logdetail,
1,
charindex('',logdetail)-1
) as char(2)) as ShmCoy,
cast(substring(logdetail,
charindex('',logdetail)+1,
charindex('',logdetail,charindex('',logdetail)+1 )-(charindex('',logdetail)+1)
) as char(10)) as ShmAcno,
cast(substring(logdetail,
charindex('',logdetail,charindex('',logdetail)+1 )+1,
charindex('',logdetail,charindex('',logdetail,ch arindex('',logdetail)+1)+1)-(charindex('',logdetail,charindex('',logdetail)+ 1)+1)
) as varchar(60)) as ShmName1,
cast(substring(logdetail,
charindex('',logdetail,charindex('',logdetail,ch arindex('',logdetail)+1)+1)+1,
charindex('',logdetail,charindex('',logdetail,ch arindex('',logdetail,charindex('',logdetail)+1)+ 1)+1)-(charindex('',logdetail,charindex('',logdetail,c harindex('',logdetail)+1)+1)+1)
) as varchar(60)) as ShmName2
into ##tblabc
from shractivitylog

select * from ##tblabc

Bye!

kiran@.boardroomlimited.com (Omavlana) wrote in message news:<b14098ab.0310080226.64bf03c6@.posting.google.com>...
> Hi,
> How can I create a temporary table say "Tblabc" with column fields
> ShmCoy char(2)
> ShmAcno char(10)
> ShmName1 varchar(60)
> ShmName2 varchar(60)
> and fill the table from the data extracted from the statement...
> "select logdetail from shractivitylog"
>
> The above query returns single value field the data seperated with a ''
> Ex:
> BRLight Blue Duck
> in this case I should get
> ShmCoy = 'BR'
> ShmAcno = ''
> ShmName1 = 'Light Blue Duck'
> ShmName2 = ''
> I want to do this job with single SQL query. Is it possible. Pls help.
>
> Herewith I am providing the sample data
> BRLight Blue Duck
> 0234578
> BRAqua Duck
> 0234586
> UBAqua Duck
>
> Regards,
> Omav

Creating temporary table

Hi,
How can I create a temporary table say "Tblabc" with column fields
ShmCoy char(2)
ShmAcno char(10)
ShmName1 varchar(60)
ShmName2 varchar(60)
and fill the table from the data extracted from the statement...
"select logdetail from shractivitylog"
The above query returns single value field the data seperated with a '·'
Ex:
BR··Light Blue Duck··
in this case I should get
ShmCoy = 'BR'
ShmAcno = ''
ShmName1 = 'Light Blue Duck'
ShmName2 = ''
I want to do this job with single SQL query. Is it possible. Pls help.
Herewith I am providing the sample data
BR··Light Blue Duck··
·0234578···
BR··Aqua Duck··
·0234586···
UB··Aqua Duck··
Regards,
OmavYou might consider copying the data out to a .Txt file with BCP. You could
then insert the data using BULK INSERT
--
HTH
Ryan Waight, MCDBA, MCSE
"Omavlana" <kiran@.boardroomlimited.com> wrote in message
news:b14098ab.0310080228.30a01631@.posting.google.com...
> Hi,
> How can I create a temporary table say "Tblabc" with column fields
> ShmCoy char(2)
> ShmAcno char(10)
> ShmName1 varchar(60)
> ShmName2 varchar(60)
> and fill the table from the data extracted from the statement...
> "select logdetail from shractivitylog"
>
> The above query returns single value field the data seperated with a '·'
> Ex:
> BR··Light Blue Duck··
> in this case I should get
> ShmCoy = 'BR'
> ShmAcno = ''
> ShmName1 = 'Light Blue Duck'
> ShmName2 = ''
> I want to do this job with single SQL query. Is it possible. Pls help.
>
> Herewith I am providing the sample data
> BR··Light Blue Duck··
> ·0234578···
> BR··Aqua Duck··
> ·0234586···
> UB··Aqua Duck··
>
> Regards,
> Omavsql

Creating table where column_names are results from query from other table

How I can create table where column name is result of query of second table?

Next cod doesnt work:

Use Pubs
declare @.naz varchar(40)
declare naziv cursor
for
select top 1 au_lname from authors

open naziv
FETCH NEXT FROM naziv INTO @.naz
close naziv
deallocate naziv
GO

CREATE TABLE #t1
(@.naz varchar(20),
quote int
)
GO

DROP TABLE #t1

use dynamic sql, although it won't work for temp tables.

EXECUTE('create table t1 (' + @.naz + ' varchar(20), quote int)'

Also, you don't need a cursor to do what you're doing above, it's overkill.

|||You don't need a cursor if you are only returning one row. Otherwise, you will need a cursor to exec the dynamic sql. If you just want the code to execute to create a number of tables, then you can do all the above in a stored procedure and have it output the code, without resorting to dynamic SQL (as you will be doing the executing). Thus:

declare @.dynsql varchar(100)
set @.dynsql = 'CREATE TABLE #t1 ( ' + @.naz + ' varchar(20), quote int ) GO'

sp_executesql @.dynsql

If you want to be really clever, and want to be able to dynamically set the type, size and precision of the column(s) to create, query INFORMATION_SCHEMA.COLUMNS for this information

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = @.naz

You will probably want to specify the tablename of the originating column that you are copying in the new table in the above query aswell.

HTH

For more SQL tips, check out my blog:
|||Use ALTER TABLE with dynamic SQL to add the columns. The base definition will be used in the CREATE TABLE statement.

Creating Stored procedures

HI

I am trying to create a stored procedure which inputs data from an access db to an SQL DB. My code:

CREATE PROCEDURE s2_comms
@.DBpath varchar(50)
AS
EXEC sp_addlinkedserver 'TempS2commsServer', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', @.DBpath
CREATE DATABASE S2comms
USE S2comms
select * into Codes from OPENQUERY(TempS2commsServer, 'SELECT * FROM Codes')
select * into comms from OPENQUERY(TempS2commsServer, 'SELECT * FROM comms')
EXEC sp_dropserver 'TempS2commsServer'

But it tells me:
a USE database statement is not allowed in a procedure or trigger.

i am new to sql and cant figure out how to get round this, any ideas?
thanks for any help.well if you are using SQL Server, just create a DTS package to do this, much easier. Then you can schedule it to run when ever.

creating pivot table

Hallo,

I have a table with 3 columns:
table tbl_user_class
===============
user_id (int) PK
class_id (varchar(20)) PK
class_value(varchar(100))

values are like so:

user_id class_id class_value
======================
1 firstname Rogier
1 lastname Doekes
2 firstname Mary
3 lastname Smith
....

I would like to create result set in the following format

user_id firstname lastname
====================
1 Rogier Doekes
2 Mary Smith
.....

How do I accomplish this? I tried using CASE WHEN statements but the best I could come up with was this:
1 Rogier null
1 null Doekes
2 Mary null
2 null Smith

when I did the following t-SQL statement:
select userID,
CASE WHEN classID = 'firstname' THEN classvalue END as 'firstname',
CASE WHEN classID = 'lastname' THEN classvalue END as 'lastname'
FROM tbl_user_class

Thanks for any help,

-Rogier DoekesUSE Northwind
GO

CREATE TABLE myTable99([user_id] int, class_id varchar(15), class_value varchar(15))
GO

INSERT INTO myTable99([user_id], class_id, class_value)
SELECT 1, 'firstname', 'Rogier' UNION ALL
SELECT 1, 'lastname', 'Doekes' UNION ALL
SELECT 2, 'firstname', 'Mary' UNION ALL
SELECT 2, 'lastname', 'Smith' UNION ALL
SELECT 3, 'firstname', 'Brett' UNION ALL
SELECT 4, 'lastname', 'Kaiser'
GO

SELECT a.[user_id], b.FirstName, c.LastName
FROM (SELECT Distinct [User_id] FROM myTable99) AS a
LEFT JOIN (SELECT [user_id], class_value as FirstName FROM myTable99 WHERE class_id = 'firstname') AS b
ON a.[user_id] = b.[user_id]
LEFT JOIN (SELECT [user_id], class_value as LastName FROM myTable99 WHERE class_id = 'lastname') AS c
ON a.[user_id] = c.[user_id]
GO

DROP TABLE myTable99
GO|||that does the job,

Thanks a lot Brett

-Rogier

Creating multiple rows from a field with a list

OK. I'm being sent some data with a varchar field called my_elements that contains a 'list' of data items e.g. "4950,1,0%4954,2,0%4955,1,1"
I want to separate this into three records with a my_element field:
"4950,1,0"
"4954,2,0"
"4955,1,1"
How can I do this in SQL? Here's a template for what I want:

create table my_table
(
my_id int,
my_elements varchar(8000)
)
insert into my_table values (1,'4950,1,0%4954,2,0%4955,1,1')

-- Now I need some SQL to produce to create rows like these:
-- 1,'4950,1,0'
-- 1,'4954,2,0'
-- 1,'4955,1,1'
--
-- Or even better, as 4 numeric fields per row:
-- 1,4950,1,0
-- 1,4954,2,0
-- 1,4955,1,1

Also, I'd like to aviod using cursors if I can.

Any help appreciated. Thanks!do you have a fixed or variable number of data items?|||OK So this example Sucks But Hey

It Works for your example

SELECT my_id,SUBSTRING(my_elements,0,9) FROM my_table
UNION
SELECT my_id,SUBSTRING(my_elements,10,8) FROM my_table
UNION
SELECT my_id,SUBSTRING(my_elements,19,9) FROM my_table

tehe

GW|||Originally posted by Paul Young
do you have a fixed or variable number of data items?

Yeah, sorry, I forgot to point out, a variable number of data items|||mmmm

wonder if we could work out the entire length of the field ie. total number of seperate records

Then

Iterate through a loop using local variables to build a dynamic SQL Statement incrementing the substring position as we go and adding the UNIONS then execute that.

It's a thought

Is the physical length of the Data Items Consistent ?|||or:

if object_id('TEMPDB..#my_table') is not null drop table #my_table

create table #my_table (
my_id int
, my_elements varchar(8000))

insert into #my_table values (1,'4950,1,0%4954,2,0%4955,1,1')
insert into #my_table values (2,'4850,1,0%4854,2,0%4855,1,1')
insert into #my_table values (3,'4750,1,0%4754,2,0%4755,1,1')
insert into #my_table values (4,'4650,1,0%4654,2,0%4655,1,1')
insert into #my_table values (5,'4550,1,0%4554,2,0%4555,1,1')

declare @.Tbl table(my_id int, my_element1 int, my_element2 int, my_element3 int)
declare @.my_id int, @.my_elements varchar(8000)
, @.RecordSeperator char(1), @.ItemSeperator char(1)
, @.my_element varchar(12)
, @.RecordPosition int, @.ItemPosition int, @.LastRecordPosition int, @.LastItemPosition int
, @.Int1 int, @.Int2 int, @.Int3 int, @.Int4 int

select @.RecordSeperator = '%'
, @.ItemSeperator = ','

select @.my_id = min(my_id) from #my_table
while (@.my_id is not null) begin
select @.my_elements = my_elements from #my_table where my_id = @.my_id

set @.LastRecordPosition = 1
set @.RecordPosition = charindex(@.RecordSeperator, @.my_elements, @.LastRecordPosition)
while (@.RecordPosition > 0) begin
set @.my_element = substring(@.my_elements,@.LastRecordPosition,@.Record Position-@.LastRecordPosition)

set @.LastItemPosition = 1
set @.ItemPosition = charindex(@.ItemSeperator, @.my_element, @.LastItemPosition)
set @.Int1 = cast(substring(@.my_element,@.LastItemPosition,@.Item Position-@.LastItemPosition) as int)

set @.LastItemPosition = @.ItemPosition + 1
set @.ItemPosition = charindex(@.ItemSeperator, @.my_element, @.LastItemPosition)
set @.Int2 = cast(substring(@.my_element,@.LastItemPosition,@.Item Position-@.LastItemPosition) as int)

set @.LastItemPosition = @.ItemPosition + 1
set @.ItemPosition = charindex(@.ItemSeperator, @.my_element, @.LastItemPosition)
set @.Int3 = cast(substring(@.my_element,@.LastItemPosition,len(@. my_element)) as int)

raiserror('ID: %d ''%s'' %d - %d - %d.',0,1,@.my_id,@.my_element,@.Int1,@.Int2,@.Int3)

insert into @.Tbl values(@.my_id, @.Int1, @.Int2, @.Int3)

set @.LastRecordPosition = @.RecordPosition + 1
set @.RecordPosition = charindex(@.RecordSeperator, @.my_elements, @.LastRecordPosition)

end

select @.my_id = min(my_id) from #my_table where my_id > @.my_id
end

select * from @.Tbl|||Wow! Looks good! Thank you Paul.

creating index on calculated field in table -sql 2000

Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2
]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegroups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2
]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug

creating index on calculated field in table -sql 2000

Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
...
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ? How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.
You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegr oups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
...
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ? How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.
|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!
|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug

creating index on calculated field in table -sql 2000

Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 = something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegroups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug