insert multiple values into table

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)

insert multiple values into table

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-07-08 : 08:59:23

how do i get multiple like values from my where statement

i tried in but it didnt work. not sure of the syntax.

i want to get the following values as well

fixedfloat, purchaseprice, roll etc.

select distinct

o.xtype AS TYPE,
o.name AS STORED_PROC,
o.crdate AS CREATE_DATE,
'ActualTradeDate' AS FIELD_NAME
from syscomments c join sysobjects o on c.id = o.id
where c.text like ('%ActualTradeDate%')order by o.xtype, o.name

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-08 : 09:26:57

where c.text like ('%ActualTradeDate%')
or c.text like ('%fixedfloat%')
or c.text like ('%purchaseprice%')
or c.text like ('%roll %')
order by o.xtype, o.name

maybe you want "and" instead of "or" - it's not clear from the question.

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

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-07-08 : 09:33:29

thanks that is what i did, i thought i could use an in statement.
thanks

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-07-08 : 09:33:30

thanks that is what i did, i thought i could use an in statement.
thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-08 : 09:39:36

Nope - not with a like.
you can

with cte as
(
select s = '%ActualTradeDate%'
union all
select s = '%fixedfloat%'
union all
select s = '%purchaseprice%'
union all
select s = '%roll%'
)
select distinct

o.xtype AS TYPE,
o.name AS STORED_PROC,
o.crdate AS CREATE_DATE,
'ActualTradeDate' AS FIELD_NAME
from syscomments c join sysobjects o on c.id = o.id
join cte
on c.text like cte.s
order by o.xtype, o.name

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

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-07-08 : 10:18:41

not sure where to place that
this is my sp where would i place and what is the syntax, bit confused

with cte as
(select s = 'ActualTradeDate'
union all
select s = 'Actual Trade Date'
union all
select s = 'Fixedfloat'
union all
select s = 'Purchaseprice'
union all
select s = 'Roll'
union all
select s = 'Rolldate'
union all
select s = 'ISIN'
union all
select s = 'Cusip'
union all
select s = 'Bloomberg')

CREATE TABLE #RPTLIST3663

(TYPE VARCHAR(2),
STORED_PROC VARCHAR(255),
CREATE_DATE DATETIME,
FIELD_NAME VARCHAR(255))

INSERT INTO #RPTLIST3663

select distinct
o.xtype AS TYPE,
o.name AS STORED_PROC,
o.crdate AS CREATE_DATE,
CASE WHEN c.text like ('%ActualTradeDate%') THEN 'ActualTradeDate'
WHEN c.text like ('%Actual Trade Date%') THEN 'Actual Trade Date'
WHEN c.text like ('%Fixedfloat%') THEN 'Fixedfloat'
WHEN c.text like ('%Purchaseprice%') THEN 'Purchaseprice'
WHEN C.text like ('%Roll%') THEN 'Roll'
WHEN c.text like ('%Rolldate%') THEN 'Rolldate'
WHEN C.text like ('%ISIN%') THEN 'ISIN'
WHEN c.text like ('%Cusip%') THEN 'Cusip'
WHEN c.text like ('%Bloomberg%') THEN 'Bloomberg' END AS FIELD_NAME
from syscomments c join sysobjects o on c.id = o.id
where c.text like ('%ActualTradeDate%')OR c.text like ('%Actual Trade Date%')
OR c.text like ('%Fixedfloat%') OR c.text like ('%Purchaseprice%')
OR c.text like ('%Roll%')OR c.text like ('%Rolldate%')
OR c.text like ('%ISIN%')OR c.text like ('%Cusip%')
OR c.text like ('%Bloomberg%')
order by o.xtype, o.name

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-08 : 10:41:12

with cte as
(select FIELD_NAME = 'ActualTradeDate', seq = 1
union all
select FIELD_NAME = 'Actual Trade Date', 2
union all
select FIELD_NAME = 'Fixedfloat', 3
union all
select FIELD_NAME = 'Purchaseprice', 4
union all
select FIELD_NAME = 'Roll', 5
union all
select FIELD_NAME = 'Rolldate', 6
union all
select FIELD_NAME = 'ISIN', 7
union all
select FIELD_NAME = 'Cusip', 8
union all
select FIELD_NAME = 'Bloomberg', 9
) ,
cte2 as
(
select distinct
max(o.xtype) AS TYPE,
o.name AS STORED_PROC,
max(o.crdate) AS CREATE_DATE,
min(seq) AS seq
from syscomments c
join sysobjects o
on c.id = o.id
join cte
on c.text like '%' + cte.FIELD_NAME + '%'
group by o.name
)
INSERT INTO #RPTLIST3663
select
cte.TYPE,
cte.STORED_PROC,
cte.CREATE_DATE,
cte2.FIELD_NAME
from cte
join cte2
on cte.seq = cte2.seq

but it might be better to have the fields as a table variable

declare @fields table (FIELD_NAME varchar(50), seq int identity)
insert @fields ((FIELD_NAME)
values ('ActualTradeDate'),('Actual Trade Date'),('Fixedfloat'),('Purchaseprice'),('Roll'),('Rolldate'),('ISIN'),('Cusip'),('Bloomberg')

;with cte2 as
(
select distinct
max(o.xtype) AS TYPE,
o.name AS STORED_PROC,
max(o.crdate) AS CREATE_DATE,
min(seq) AS seq
from syscomments c
join sysobjects o
on c.id = o.id
join @fields cte
on c.text like '%' + cte.FIELD_NAME + '%'
group by o.name
)
INSERT INTO #RPTLIST3663
select
cte.TYPE,
cte.STORED_PROC,
cte.CREATE_DATE,
cte2.FIELD_NAME
from @fields cte
join cte2
on cte.seq = cte2.seq

Note - this will only give the value for the lowest seq for the name found like your code. Easy to change it to give them all as seperate entries or a csv list.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources