February 6, 2014 at 1:36 am
Hi
I have this query;
select c.name
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
where t.name= 'Hosea_tblDATA_NOTES'and c.is_identity = 0
order by column_id
and I get these results;
NOTE_ID
NOTE_DESCRIPTION
NOTE_TEXT
NOTE_STATUS
NOTE_STARTDATE
NOTE_ENDDATE
NOTE_AUTHOR
NOTE_LASTUPDATE
I was thinking if it is possible to create a table from the results of this query, my table name(in this case 'Hosea_tblDATA_NOTES') will be a parameter, that whatever table name I pass, with the results I get I will be able to create another table, assuming all data types are varchars, but it should be generic.
I’m trying to create it but no luck. Help please, or advise.
February 6, 2014 at 1:57 am
Can't you use SELECT ... INTO?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 6, 2014 at 2:06 am
I want to create a new table with the about of that query, that output has to be my columns
February 6, 2014 at 2:16 am
hoseam (2/6/2014)
I want to create a new table with the about of that query, that output has to be my columns
Ah ok, now I get it.
DECLARE @SQLStatement VARCHAR(2000);
SET @SQLStatement = 'CREATE TABLE Hosea_tblDATA_NOTES (';
SELECT @SQLStatement = @SQLStatement + ',' + c.name + ' VARCHAR(100) NULL'
FROM sys.tables t
JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHEREt.name= 'Hosea_tblDATA_NOTES'
AND c.is_identity= 0
ORDER BY column_id;
SET @SQLStatement = @SQLStatement + ');';
SET @SQLStatement = REPLACE(@SQLStatement,'(,','('); -- remove first comma
EXEC sp_executesql @SQLStatement;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 6, 2014 at 2:28 am
Is that a dynamic sql, I'm just reading about it now and I haven't grasp it yet, can you explain your query to me please, also explaining dynamic sql.
I copied the code and I get this error, "Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'"
February 6, 2014 at 2:35 am
It is dynamic SQL yes.
The concept is simple: you construct a SQL statement on the fly, store it inside a string variable and execute it with sp_executesql.
Add PRINT @SQLStatement right before the EXEC statement, you'll see what I mean.
To resolve the error: make @SQLStatement a NVARCHAR variable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 6, 2014 at 3:00 am
I tried this for a table with VARCHAR and DECIMAL datatype. and came up with following ... not sure whether it will work for all the datatypes out there or not ... but you can give it a try
select t.name TAB_NAME,'[' + c.name +']' COL_NAME,TY.name DT_NAME,c.max_length,c.precision,c.scale
INTO #TEMP
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
LEFT OUTER JOIN sys.types TY
ON c.system_type_id = TY.system_type_id
where t.name= 'TAB_NAME'and c.is_identity = 0
order by column_id
DECLARE @tab VARCHAR(1000)= 'CREATE TABLE NEW_TAB_NAME ( '
SELECT @tab +
STUFF(
(
SELECT ','+ COL_NAME + ' ' + CASE
WHEN DT_NAME LIKE '%CHAR%' THEN DT_NAME + '(' + CONVERT(VARCHAR(4),max_length) + ')'
WHEN DT_NAME = 'DECIMAL' THEN DT_NAME + '(' + CONVERT(VARCHAR(4),PRECISION) +',' + CONVERT(VARCHAR(4),SCALE) +')'
ELSE DT_NAME
END
FROM #TEMP
FOR XML PATH ('')
),1,1,''
) + ')'
DROP TABLE #TEMP
February 6, 2014 at 3:43 am
hoseam (2/6/2014)
I want to create a new table with the about of that query, that output has to be my columns
maybe
select NOTE_ID, NOTE_DESCRIPTION, NOTE_TEXT, NOTE_STATUS, NOTE_STARTDATE
, NOTE_ENDDATE, NOTE_AUTHOR, NOTE_LASTUPDATE into NEW_TABLE_NAME
from Hosea_tblDATA_NOTES
where 1=0
February 6, 2014 at 3:45 am
I edited your query to this:
DECLARE @SQLStatement NVARCHAR(2000);
SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tblDATA_NOTE (';
SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+st.name +' '+cast(c.max_length as varchar(50))
FROM sys.tables t
JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types st
ON st.system_type_id = c.system_type_id
WHERE t.name = 'Hosea_tblDATA_NOTES'
AND c.is_identity= 0
ORDER BY column_id;
SET @SQLStatement = @SQLStatement + ');';
SET @SQLStatement = REPLACE(@SQLStatement,'(,','('); -- remove first comma
--EXEC sp_executesql @SQLStatement;
select @SQLStatement
I get the datatype from sys.types st, now what I'm struggling with is, I want to remove length on INT and DECIMAL but keep it on varchar, and also put it in brackets.
February 6, 2014 at 3:47 am
SrcName (2/6/2014)
hoseam (2/6/2014)
I want to create a new table with the about of that query, that output has to be my columnsmaybe
select NOTE_ID, NOTE_DESCRIPTION, NOTE_TEXT, NOTE_STATUS, NOTE_STARTDATE
, NOTE_ENDDATE, NOTE_AUTHOR, NOTE_LASTUPDATE into NEW_TABLE_NAME
from Hosea_tblDATA_NOTES
where 1=0
Yes, hardcoding everything is surely the solution.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 6, 2014 at 3:50 am
hoseam (2/6/2014)
I edited your query to this:DECLARE @SQLStatement NVARCHAR(2000);
SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tblDATA_NOTE (';
SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+st.name +' '+cast(c.max_length as varchar(50))
FROM sys.tables t
JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types st
ON st.system_type_id = c.system_type_id
WHERE t.name = 'Hosea_tblDATA_NOTES'
AND c.is_identity= 0
ORDER BY column_id;
SET @SQLStatement = @SQLStatement + ');';
SET @SQLStatement = REPLACE(@SQLStatement,'(,','('); -- remove first comma
--EXEC sp_executesql @SQLStatement;
select @SQLStatement
I get the datatype from sys.types st, now what I'm struggling with is, I want to remove length on INT and DECIMAL but keep it on varchar, and also put it in brackets.
Stuff like this can very easily be found on the net.
After 1 minute of googling:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 6, 2014 at 6:14 am
please tell me if this logic is correct. I'm passing table name as parameter, the same tableName that will be used to create the *new table* called tempTable.
after the temp table is created, I want to load all the data from the table that I passed in as a parameter, load them into the new tempTable, do some update in the new tempTable then load the data back to the original table, the one I passed in as a parameter.
Currently I get this error: Must declare the table variable "@TableName"
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tempTable]') AND type in (N'U'))
DROP TABLE [dbo].[tempTable]
GO
DECLARE @SQLStatement NVARCHAR(2000),@TableName VARCHAR(50), @Product_Id VARCHAR(50), @Fund_Id VARCHAR(50),
@NewFund_Id VARCHAR(50),@NewProduct_Id VARCHAR(50)
SET @Product_Id = 'AGP1'
SET @Fund_Id = 'E016'
SET @NewProduct_Id = 'PCCV'
SET @NewFund_Id = 'E33333'
SET @TableName = 'Hosea_tblDATA_NOTES'
SET @SQLStatement = 'CREATE TABLE dbo.tempTable (';
SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+ CASE
WHEN st.name LIKE '%CHAR%'
THEN st.name + '(' + CONVERT(VARCHAR(4),c.max_length) + ')'
ELSE st.name
END
FROM sys.tables t
JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types st
ON st.system_type_id = c.system_type_id
WHERE t.name = @TableName
AND c.is_identity= 0
ORDER BY column_id;
SET @SQLStatement = @SQLStatement + ');';
SET @SQLStatement = REPLACE(@SQLStatement,'(,','('); -- remove first comma
EXEC sp_executesql @SQLStatement;
SELECT *
INTO tempTable
FROM @TableName
WHERE (Product_Id = is null or Product_Id = @Product_Id )
AND (FUND_ID is null or FUND_ID = @FUND_ID )
February 6, 2014 at 6:28 am
You used @tablename inside the dynamic SQL.
If you would print out @SQLStatement, you would see there is no declaration for @tablename.
You have two options:
* pass @tablename as an input parameter to sp_executesql. More info: Using sp_executesql
* you can use REPLACE on @SQLStatement to replace the string @tablename inside @SQLStatement with the actual value of @tablename. Make sure you don't forget to include single quotes in the @SQLStatement around @tablename.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply