emi_sastra
asked on
Generate list for insert to sql list
Hi All,
I want to generate below list :
TMCUSTOMER ("0015 ", " ", "CASH ", "CASH ", "Jl.Samratulangi No.83", "", " ", " ", "INDONESIA ", " ", " ", " ", " ", " ", " ", 0d, 0d, 0d, 0d, 0, "UMM ", " ", null, null, null, "110101 ", "T", " ", "RP ", null, null, null, null));
TMCUSTOMER is the table name.
How could I do it ?
Thank you.
I want to generate below list :
TMCUSTOMER ("0015 ", " ", "CASH ", "CASH ", "Jl.Samratulangi No.83", "", " ", " ", "INDONESIA ", " ", " ", " ", " ", " ", " ", 0d, 0d, 0d, 0d, 0, "UMM ", " ", null, null, null, "110101 ", "T", " ", "RP ", null, null, null, null));
TMCUSTOMER is the table name.
How could I do it ?
Thank you.
HainKurt
with just this information, you cannot get any answer :)
but you can try
select
'TMCUSTOMER ("0015 ", " ", "CASH ", "CASH ", "Jl.Samratulangi No.83", "", " ", " ", "INDONESIA ", " ", " ", " ", " ", " ", " ", 0d, 0d, 0d, 0d, 0, "UMM ", " ", null, null, null, "110101 ", "T", " ", "RP ", null, null, null, null));'ASKER
Hi Huseyin ,
It is obvious that the list is the data from a table.
I missed at the header, I mean it will be used to feed to sql lite using coding.
It has 3 pattern at table scheme :
1. String (Char, Varchar and etc), should use " at the beginning and end of a column.
2. Number (Money, Integer and etc), use d after then number
3. Date
The problem is how to query the data and giving format based on the data type.
Thank you.
It is obvious that the list is the data from a table.
I missed at the header, I mean it will be used to feed to sql lite using coding.
It has 3 pattern at table scheme :
1. String (Char, Varchar and etc), should use " at the beginning and end of a column.
2. Number (Money, Integer and etc), use d after then number
3. Date
The problem is how to query the data and giving format based on the data type.
Thank you.
ASKER
Query MS SQL table into array list.
Thank you.
Thank you.
use EE
Declare @TMCUSTOMER Table(Col1 varchar(25)
, Col2 varchar(25)
, Col3 varchar(25)
, Col4 varchar(25)
, Col5 varchar(25)
, Col6 varchar(25)
, Col7 varchar(25)
, Col8 varchar(25)
, Col9 varchar(25)
, Col10 varchar(25)
, Col11 varchar(25)
, Col12 varchar(25)
, Col13 varchar(25)
, Col14 varchar(25)
, Col15 varchar(25)
, Col16 varchar(25)
, Col17 varchar(25)
, Col18 varchar(25)
, Col19 varchar(25)
, Col20 varchar(25)
, Col21 varchar(25)
, Col22 varchar(25)
, Col23 varchar(25)
, Col24 varchar(25)
, Col25 varchar(25)
, Col26 varchar(25)
, Col27 varchar(25)
, Col28 varchar(25)
, Col29 varchar(25)
, Col30 varchar(25)
, Col31 varchar(25)
, Col32 varchar(25)
, Col33 varchar(25));
Insert Into @TMCUSTOMER(Col1, Col2, Col3, Col4,Col5, Col6,Col7, Col8,Col9, Col10,Col11, Col12,Col13, Col14,Col15, Col16,Col17, Col18,Col19, Col20,Col21, Col22,Col23, Col24,Col25, Col26, Col27,Col28, Col29,Col30,Col31, Col32,Col33) values
('0025 ', ' ', 'CASH ', 'CASH ', 'Jl.Samratulangi No.83', '', ' ', ' ', 'INDONESIA ', ' ', ' ', ' ', ' ', ' ', ' ', '0d', '0d', '0d', '0d', 0, 'UMM ', ' ', null, null, null, '110101 ', 'T', ' ', 'RP ', null, null, null, null);
Select * from @TMCUSTOMER;ASKER
Hi Mike,
Your code can not be used.
Thank you.
Your code can not be used.
Thank you.
Display the create table TMCUSTOMER directive
So you want an output that has tablename and some data organized in this specific way, though you are asking about an insert.
So you want an output that has tablename and some data organized in this specific way, though you are asking about an insert.
ASKER
Hi arnold,
The purpose to insert to sql lite.
Any other array list format are welcome.
Thank you.
The purpose to insert to sql lite.
Any other array list format are welcome.
Thank you.
ASKER
Hi All,
I try below :
SELECT CustCode, FullName, CreditLimit, CrtId, CrtDate
FROM TMCUSTOMER
FOR JSON AUTO
The error at JSON.
Thank you.
I try below :
SELECT CustCode, FullName, CreditLimit, CrtId, CrtDate
FROM TMCUSTOMER
FOR JSON AUTO
The error at JSON.
Thank you.
>>"It is obvious that the list is the data from a table."
No, it is not obvious at all, it just looks like a long text string.
>>"I mean it will be used to feed to sql lite using coding"
so you want a set in INSERTS? Suitable for SQLite?
The source able is in MS SQL Server?
What are the column names in the table TMCUSTOMER?
e.g. what column holds "0015 " in the following row?
TMCUSTOMER ("0015 ", " ", "CASH ", "CASH ", "Jl.Samratulangi No.83", "", " ", " ", "INDONESIA ", " ", " ", " ", " ", " ", " ", 0d, 0d, 0d, 0d, 0, "UMM ", " ", null, null, null, "110101 ", "T", " ", "RP ", null, null, null, null));
Without column names we really cannot help
No, it is not obvious at all, it just looks like a long text string.
>>"I mean it will be used to feed to sql lite using coding"
so you want a set in INSERTS? Suitable for SQLite?
The source able is in MS SQL Server?
What are the column names in the table TMCUSTOMER?
e.g. what column holds "0015 " in the following row?
TMCUSTOMER ("0015 ", " ", "CASH ", "CASH ", "Jl.Samratulangi No.83", "", " ", " ", "INDONESIA ", " ", " ", " ", " ", " ", " ", 0d, 0d, 0d, 0d, 0, "UMM ", " ", null, null, null, "110101 ", "T", " ", "RP ", null, null, null, null));
Without column names we really cannot help
You are trying to create a single column that has the comma separated values?
Insert into TMCUSTOMER COLUMNNAME VALUE (.......)
Inserting a list into a column would require that you unpack and makes data retrieval more complicated, so the question is why would you fo it, you could always coalesce all the columns when you query the table if there is a need to output the data as a list.
What are the requirements restrictions
What are you using to interact with sqllite?
Insert into TMCUSTOMER COLUMNNAME VALUE (.......)
Inserting a list into a column would require that you unpack and makes data retrieval more complicated, so the question is why would you fo it, you could always coalesce all the columns when you query the table if there is a need to output the data as a list.
What are the requirements restrictions
What are you using to interact with sqllite?
ASKER
- Without column names we really cannot help
That is the challenge, not need to know the columns name.
We could use asterisk (*) to query.
SELECT
*
FROM <TABLENAME>
No need to know about the table column name and its schema.
The query should just know the data type, the columns are based on what it is on the table.
Thank you.
That is the challenge, not need to know the columns name.
We could use asterisk (*) to query.
SELECT
*
FROM <TABLENAME>
No need to know about the table column name and its schema.
The query should just know the data type, the columns are based on what it is on the table.
Thank you.
ASKER
- You are trying to create a single column that has the comma separated values?
Not single columns.
- What are you using to interact with sqllite?
The list generated from Ms SQL for any table name.
Thank you.
Not single columns.
- What are you using to interact with sqllite?
The list generated from Ms SQL for any table name.
Thank you.
If you are using json there are methods to convert the list of data coming back to insert the individual data into a correct column, you seem to be approaching the issue if one side needs the data as a list, array, you are trying to mimic the same structure on the data storage side.
Json has a mechanism that the handler/exchange extracts data needed and sends it in json that on the client the data can be displayed the same is true when a client browser sends a list for inser, the receiving side takes the items and stores them in a table as mike's example would in the sqllite.
Starting from the begining you might wish to open a new question detailing what you are trying to do overall and ask for assistance to get there.
I.e. You are building a web app that does X and you want to use json to refresh data, as well as provide user input that would add data into a table, etc.....
If you use php for your web app, asp, asp.net, etc.
Json has a mechanism that the handler/exchange extracts data needed and sends it in json that on the client the data can be displayed the same is true when a client browser sends a list for inser, the receiving side takes the items and stores them in a table as mike's example would in the sqllite.
Starting from the begining you might wish to open a new question detailing what you are trying to do overall and ask for assistance to get there.
I.e. You are building a web app that does X and you want to use json to refresh data, as well as provide user input that would add data into a table, etc.....
If you use php for your web app, asp, asp.net, etc.
ASKER
i just need the format (array list) that could be used to inserted to SQL Lite.
Just the list of data from MS SQL Tables, no need to know how to insert to SQL Lite.
Thank you.
Just the list of data from MS SQL Tables, no need to know how to insert to SQL Lite.
Thank you.
A list in it nature means it is stored as a single value into a single column.
Are you looking to first get a data set from an ms SQL in a comma separated list
And upon getting that data you want it inserted as a list into a table of sqllite whose structure is a single column or are those data sets have a matching set of columns on the sqllite side?
the only thing known is a tablename, ms SQL, sqllite and json is involved somehow .....
Are you looking to first get a data set from an ms SQL in a comma separated list
And upon getting that data you want it inserted as a list into a table of sqllite whose structure is a single column or are those data sets have a matching set of columns on the sqllite side?
the only thing known is a tablename, ms SQL, sqllite and json is involved somehow .....
ASKER
Here is the link.
https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server
But I fails try something like this :
SELECT name, surname
FROM emp
FOR JSON AUTO
Thank you.
https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server
But I fails try something like this :
SELECT name, surname
FROM emp
FOR JSON AUTO
Thank you.
ASKER
Hi arnold.
The database schema at MS SQL and SQL Lite are identical.
Don't worry about the columns name or data type.
Just how to query array list like my sample or like json.
Thank you.
The database schema at MS SQL and SQL Lite are identical.
Don't worry about the columns name or data type.
Just how to query array list like my sample or like json.
Thank you.
@emi_sastra,
re;> Your code can not be used.
I have tested it. Why you are saying cannot be used.
Instead of putting it in a array (which I don't think it exist in TSQL), put in a temp table (use @temp instead of what I have used in my solution as @TMCUSTOMER). You can retrieve its data using a UDF.
Mike
re;> Your code can not be used.
I have tested it. Why you are saying cannot be used.
Instead of putting it in a array (which I don't think it exist in TSQL), put in a temp table (use @temp instead of what I have used in my solution as @TMCUSTOMER). You can retrieve its data using a UDF.
Mike
If you need the data pulled from ms SQL in sqllite why do you inject json into the process?
I can not figure out what it is you are trying to do?
Are you want a json dataset retrieved from ms SQL to then be inserted into an SQL lite table, you would need to use json to covert the json list back into a column stracture to inset the data in sqllite.
You pulled a square peg from ms SQL, then used a tool, that shaved the corners, and are asking us how to make the peg square again.
I can not figure out what it is you are trying to do?
Are you want a json dataset retrieved from ms SQL to then be inserted into an SQL lite table, you would need to use json to covert the json list back into a column stracture to inset the data in sqllite.
You pulled a square peg from ms SQL, then used a tool, that shaved the corners, and are asking us how to make the peg square again.
ASKER
Hi Mike,
Your code works based on your thought.
I have query below :
SELECT CustCode, FullName, CreditLimit, CrtId, CrtDate
FROM TMCUSTOMER
AAJ SUSTER ANI 0.00 LIA 2017-04-04 08:59:38.477
AAS A.R 0.00 NIBAH 2016-02-26 17:48:03.890
become :
"AAJ", "SUSTER ANI",0d, "LIA", "2017-04-04 08:59:38.477"
"AAS ", "A.R" , 1d, "NIBAH", "2016-02-26 17:48:03.890"
How could I add " to string format ? Add d to number and etc.
Thank you.
Your code works based on your thought.
I have query below :
SELECT CustCode, FullName, CreditLimit, CrtId, CrtDate
FROM TMCUSTOMER
AAJ SUSTER ANI 0.00 LIA 2017-04-04 08:59:38.477
AAS A.R 0.00 NIBAH 2016-02-26 17:48:03.890
become :
"AAJ", "SUSTER ANI",0d, "LIA", "2017-04-04 08:59:38.477"
"AAS ", "A.R" , 1d, "NIBAH", "2016-02-26 17:48:03.890"
How could I add " to string format ? Add d to number and etc.
Thank you.
check this
declare @SQL nvarchar(max);
with c as (
SELECT c.name colName, c.system_type_id, t.name datatype
FROM sys.columns c inner join sys.types t on c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('dbo.ulkeler') and t.name<>'sysname'
)
select @SQL = isnull(@SQL + ''','' + ', '') + case
when datatype in ('char','nvarchar','varchar') then '''"'' + ' + colName + ' + ''"'''
when datatype in ('int','biginit','tinyint','bit') then 'cast(' + colName + ' as varchar) + ''d'''
when datatype in ('dat','datetime', 'datetime2') then '''"# + ' + colName + ' + "'''
else '''"'' + ' + colName + ' + ''" ''' end
from c
set @SQL = N'select ''ULKELER ('' + ' + @SQL + ''');'' from ULKELER';
print @sql;
exec sp_executesql @sql;
ULKELER (1d',"AF"',"AFG"',"Afghanistan"',"93"');
ULKELER (2d',"AL"',"ALB"',"Albania"',"355"');
ULKELER (3d',"DZ"',"DZA"',"Algeria"',"213"');
ULKELER (4d',"AS"',"ASM"',"American Samoa"',"684"');ASKER
Hi Arnola,
- I can not figure out what it is you are trying to do?
Back to top of my question:
I wan to query data inside MS SQL into specific format.
Don't worry about SQL Lite.
SELECT CustCode, FullName, CreditLimit, CrtId, CrtDate
FROM TMCUSTOMER
AAJ SUSTER ANI 0.00 LIA 2017-04-04 08:59:38.477
AAS A.R 0.00 NIBAH 2016-02-26 17:48:03.890
become :
"AAJ", "SUSTER ANI",0d, "LIA", "2017-04-04 08:59:38.477"
"AAS ", "A.R" , 1d, "NIBAH", "2016-02-26 17:48:03.890"
How could I add " to string format ? Add d to number and etc.
And I want to SELECT *, not SELECT columns names.
Thank you.
- I can not figure out what it is you are trying to do?
Back to top of my question:
I wan to query data inside MS SQL into specific format.
Don't worry about SQL Lite.
SELECT CustCode, FullName, CreditLimit, CrtId, CrtDate
FROM TMCUSTOMER
AAJ SUSTER ANI 0.00 LIA 2017-04-04 08:59:38.477
AAS A.R 0.00 NIBAH 2016-02-26 17:48:03.890
become :
"AAJ", "SUSTER ANI",0d, "LIA", "2017-04-04 08:59:38.477"
"AAS ", "A.R" , 1d, "NIBAH", "2016-02-26 17:48:03.890"
How could I add " to string format ? Add d to number and etc.
And I want to SELECT *, not SELECT columns names.
Thank you.
ASKER
Hi Huseyin,
You just get what I want.
Let me try it first.
Thank you.
You just get what I want.
Let me try it first.
Thank you.
ASKER
I get below error.
select 'REVENUE2016 (' + '"' + SalesDate + '" '',' + '"' + Btl + '" '',' + '"' + NamaBarang + '"'',' + '"' + Vintage + '"'');' from REVENUE2016
Msg 402, Level 16, State 1, Line 1
The data types varchar and date are incompatible in the add operator.
Thank you.
select 'REVENUE2016 (' + '"' + SalesDate + '" '',' + '"' + Btl + '" '',' + '"' + NamaBarang + '"'',' + '"' + Vintage + '"'');' from REVENUE2016
Msg 402, Level 16, State 1, Line 1
The data types varchar and date are incompatible in the add operator.
Thank you.
ASKER
The schema of table REVENUE2016
SalesDate date
NamaBarang varchar(MAX)
Vintage varchar(50)
Btl money
Thank you.
SalesDate date
NamaBarang varchar(MAX)
Vintage varchar(50)
Btl money
Thank you.
it is not correct, try this
produces @sql as
and when executed, it gives
you need to extend line 9-12 above to accomodate all datatypes...
they are in sys.types table...
declare @SQL nvarchar(max);
with c as (
SELECT c.name colName, c.system_type_id, t.name datatype
FROM sys.columns c inner join sys.types t on c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('dbo.ulkeler') and t.name<>'sysname'
)
select @SQL = isnull(@SQL + ', '' + ', '') + case
when datatype in ('char','nvarchar','varchar') then '''"'' + ' + colName + ' + ''"'
when datatype in ('int','biginit','tinyint','bit') then 'cast(' + colName + ' as varchar) + ''d'
when datatype in ('dat','datetime', 'datetime2') then '''"# + ' + colName + ' + "'''
else '''"'' + ' + colName + ' + ''" ''' end
from c
--print @sql;
set @SQL = N'select ''ULKELER ('' + ' + @SQL + ');'' from ULKELER';
--print @sql;
exec sp_executesql @sql;produces @sql as
select 'ULKELER (' + cast(UlkeId as varchar) + 'd, ' + '"' + IkiliKod + '", ' + '"' + UcluKod + '", ' + '"' + UlkeAdi + '", ' + '"' + TelKodu + '");' from ULKELERand when executed, it gives
ULKELER (1d, "AF", "AFG", "Afghanistan", "93");
ULKELER (2d, "AL", "ALB", "Albania", "355");
ULKELER (3d, "DZ", "DZA", "Algeria", "213");
ULKELER (4d, "AS", "ASM", "American Samoa", "684");you need to extend line 9-12 above to accomodate all datatypes...
they are in sys.types table...
>>"Don't worry about the columns name or data type."
Well we do have to worry about both.
Try this query and provide the result please:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TMCUSTOMER '
Using this type of data we might be able to arrive at some "dynamic sql" that will automate the production of the data rows.
Do you want SQL inserts for SQLite or not?
Well we do have to worry about both.
Try this query and provide the result please:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TMCUSTOMER '
Using this type of data we might be able to arrive at some "dynamic sql" that will automate the production of the data rows.
Do you want SQL inserts for SQLite or not?
the solution above is dynamic creation of such sql
if your table has just a few column, then prepare sql manually and run to get the same result...
select 'ULKELER (' + cast(UlkeId as varchar) + 'd, ' + '"' + IkiliKod + '", ' + '"' + UcluKod + '", ' + '"' + UlkeAdi + '", ' + '"' + TelKodu + '");' from ULKELERif your table has just a few column, then prepare sql manually and run to get the same result...
ASKER
Hi PortletPaul,
- Do you want SQL inserts for SQLite or not?
Yes.
Thank you.
- Do you want SQL inserts for SQLite or not?
Yes.
Thank you.
ASKER
Hi Huseyin,
I query below :
declare @SQL nvarchar(max);
with c as (
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'REVENUE2016 '
)
select @SQL = isnull(@SQL + ''','' + ', '') + case
when DATA_TYPE in ('char','nvarchar','varcha
when DATA_TYPE in ('int','biginit','tinyint'
when DATA_TYPE in ('dat','datetime', 'datetime2') then '''"# + ' + COLUMN_NAME + ' + "'''
else '''"'' + ' + COLUMN_NAME + ' + ''" ''' end
from c
set @SQL = N'select ''REVENUE2016 ('' + ' + @SQL + ''');'' from REVENUE2016';
print @sql;
exec sp_executesql @sql;
I get error :
select 'REVENUE2016 (' + '"' + SalesDate + '" '',' + '"' + NamaBarang + '"'',' + '"' + Vintage + '"'',' + '"' + Btl + '" '');' from REVENUE2016
Msg 402, Level 16, State 1, Line 1
The data types varchar and date are incompatible in the add operator.
Which part of code is wrong ?
Thank you.
I query below :
declare @SQL nvarchar(max);
with c as (
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'REVENUE2016 '
)
select @SQL = isnull(@SQL + ''','' + ', '') + case
when DATA_TYPE in ('char','nvarchar','varcha
when DATA_TYPE in ('int','biginit','tinyint'
when DATA_TYPE in ('dat','datetime', 'datetime2') then '''"# + ' + COLUMN_NAME + ' + "'''
else '''"'' + ' + COLUMN_NAME + ' + ''" ''' end
from c
set @SQL = N'select ''REVENUE2016 ('' + ' + @SQL + ''');'' from REVENUE2016';
print @sql;
exec sp_executesql @sql;
I get error :
select 'REVENUE2016 (' + '"' + SalesDate + '" '',' + '"' + NamaBarang + '"'',' + '"' + Vintage + '"'',' + '"' + Btl + '" '');' from REVENUE2016
Msg 402, Level 16, State 1, Line 1
The data types varchar and date are incompatible in the add operator.
Which part of code is wrong ?
Thank you.
not sure what exactly you want... try this...
when DATA_TYPE in ('date','datetime', 'datetime2') then '''"# + ' + COLUMN_NAME + ' + "'''
>>>
when DATA_TYPE in ('date','datetime', 'datetime2') then '''"#'' + convert(varchar, ' + COLUMN_NAME + ',120) + ''"'
it should generate
"#2017-05-28 01:59:14"
you should work on this part if you want something else
fixed code
this code generates
and query will be
when DATA_TYPE in ('date','datetime', 'datetime2') then '''"# + ' + COLUMN_NAME + ' + "'''
>>>
when DATA_TYPE in ('date','datetime', 'datetime2') then '''"#'' + convert(varchar, ' + COLUMN_NAME + ',120) + ''"'
it should generate
"#2017-05-28 01:59:14"
you should work on this part if you want something else
fixed code
declare @SQL nvarchar(max);
with c as (
SELECT c.name colName, c.system_type_id, t.name datatype
FROM sys.columns c inner join sys.types t on c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('dbo.test') and t.name<>'sysname'
)
select @SQL = isnull(@SQL + ', '' + ', '') + case
when datatype in ('char','nvarchar','varchar') then '''"'' + ' + colName + ' + ''"'
when datatype in ('int','biginit','tinyint','bit') then 'cast(' + colName + ' as varchar) + ''d'
when datatype in ('date','datetime', 'datetime2') then '''"#'' + convert(varchar, ' + colName + ',120) + ''"'
else '''"'' + ' + colName + ' + ''" ''' end
from c
--print @sql;
set @SQL = N'select ''TEST ('' + ' + @SQL + ');'' from TEST';
--print @sql;
exec sp_executesql @sql;this code generates
TEST (1d, "#2017-05-28 02:03:47", "One");
TEST (2d, "#2017-05-28 02:03:47", "Two");
TEST (3d, "#2017-05-28 02:03:47", "Three");
TEST (4d, "#2017-05-28 02:03:47", "Four");
TEST (5d, "#2017-05-28 02:03:47", "Five");and query will be
select 'TEST (' + cast(ID as varchar) + 'd, ' + '"#' + convert(varchar, createdate,120) + '", ' + '"' + Title + '");' from TESTASKER
Still get error :
/****** Object: Table [dbo].[REVENUE2016] Script Date: 5/28/2017 1:19:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[REVENUE2016](
[SalesDate] [date] NULL,
[NamaBarang] [varchar](max) NULL,
[Vintage] [varchar](50) NULL,
[Btl] [money] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
declare @SQL nvarchar(max)
with c as (
SELECT c.name colName, c.system_type_id, t.name datatype
FROM sys.columns c inner join sys.types t on c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('dbo.REVENUE2016
)
select @SQL = isnull(@SQL + ', '' + ', '') + case
when datatype in ('char','nvarchar','varcha
when datatype in ('int','biginit','tinyint'
when datatype in ('date','datetime', 'datetime2') then '''"#'' + convert(varchar, ' + colName + ',120) + ''"'
else '''"'' + ' + colName + ' + ''" ''' end
from c
--print @sql;
set @SQL = N'select ''REVENUE2016 ('' + ' + @SQL + ')'' from REVENUE2016 ';
--print @sql;
exec sp_executesql @sql;
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ' from REVENUE2016 '.
Thank you.
/****** Object: Table [dbo].[REVENUE2016] Script Date: 5/28/2017 1:19:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[REVENUE2016](
[SalesDate] [date] NULL,
[NamaBarang] [varchar](max) NULL,
[Vintage] [varchar](50) NULL,
[Btl] [money] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
declare @SQL nvarchar(max)
with c as (
SELECT c.name colName, c.system_type_id, t.name datatype
FROM sys.columns c inner join sys.types t on c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('dbo.REVENUE2016
)
select @SQL = isnull(@SQL + ', '' + ', '') + case
when datatype in ('char','nvarchar','varcha
when datatype in ('int','biginit','tinyint'
when datatype in ('date','datetime', 'datetime2') then '''"#'' + convert(varchar, ' + colName + ',120) + ''"'
else '''"'' + ' + colName + ' + ''" ''' end
from c
--print @sql;
set @SQL = N'select ''REVENUE2016 ('' + ' + @SQL + ')'' from REVENUE2016 ';
--print @sql;
exec sp_executesql @sql;
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ' from REVENUE2016 '.
Thank you.
uncomment
--print @sql;
comment out this
exec sp_executesql @sql;
and see what sql it generates
then get it and run it to see where the issue is...
--print @sql;
comment out this
exec sp_executesql @sql;
and see what sql it generates
then get it and run it to see where the issue is...
probably you have a different datatype and it goes to else part, which I did not test :)
ok, I changed the default part as well
declare @SQL nvarchar(max);
the else part should cast the column as varchar and put it as "somevalue"
declare @SQL nvarchar(max);
with c as (
SELECT c.name colName, c.system_type_id, t.name datatype
FROM sys.columns c inner join sys.types t on c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('dbo.test') and t.name<>'sysname'
)
select @SQL = isnull(@SQL + ', '' + ', '') + case
when datatype in ('char','nvarchar','varchar') then '''"'' + ' + colName + ' + ''"'
when datatype in ('int','biginit','tinyint','bit') then 'cast(' + colName + ' as varchar) + ''d'
when datatype in ('date','datetime', 'datetime2') then '''"#'' + convert(varchar, ' + colName + ',120) + ''"'
else '''"'' + cast(' + colName + ' as varchar) + ''"' end
from c
--print @sql;
set @SQL = N'select ''TEST ('' + ' + @SQL + ');'' from TEST';
print @sql;
exec sp_executesql @sql;the else part should cast the column as varchar and put it as "somevalue"
ASKER
Just this.
'"#' + convert(varchar, SalesDate,120) + '", ' + '"' + Btl + '" ', ' + '"' + NamaBarang + '", ' + '"' + Vintage + '"
Thank you.
'"#' + convert(varchar, SalesDate,120) + '", ' + '"' + Btl + '" ', ' + '"' + NamaBarang + '", ' + '"' + Vintage + '"
Thank you.
remember, this is not full code, there may be different datatypes and different formats that you may want...
so, you should change/work on "case....end" section to your needs...
so, you should change/work on "case....end" section to your needs...
ASKER
- remember, this is not full code, there may be different datatypes and different formats that you may want...
Yes, it is a money type.
One problem.
The result column order is not base on what we see at design ?
Thank you.
Yes, it is a money type.
One problem.
The result column order is not base on what we see at design ?
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
'"#' + convert(varchar, SalesDate,120) + '", ' + cast(Btl as varchar) + 'd, ' + '"' + NamaBarang + '", ' + '"' + Vintage + '"'
REVENUE2016 ("#2016-08-23", 1.00d, "Allees de Cantemerle 2nd wine of Cantemerle", "2009")
SalesDate
NamaBarang
Vintage
Btl
Thank you.
REVENUE2016 ("#2016-08-23", 1.00d, "Allees de Cantemerle 2nd wine of Cantemerle", "2009")
SalesDate
NamaBarang
Vintage
Btl
Thank you.
ASKER
Where is the order problem ?
What code do you change ?
Thank you.
What code do you change ?
Thank you.
ASKER
Thank you very much for your help.
ASKER
Just info :
Should change from :
with c as (
SELECT c.column_id, c.name colName, c.system_type_id, t.name datatype
FROM sys.columns c inner join sys.types t on c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('dbo.test') and t.name<>'sysname'
)
To :
with c as (
SELECT Ordinal_Position, Column_Name, Data_Type,
Character_Maximum_Length from information_schema.columns
)
your CTE shows all databasenya table schema.
Thank you.
Should change from :
with c as (
SELECT c.column_id, c.name colName, c.system_type_id, t.name datatype
FROM sys.columns c inner join sys.types t on c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('dbo.test') and t.name<>'sysname'
)
To :
with c as (
SELECT Ordinal_Position, Column_Name, Data_Type,
Character_Maximum_Length from information_schema.columns
)
your CTE shows all databasenya table schema.
Thank you.
and you should add this on top of the case when ... end
for NULL values...
here is the update
for NULL values...
here is the update
declare @SQL nvarchar(max);
with c as (
SELECT c.column_id, c.name colName, c.system_type_id, t.name datatype
FROM sys.columns c inner join sys.types t on c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('dbo.calendar') and t.name<>'sysname'
)
select @SQL = isnull(@SQL + ', '' + ', '') + case
when colName is null then '''NULL'
when datatype in ('char','nvarchar','varchar') then '''"'' + ' + colName + ' + ''"'
when datatype in ('int','biginit','tinyint','bit') then 'cast(' + colName + ' as varchar) + ''d'
when datatype in ('date','datetime', 'datetime2') then '''"#'' + convert(varchar, ' + colName + ',120) + ''"'
else '''"'' + cast(' + colName + ' as varchar) + ''"' end
from c
order by column_id