Script a table with their records data

Transact-SQL
1

Hello sql experts,

How do I script a table including their data?

For example: I have a table with three columns

and each column has 10 records. So I want to script

the tables including those 10 records so if I

re create to another database, I have those records.

Anyone?

2

I assume you're wanting to have the ability to export/import your records and of course as SQL code right? If you already have the database created, most DBMS should have an export option like this. If you're wanting to code it, it would look something like (if I understand right):

USE database_name;

CREATE TABLE recordTable (
  Primary_Key CHAR(15) NOT NULL,
  Column2 VARCHAR(20) NOT NULL,
  Column3 VARCHAR(20) NOT NULL,
  PRIMARY KEY (Primary_Key)
);

INSERT INTO recordTable VALUES ('1', 'Value 2', 'Value 3'); --Record 1
INSERT INTO recordTable VALUES ('2', 'Value 2', 'Value 3'); --Record 2
INSERT INTO recordTable VALUES ('3', 'Value 2', 'Value 3'); --Record 3
INSERT INTO recordTable VALUES ('4', 'Value 2', 'Value 3'); --Record 4
INSERT INTO recordTable VALUES ('5', 'Value 2', 'Value 3'); --Record 5
INSERT INTO recordTable VALUES ('6', 'Value 2', 'Value 3'); --Record 6
INSERT INTO recordTable VALUES ('7', 'Value 2', 'Value 3'); --Record 7
INSERT INTO recordTable VALUES ('8', 'Value 2', 'Value 3'); --Record 8
INSERT INTO recordTable VALUES ('9', 'Value 2', 'Value 3'); --Record 9
INSERT INTO recordTable VALUES ('10', 'Value 2', 'Value 3'); --Record 10



--TO TEST THE OUTPUT
SELECT * FROM recordTable
ORDER BY Primary_Key ASC;
1 Like
3

SSMS will do that.

Right click the database
Generate Scripts
Change "Script data" from FALSE to TRUE (and select anything else you want / don't want - e.g. in your example the Tables, but if you script tables then you may also want PKeys, Indexes, Constraints etc.?)

In more recent versions its under "Types of data to script" or something like that.

1 Like
4

Thank you Kristen and smartdood.