| Author |
 Topic  |
|
|
tsaliki
Starting Member
India
12 Posts |
 Posted - 11/08/2012 : 00:15:19
|
i have a table with records and code as follows:
create table companieswithexec
(
[companyname] varchar(100),
[exec first Name 1] varchar(100),
[exec last Name 1] varchar (100),
[exec first Name 2] varchar(100),
[exec last Name 2] varchar (100),
[exec first Name 3] varchar(100),
[exec last Name 3] varchar (100),
[exec first Name 4] varchar(100),
[exec last Name 4] varchar (100),
[exec first Name 5] varchar(100),
[exec last Name 5] varchar (100),
[exec first Name 6] varchar(100),
[exec last Name 6] varchar (100),
[exec first Name 7] varchar(100),
[exec last Name 7] varchar (100)
)
insert into companieswithexec values ('sandisk corp','adams','paul','jacob','david','smith','alexander','russel','sasi','dravid','alex','ruth','kiran','geary','michael')
insert into companieswithexec values ('altech corp','george','eric','john','shyam','scott','todd','richard','joseph','mark','andrew','strauss','neil','johnson','steve')
insert into companieswithexec values ('xyz corp','mike','peter','saurav','jay','gary','james','lara','laxman','gilli','sachin','adam','waugh','mark','chris')
the code is :
DECLARE @COLNAME VARCHAR(50)
DECLARE @SQL VARCHAR(max)
DECLARE @COL_NBR VARCHAR(5)
DECLARE @COMPANY VARCHAR(100)
SET @COMPANY = 'sandisk corp,xyz corp,abc,altech corp '
SET @SQL = ''
-----------
DECLARE COL_CUR CURSOR FOR SELECT [NAME] FROM SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('companieswithexec') AND [NAME] LIKE 'exec first Name%'
OPEN COL_CUR FETCH NEXT FROM COL_CUR INTO @COLNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COL_NBR = (SELECT REPLACE(@COLNAME,'exec first Name ',''))
SET @SQL = @SQL + (SELECT 'SELECT [companieswithexec].[companyname] as [company],[exec first Name ' + @COL_NBR + '] AS [exec first Name],[exec last Name ' + @COL_NBR + '] AS [exec last Name] FROM [companieswithexec] ')
SET @SQL = @SQL + ' UNION '
FETCH NEXT FROM COL_CUR INTO @COLNAME
END
CLOSE COL_CUR
DEALLOCATE COL_CUR
SET @SQL = (SELECT REPLACE(@SQL + ' ',' UNION ',''))
SET @SQL = ';WITH CTE ([companyname],[exec first Name],[exec last Name]) AS (' + @SQL + ')'
SET @SQL = @SQL + 'SELECT * FROM CTE WHERE [companyname] IN (''' + replace(@COMPANY,',',''',''') + ''')'
EXEC (@SQL)
I wanted to store this output in another table.So i created other table as below and tried the below code but not working.So please help me
create table testexecutives
(
firstname varchar(100),
secondname varchar(100),
companyname varchar(max)
)
the code i tried is as follows:
;with cte1(Companyname,[exec first name],[exec Last Name])
as
(
DECLARE @COLNAME VARCHAR(50)
DECLARE @SQL VARCHAR(max)
DECLARE @COL_NBR VARCHAR(5)
DECLARE @COMPANY VARCHAR(100)
SET @COMPANY = 'sandisk corp,xyz corp,abc,altech corp '
SET @SQL = ''
-----------
DECLARE COL_CUR CURSOR FOR SELECT [NAME] FROM SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('companieswithexec') AND [NAME] LIKE 'exec first Name%'
OPEN COL_CUR FETCH NEXT FROM COL_CUR INTO @COLNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COL_NBR = (SELECT REPLACE(@COLNAME,'exec first Name ',''))
SET @SQL = @SQL + (SELECT 'SELECT [companieswithexec].[companyname] as [company],[exec first Name ' + @COL_NBR + '] AS [exec first Name],[exec last Name ' + @COL_NBR + '] AS [exec last Name] FROM [companieswithexec] ')
SET @SQL = @SQL + ' UNION '
FETCH NEXT FROM COL_CUR INTO @COLNAME
END
CLOSE COL_CUR
DEALLOCATE COL_CUR
SET @SQL = (SELECT REPLACE(@SQL + ' ',' UNION ',''))
SET @SQL = ';WITH CTE ([companyname],[exec first Name],[exec last Name]) AS (' + @SQL + ')'
SET @SQL = @SQL + 'SELECT * FROM CTE WHERE [companyname] IN (''' + replace(@COMPANY,',',''',''') + ''')'
EXEC (@SQL)
)
insert into sreenu (Companyname,firstname,LastName) values
select companyname,firstname,lastname from cte1
but i am getting error.
So can you please tell me how to store the the above output into another table.Thank you
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 11/08/2012 : 00:41:09
|
I modified some code......
Run below script once...
create table companieswithexec
(
[companyname] varchar(100),
[exec first Name 1] varchar(100),
[exec last Name 1] varchar (100),
[exec first Name 2] varchar(100),
[exec last Name 2] varchar (100),
[exec first Name 3] varchar(100),
[exec last Name 3] varchar (100),
[exec first Name 4] varchar(100),
[exec last Name 4] varchar (100),
[exec first Name 5] varchar(100),
[exec last Name 5] varchar (100),
[exec first Name 6] varchar(100),
[exec last Name 6] varchar (100),
[exec first Name 7] varchar(100),
[exec last Name 7] varchar (100)
)
insert into companieswithexec values ('sandisk corp','adams','paul','jacob','david','smith','alexander','russel','sasi','dravid','alex','ruth','kiran','geary','michael')
insert into companieswithexec values ('altech corp','george','eric','john','shyam','scott','todd','richard','joseph','mark','andrew','strauss','neil','johnson','steve')
insert into companieswithexec values ('xyz corp','mike','peter','saurav','jay','gary','james','lara','laxman','gilli','sachin','adam','waugh','mark','chris')
create table testexecutives
(
firstname varchar(100),
secondname varchar(100),
companyname varchar(max)
)
--the code is :
DECLARE @COLNAME VARCHAR(50)
DECLARE @SQL VARCHAR(max)
DECLARE @COL_NBR VARCHAR(5)
DECLARE @COMPANY VARCHAR(100)
DECLARE @tempTab varchar(50) = 'testexecutives'
SET @COMPANY = 'sandisk corp,xyz corp,abc,altech corp '
SET @SQL = ''
-----------
DECLARE COL_CUR CURSOR FOR SELECT [NAME] FROM SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('companieswithexec') AND [NAME] LIKE 'exec first Name%'
OPEN COL_CUR FETCH NEXT FROM COL_CUR INTO @COLNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COL_NBR = (SELECT REPLACE(@COLNAME,'exec first Name ',''))
SET @SQL = @SQL + (SELECT 'SELECT [companieswithexec].[companyname] as [company],[exec first Name ' + @COL_NBR + '] AS [exec first Name],[exec last Name ' + @COL_NBR + '] AS [exec last Name] FROM [companieswithexec] UNION ')
FETCH NEXT FROM COL_CUR INTO @COLNAME
END
CLOSE COL_CUR
DEALLOCATE COL_CUR
--PRINT @sql
SET @SQL = ';WITH CTE ([companyname],[exec first Name],[exec last Name]) AS (' + left(@SQL, len(@sql) - 6) + ')'
SET @SQL = @SQL + ' INSERT INTO '+ @tempTab + ' OUTPUT INSERTED.* SELECT * FROM CTE WHERE [companyname] IN (''' + replace(@COMPANY,',',''',''') + ''')'
select @SQL
EXEC (@SQL)
SELECT * FROM testexecutives
--
Chandu |
 |
|
| |
Topic |
|
|
|
How to store the above result in another table
